Compare two data tables in c#

Description:

Comparing two datatable and get the final table with difference in it, this kind of requirement is generally arise generally when we are doing multiple insert or update. I have scenario like get the two different tables data, compare it and if there is difference in output then insert that data into the third table. So use the approach to get the data from database into different table and then insert the difference output using SqlBulkCopy in to the third table.

 

Code:

Following is the code to compare two datatable. Just copy and paste this class into your code and its working.

 

using System;

using System.Data;

 

namespace [Namespace Name]

{

public class CompareDataTables

{

public static DataTable CompareTables(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];

DataColumn[] firstcolumns = new DataColumn[2];

firstcolumns[0] = ds.Tables[0].Columns[1];

firstcolumns[1] = ds.Tables[0].Columns[7];

 

//for (int i = 0; i < firstcolumns.Length; i++)

//{

// firstcolumns[i] = ds.Tables[0].Columns[i];

//}

 

//DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count];

DataColumn[] secondcolumns = new DataColumn[2];

secondcolumns[0] = ds.Tables[1].Columns[1];

secondcolumns[1] = ds.Tables[1].Columns[7];

 

//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;

}

}

}

 

Code Explanation:

 

1. CompareTables method is static, so you can call it directly from your class. Just add the appropriate reference.
2. Pass the two data tables in this method.
3. Then creates the dataset.
4. Add two tables into the dataset.
5. Then important part, define the columns two compare the data table. In my case i just want to check the column 1 & 7. If there is a mismatch between this two then inserts the record. You can modify the code as per your requirement or just uncomment the line above and below my code , which adds all columns.
6. Creates data relation on that column(s).
7. Add relation two the dataset.
8. Create the target or difference table colums
9. Then load the difference data in it and return the difference table.