I am inserting Records of an Excel
Sheet in a table. Problem is Excel sheet contains rows that have duplicate
values in some columns. But since I am reading the whole Excel in a DataTable
, all these rows are loaded in my DataTable and therefore when I Insert into SQL Table
I get duplicate records. Can anyone suggest any solution to this? How can I avoid this duplication. Should I have to break the DataTable in rows somehow?
C# Code: Reading Excel and Inserting rows in DataTable
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[42] { new DataColumn("Template", typeof(string)),
new DataColumn("Cust_Name", typeof(string)),
new DataColumn("Invoice_No", typeof(int)),
new DataColumn("InvoiceDate",typeof(DateTime)),
new DataColumn("SR_No", typeof(int)),
.
.
.
new DataColumn("ContactTel3", typeof(string))});
foreach (GridViewRow row in GridView1.Rows)
{
int rowIndex = row.RowIndex;
if (rowIndex > 0)
{
string Template = row.Cells[0].Text;
string Cust_Name = row.Cells[1].Text;
int Invoice_No = int.Parse(row.Cells[2].Text);
//DateTime InvoiceDate = DateTime.ParseExact(row.Cells[3].Text, "d-MMM-yy", CultureInfo.InvariantCulture);
string InvoiceDate = (row.Cells[3].Text);
int Sr_No = int.Parse(row.Cells[4].Text);
.
.
.
string ContactTel3 = (row.Cells[41].Text);
dt.Rows.Add(Template, Cust_Name, Invoice_No, InvoiceDate, Sr_No, Description1, Description2, Description3, Description4, Description5,
CurrencyCode, Amount, Subject, Reference, CustomerAddress1, CustomerAddress2, CustomerAddress3, CustomerAddress4, CustomerAddress5,
CustomerAddress6, CustomerTelephone, EmailIdTo, EmailIdCC, BankName, AccountTitle, AccountNo, CurrencyCode1, BankAddress1,
BankAddress2, BankAddress3, BankAddress4, SwiftCode, IBAN, ContactName1, ContactEmail1, ContactTel1, ContactName2, ContactEmail2,
ContactTel2, ContactName3, ContactEmail3, ContactTel3);
}
}
C# Code for Passing DataTable to Stored Procedure
string consString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(consString);
using (SqlCommand cmd = new SqlCommand("[spInsertExcel]"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@tblInvoice", dt);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
And Here's my Stored Procedure:
Create PROCEDURE [dbo].[spInsertExcel]
@tblInvoice [tblInvoiceType] READONLY
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(Select Invoice_No from Invoice)
Begin
INSERT into Invoice([Template],[Cust_Name],[Invoice_No] ,[InvoiceDate],[Sr_No] ,[CurrencyCode] ,[Subject] ,[Reference],[CustomerAddress1] ,
[CustomerAddress2] ,[CustomerAddress3] ,[CustomerAddress4] ,[CustomerAddress5] ,[CustomerAddress6] ,[CustomerTelephone],[EmailIdTo] ,
[EmailIDCC] ,[BankName] ,[AccountTitle] ,[AccountNo] ,[Bankcurrency] ,[BankAddress1] ,[BankAddress2] ,[BankAddress3] ,[BankAddress4] ,
[SwiftCode],[IBAN],[ContactName1],[ContactEmail1],[ContactTel1],[ContactName2],[ContactEmail2],[ContactTel2],[ContactName3],[ContactEmail3],[ContactTel3])
Select [Template],[Cust_Name],[Invoice_No],[InvoiceDate],Sr_No,
CurrencyCode,[Subject], Reference,CustomerAddress1,CustomerAddress2,CustomerAddress3,CustomerAddress4 ,
CustomerAddress5,CustomerAddress6,CustomerTelephone,EmailIdTo , EmailIDCC,BankName, AccountTitle,
AccountNo , Bankcurrency,BankAddress1,BankAddress2,BankAddress3,BankAddress4,SwiftCode,IBAN,ContactName1,ContactEmail1,ContactTel1,
ContactName2 ,ContactEmail2,ContactTel2,ContactName3 ,ContactEmail3,ContactTel3 from @tblInvoice
END
Else
Raiserror('You have already uploaded this file',16,1)
END
EDITED As Answered by a user here, I included the following line of code
DataTable distinctDt = dt.DefaultView.ToTable(true, "Template", "Cust_Name"...);
But still I get The same table. Attached Picture of distinctDt on Debug
I want to insert duplicate columns like Template
,Cust_Name
,Invoice_No
etc in Invoice
table and non-duplicate columns like Amount
,Description1
in another table.But this way Invoice
table will have multiple records. How can I achieve it?
You could filter similiar records after adding them into DataTable by calling Distinct method on DataRow collection:
Or you can filter them before adding to DataTable like this: