Divide Rows of DataTable

Question

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?

Answer 1

You could filter similiar records after adding them into DataTable by calling Distinct method on DataRow collection:

var distinct = dataTable.AsEnumerable().Distinct(DataRowComparer.Default);

Or you can filter them before adding to DataTable like this:

GridView1.Rows.Select(x=> new 
{
                Template = row.Cells[0].Text;
                Cust_Name = row.Cells[1].Text;
                Invoice_No = int.Parse(row.Cells[2].Text);
                InvoiceDate = DateTime.ParseExact(row.Cells[3].Text, "d-MMM-yy", CultureInfo.InvariantCulture);
                nvoiceDate = (row.Cells[3].Text);
                Sr_No = int.Parse(row.Cells[4].Text);
}).Distinct();

Answer 2

You can select distinct values from your table dt before dumping it into database. Using code below.

DataTable distinctDt = dt.DefaultView.ToTable(true, "Template", "Cust_Name"...);

Now use distinctDt to dump that distinct data into database. For more information on this refer MSDN.

Answer 3

I think you need to have a IDENTITY column in your invoice table.
If you add an IDENTITY(1, 1) field say it ID you can do your custom operation on it in an indexed way.
Now you can use another stored procedure to copy your data from invoice table to otherTable like this:

INSERT INTO [otherTable]
SELECT 
    [Amount], [Description]
FROM
    [invoice]
GROUP BY
    [Amount], [Description]