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;
}
Hi Kaushik
ReplyDeleteI 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
public static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2)
Delete{
dt1.Merge(dt2);
DataTable d3 = dt2.GetChanges();
return d3;
}
Did you manage to find an answer to this problem, of being able to find the differences between two DataTable's.
ReplyDeleteWow Very Nice Posted.
ReplyDeleteHow to Update the difference into second table?
kuvanka@gmail.com
Thanks,
Siva
Unfortunately, tables can't have more than 32 columns to be able to use the method you have provided.
ReplyDeleteHi this is very much helpful
ReplyDeleteyour code work perfectly but i want to update whole Datatable from one database to another that is not here.
ReplyDeletecan u send me this code ?
Thanks much!
ReplyDeleteDataRelation don't allows to have more than 32 columns.
ReplyDeleteNice dude i will try this logic.
ReplyDeleteThanks
Document Comparison
Hi, this logic is nice,
ReplyDeleteBut 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.....
Hello friends
ReplyDeleteI 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;
}
Hi Kaushik,
ReplyDeleteThis is useful for my scenario.
Great post.
Marcio Coelho
var unMatched = dt1.AsEnumerable().Select(r => r.Field("col")).Except(dt2.AsEnumerable().Select(r => r.Field("col"))));
ReplyDeleteNote: DataType of both the columns must be same otherwise convert as below :
.Select(r => Convert.ToDecimal(r.Field("col1")))).
If both tables have the same schema..Then use the following code
ReplyDeletepublic static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2)
{
dt1.Merge(dt2);
DataTable d3 = dt2.GetChanges();
return d3;
}
This comment has been removed by the author.
ReplyDeletei've got an error because columns of two datatables are not the same,can you helop me with this please?
ReplyDelete