Insert NON-DUPLICATE Data into SQL Database from Excel file using ASP.NET Core 3.1

Question

I'm trying to use two C# DataTables like below to meet my requirement.

dtExcelData: This DataTable holds the data which is uploaded from Excel file. This data should be inserted into SQL Server Database based on certain conditions which were mentioned below.

dtDbData: This DataTable holds data from Database. Preparing this DataTable just by reading two columns which represent Primary Key.

I'm using dtExcelData datatable to save records into SQL Server Database after uploading Excel file.

The requirement is that I should validate dtExcelData before I insert into database. There exists 39 columns in dtExcelData datatable with the column headings column1, column2, ... column39. And, the number of rows can range upto 400 (or even little more).

I've to do validation like below: column6, and column22 from Excel file combinedly is considered as primary key. If this same data is already available in database, I should NOT consider that record to insert into database. I can simply ignore that record. All other records should be inserted into database.

I've tried number of approaches to meet this requirement, but unable to arrive to proper solution.

I am looking for some approach like below:

dtExcelData.Except(dtDbData, SomeDataComparerForTheseTwoColumns)

Looking for some help.

Answer 1

Below is a work demo, you can refer to it.

        DataTable dtExcelData = new DataTable(); 
        dtExcelData.Columns.Add("Id", typeof(int));
        dtExcelData.Columns.Add("Name", typeof(string));
        DataRow row = dtExcelData.NewRow();
        row["Id"] = 1;
        row["Name"] = "John";
        dtExcelData.Rows.Add(row);
        DataRow row2 = dtExcelData.NewRow();
        row2["Id"] = 2;
        row2["Name"] = "Max";
        dtExcelData.Rows.Add(row2);
        DataRow row3= dtExcelData.NewRow();
        row3["Id"] = 2;
        row3["Name"] = "John";
        dtExcelData.Rows.Add(row3);
        DataRow row4 = dtExcelData.NewRow();
        row4["Id"] = 1;
        row4["Name"] = "Max";
        dtExcelData.Rows.Add(row4);
        DataRow row5 = dtExcelData.NewRow();
        row5["Id"] = 3;
        row5["Name"] = "Tom";
        dtExcelData.Rows.Add(row5);


        DataTable dtDbData = new DataTable(); 
        dtDbData.Columns.Add("Id", typeof(int));
        dtDbData.Columns.Add("Name", typeof(string));
        DataRow rrow = dtDbData.NewRow();
        rrow["Id"] = 1;
        rrow["Name"] = "John";
        dtDbData.Rows.Add(rrow);
        DataRow rrow2 = dtDbData.NewRow();
        rrow2["Id"] = 2;
        rrow2["Name"] = "Max";
        dtDbData.Rows.Add(rrow2);



        //match up the column and then find the missing.
        var matched = from r1 in dtExcelData.AsEnumerable()
                      join r2 in dtDbData.AsEnumerable()
                      on r1.Field<int>(0) equals r2.Field<int>(0)
                      where r1.Field<string>(1) == r2.Field<string>(1)
                      select r1;
        //This should give you the rows which do not have a match
        var missing = from table1 in dtExcelData.AsEnumerable()
                      where !matched.Contains(table1)
                      select table1;


        if (missing.Any())
        {
            //use for/foreach loop through each row and then insert into database.
            // do something with these rows 
        }

result: