Comparing two DataTables by specific column in C#

Terry Minton picture Terry Minton · Oct 19, 2013 · Viewed 8.5k times · Source

I am fairly new to C#, but I was able to pull in data from an excel document to a datatable and also data from a sql stored procedure into a datatable. Now I need to loop through the excel datatable to find anything that does not match from the sql datatable. I would also like the matching to begin at a certain column name which is not necessarily the first column. Any ideas or pointers? Thank you!!

Here is my code

foreach (DataRow row in excelTb.Rows) // Loop over the rows.
{
    Console.WriteLine("--- Row ---"); // Print separator.
    foreach (var item in row.ItemArray) // Loop over the items.
    {
        Console.Write("Item: "); // Print label.
        Console.WriteLine(item); // Invokes ToString abstract method.
    }
}
Console.Read(); // Pause.*/

Answer

Tafari picture Tafari · Oct 19, 2013

I am not sure if I get your intentions right, but just for compering rows from one datatable to another on certain column you could try this:

private void compareDataTables()
{
  // I assume that datatables are of same length
  for(int i = 0; i < excelDataTable.Rows.Count; i++)
  {
    // Assuming that given columns in both datatables are of same type
    if(excelDataTable.Rows[i]["col_name"] == sqlDataTable.Rows[i]["col_name"])
    {
      //your code
    }
}

Printing column names:

string names = "Excel table: \n";
foreach (DataColumn dc in excelDataTable.Columns)
{
    names += dc.ColumnName+"\n";
}

names += "Sql table: \n";
foreach (DataColumn dc in sqlDataTable.Columns)
                {
                    names += dc.ColumnName+"\n";
                }
MessageBox.Show(names);