How to Check and remove duplicate Row With using DataTable And DataRow?

Question

_{Wednesday, September 23, 2020 4:19 AM}

// the function that i want is when using sqlbulkcopy to
 //add data to sql, but on adding to sql the duplicate row
// also will added to sql also, and the below is the code 
//that to check duplicate row but it no work.

DataTable ExcelTable;//Table Import From Excel
DataTable gridTable;//Table Import From DataGridView or from sql for compare
Datatable NT;//For New DataTable

foreach (DataRow DR1 in ExcelTable.Rows)
            {
                bool SameSame = false;
                foreach (DataRow DR2 in gridTable.Rows)
                {
                    for (int i = 0; i < ExcelTable.Columns.Count; i++)
                    {
                        bool Same = false;
                        if (DR1[i] == DR2[i])
                        {
                            Same = true;
                        }
                        if (Same)
                        {
                            SameSame = true;
                        }
                    }
                }
//this way i want to check if the row same will remove 
//the row and insert data that no duplicate to new datatable
                if (SameSame.Equals(true))
                {
                    MessageBox.Show("Same" + DR1.Table.Rows[k][1]);
                }
                k++;
            }

All replies (2)

_{Wednesday, September 23, 2020 7:20 AM}

One first problem that I see with your code is that it compares each row with every row... including itself. Therefore, it will always find same=true, because the row will be equal to itself. I presume that there is no field or combination of fields that can be used to uniquely identify a row (otherwise there would never be any duplicates), so instead of true/false you need a counter for number of duplicates and only remove a row when the counter is bigger than one. O use "for" loops instead of "foreach" and compare the loop counters so that when they are the same you don´t attempt to compare that row (youwould be comparing the row with itself).

Be careful with "if (DR1[i]==DR2[i])". This will only work if all the columns in your row have compare-by-value semantics (e.g., numbers, dates or strings). But if you have any columns that compare by reference (for instance, arrays of bytes) then this "if" will say that they are different even if they contain the same values.

The logic in the comparisons is inverted. You should start with SameSame=true and set it to false if any column is different. And you can break the loop that compares the columns as soon as this happens; no need to continue comparing the rest of the columns -- once you found a mismatch you already know that the rows are not equal.

As a side note (this will not cause your code to fail but it is superfluous), you don't need both variables "Same" and "SameSame". You can simply set SameSame to false and break the loop. No need for the variable Same.

Oh, and "if (SameSame.Equals(true))" is unnecessarily compex. You can simply write "if (SameSame)".

_{Wednesday, September 23, 2020 9:01 AM}

Hi alianware jack,
Alberto Poblacion has explained the error in your code in detail.
Depends on what you want, you need to loop through the rows of each table. And then through each column within that loop to compare individual values.
Here is a code example to compare two DataTables and return a DataTable with DifferentRecords.

public DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable)
{
    //Create Empty Table  
    DataTable ResultDataTable = new DataTable("ResultDataTable");

    //use a Dataset to make use of a DataRelation object  
    using (DataSet ds = new DataSet())
    {
        //Add tables  
        ds.Tables.AddRange(new DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy()
        });

        //Get Columns for DataRelation  
        DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];
        for (int i = 0; i < firstColumns.Length; i++)
        {
            firstColumns[i] = ds.Tables[0].Columns[i];
        }

        DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];
        for (int i = 0; i < secondColumns.Length; i++)
        {
            secondColumns[i] = ds.Tables[1].Columns[i];
        }

        DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);
        ds.Relations.Add(r1);

        DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);
        ds.Relations.Add(r2);

        //Create columns for return table  
        for (int i = 0; i < FirstDataTable.Columns.Count; i++)
        {
            ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType);
        }

        //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.  
        ResultDataTable.BeginLoadData();
        foreach (DataRow parentrow in ds.Tables[0].Rows)
        {
            DataRow[] childrows = parentrow.GetChildRows(r1);
            if (childrows == null || childrows.Length == 0)
                ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
        }

        //If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.  
        foreach (DataRow parentrow in ds.Tables[1].Rows)
        {
            DataRow[] childrows = parentrow.GetChildRows(r2);
            if (childrows == null || childrows.Length == 0)
                ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
        }
        ResultDataTable.EndLoadData();
    }

    return ResultDataTable;
}

Best Regards,
Daniel Zhang

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [email protected].