Saturday, December 29, 2007

C#: Compare 2 DataTables and return a 3rd with differences

public DataTable CompareDataTables(DataTable first, DataTable second)
{
first.TableName = "FirstTable";
second.TableName = "SecondTable";

//Create Empty Table
DataTable table = new DataTable("Difference");

try
{
//Must use a Dataset to make use of a DataRelation object
using (DataSet ds = new DataSet())
{
//Add tables
ds.Tables.AddRange(new DataTable[] { first.Copy(), second.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];
}

//Create DataRelation
DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);

ds.Relations.Add(r);

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

//If First Row not in Second, Add to return table.
table.BeginLoadData();

foreach (DataRow parentrow in ds.Tables[0].Rows)
{
DataRow[] childrows = parentrow.GetChildRows(r);
if (childrows == null || childrows.Length == 0)
table.LoadDataRow(parentrow.ItemArray, true);
}

table.EndLoadData();

}
}
catch (Exception ex)
{
throw ex;
}

return table;
}

17 comments:

  1. Hi Kaushik
    I have to compare two large data table which contains millions of records.your method takes lot of time to compare.

    So the problem is that is there a way to compare and find difference by executing a single Query.
    like we do in SQL.

    ~pramod

    ReplyDelete
    Replies
    1. public static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2)
      {

      dt1.Merge(dt2);

      DataTable d3 = dt2.GetChanges();


      return d3;

      }

      Delete
  2. Did you manage to find an answer to this problem, of being able to find the differences between two DataTable's.

    ReplyDelete
  3. Wow Very Nice Posted.

    How to Update the difference into second table?

    kuvanka@gmail.com

    Thanks,
    Siva

    ReplyDelete
  4. Unfortunately, tables can't have more than 32 columns to be able to use the method you have provided.

    ReplyDelete
  5. your code work perfectly but i want to update whole Datatable from one database to another that is not here.
    can u send me this code ?

    ReplyDelete
  6. DataRelation don't allows to have more than 32 columns.

    ReplyDelete
  7. Hi, this logic is nice,
    But I am getting some error while doing the dataRelation."Parent Columns and Child Columns don't have type-matching columns.".. Here I am trying to compair the two data table. (the table export to excel, In excel sheet user made some chnage on that, then that excel sheet need to import and compair with table witch are the column are got edited)
    Please help mee.....

    ReplyDelete
  8. Hello friends
    I used below code which working fine in case of any number of records.
    public Boolean CompareTwoDataTable(DataTable dt1, DataTable dt2)
    {
    Int32 i;
    if (dt1.Rows.Count != dt2.Rows.Count)
    {
    return false;
    }
    for (i = 0; i < dt1.Rows.Count; i++)
    {
    dt2.DefaultView.RowFilter = "Tag='" + dt1.Rows[i]["Tag"].ToString() + "'";
    if (dt2.Rows.Count == 0)
    {
    return false;
    }
    }
    return true;
    }

    ReplyDelete
  9. Hi Kaushik,
    This is useful for my scenario.
    Great post.
    Marcio Coelho

    ReplyDelete
  10. var unMatched = dt1.AsEnumerable().Select(r => r.Field("col")).Except(dt2.AsEnumerable().Select(r => r.Field("col"))));

    Note: DataType of both the columns must be same otherwise convert as below :

    .Select(r => Convert.ToDecimal(r.Field("col1")))).

    ReplyDelete
  11. If both tables have the same schema..Then use the following code

    public static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2)
    {

    dt1.Merge(dt2);

    DataTable d3 = dt2.GetChanges();


    return d3;

    }

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. i've got an error because columns of two datatables are not the same,can you helop me with this please?

    ReplyDelete

Please use your common sense before making a comment, and I truly appreciate your constructive criticisms.