Hi Friends, There are times when we want to compare two DataTables and find out the Differences in them. There are different ways of doing it, The Most common way is by using Loops but the problem of using loops is that, if records are more in both Tables then loops execution time increases which can be performance killer.
Here i would like to show a method which can be used to compare both Datatables and list there Differences. This method is short and sweet and uses LINQ for comparing the results.
So to Start, first fire up Visual Studio, select New Console Application Project and name it as "CompareDataTable", Make sure you select .Net Framework 3.5 as Linq was introduced in it.
In below example we are creating two DataTables dt1 and dt2 and pass them to our method "CompareDataTables" to compare them.
static void Main(string[] args)
static void Main(string[] args)
{
//Declare Datatables dt1 and dt2
DataTable dt1 = new DataTable(),dt2 = new DataTable();
//Add columns and datatypes for datatable dt1
dt1.Columns.Add("Id",typeof(int));
dt1.Columns.Add("Name", typeof(string));
dt1.Columns.Add("Address", typeof(string));
//Add columns and datatypes for datatable dt2
dt2.Columns.Add("Id", typeof(int));
dt2.Columns.Add("Name", typeof(string));
dt2.Columns.Add("Address", typeof(string));
//Add rows for datatable dt1
dt1.Rows.Add(1, "Hardcoderz", "ABC");
dt1.Rows.Add(1, "G Gurav", "ABC2");
dt1.Rows.Add(1, "Ganesh Gurav", "ABC1");
//Add rows for datatable dt2
dt2.Rows.Add(1, "Hardcoderz", "ABC");
dt2.Rows.Add(1, "D Gurav", "ABC2");
dt2.Rows.Add(1, "Ganesh Gurav", "ABC1");
//Call our CompareDataTables() Method
CompareDataTables(dt1, dt2);
}
So lets write our CompareDataTables method. The method will fetch non matching rows by using Except Method of linq and store it in NoMatch1 variable. Records1 and Recodrs2 are AnonymousType of Linq. Below code illustrates it.
static void CompareDataTables(DataTable dt1, DataTable dt2)
{
var Records1 = dt1.AsEnumerable().Select(s1 => new { Id = s1["Id"].ToString(), Name = s1["Name"].ToString(),Address = s1["Address"].ToString() });
var Records2 = dt2.AsEnumerable().Select(s1 => new { Id = s1["Id"].ToString(), Name = s1["Name"].ToString(), Address = s1["Address"].ToString() });
var NoMatch1 = Records1.Except(Records2);
}
Now Making our Method more descriptive. Our Method CompareDataTables Convert the Datatables to a collection of AnonymousType, and fetches the non matching values by Linq Except method. and Prints the result on the screen.
static void CompareDataTables(DataTable dt1, DataTable dt2)
{
// All data in both DataTables are stored in Records1 and Records2 of type Anonymous, as we dont know the Type we use var.
var Records1 = dt1.AsEnumerable().Select(s1 => new { Id = s1["Id"].ToString(), Name = s1["Name"].ToString(),Address = s1["Address"].ToString() });
var Records2 = dt2.AsEnumerable().Select(s1 => new { Id = s1["Id"].ToString(), Name = s1["Name"].ToString(), Address = s1["Address"].ToString() });
//show values of dt1 stored in Records1 Anonymous Type
Console.WriteLine("Table 1:");
Console.WriteLine("--------------------");
Console.WriteLine("Id | Name | Address");
Console.WriteLine("--------------------");
// Select is a Linq method to fetch data from collections.
//ToArray<string>() is used to create a array of string.
//Join is use to Join array of string with new line (\n).
Console.WriteLine(string.Join("\n", Records1.Select(s1 => Convert.ToString(s1.Id) + " | " + Convert.ToString(s1.Name) + " | " + Convert.ToString(s1.Address)).ToArray>string>()));
Console.WriteLine("\nTable 2:");
Console.WriteLine("--------------------");
Console.WriteLine("Id | Name | Address");
Console.WriteLine("--------------------");
// Select is a Linq method to fetch data from collections.
//ToArray<string>() is used to create a array of string.
//Join is use to Join array of string with new line (\n).
Console.WriteLine(string.Join("\n", Records2.Select(s1 => Convert.ToString(s1.Id) + " | " + Convert.ToString(s1.Name) + " | " + Convert.ToString(s1.Address)).ToArray<string>()));
//Except method of Linq returns non match rows.
var NoMatch1 = Records1.Except(Records2);
var NoMatch2 = Records2.Except(Records1);
//Show Non matching records from table 1.
Console.WriteLine("\nNon Matching Records: From Table 1:");
Console.WriteLine("--------------------");
Console.WriteLine("Id | Name | Address");
Console.WriteLine("--------------------");
Console.WriteLine(string.Join("\n", NoMatch1.Select(s1 => Convert.ToString(s1.Id) + " " + Convert.ToString(s1.Name) + " " + Convert.ToString(s1.Address)).ToArray<string>()));
//show non matching records from table 2
Console.WriteLine("\nNon Matching Records: From Table 2:");
Console.WriteLine("--------------------");
Console.WriteLine("Id | Name | Address");
Console.WriteLine("--------------------");
Console.WriteLine(string.Join("\n", NoMatch2.Select(s1 => Convert.ToString(s1.Id) + " " + Convert.ToString(s1.Name) + " " + Convert.ToString(s1.Address)).ToArray<string>()));
Console.ReadLine();
}
The Full code of Program.cs is as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web.UI.WebControls;
namespace CompareDataTable
{
class Program
{
static void Main(string[] args)
{
//Declare Datatables dt1 and dt2
DataTable dt1 = new DataTable(),dt2 = new DataTable();
//Add columns and datatypes for datatable dt1
dt1.Columns.Add("Id",typeof(int));
dt1.Columns.Add("Name", typeof(string));
dt1.Columns.Add("Address", typeof(string));
//Add columns and datatypes for datatable dt2
dt2.Columns.Add("Id", typeof(int));
dt2.Columns.Add("Name", typeof(string));
dt2.Columns.Add("Address", typeof(string));
//Add rows for datatable dt1
dt1.Rows.Add(1, "Hardcoderz", "ABC");
dt1.Rows.Add(1, "G Gurav", "ABC2");
dt1.Rows.Add(1, "Ganesh Gurav", "ABC1");
//Add rows for datatable dt2
dt2.Rows.Add(1, "Hardcoderz", "ABC");
dt2.Rows.Add(1, "D Gurav", "ABC2");
dt2.Rows.Add(1, "Ganesh Gurav", "ABC1");
//Call our CompareDataTables() Method
CompareDataTables(dt1, dt2);
}
static void CompareDataTables(DataTable dt1, DataTable dt2)
{
// All data in both DataTables are stored in Records1 and Records2 of type Anonymous, as we dont know the Type we use var.
var Records1 = dt1.AsEnumerable().Select(s1 => new { Id = s1["Id"].ToString(), Name = s1["Name"].ToString(),Address = s1["Address"].ToString() });
var Records2 = dt2.AsEnumerable().Select(s1 => new { Id = s1["Id"].ToString(), Name = s1["Name"].ToString(), Address = s1["Address"].ToString() });
//show values of dt1 stored in Records1 Anonymous Type
Console.WriteLine("Table 1:");
Console.WriteLine("--------------------");
Console.WriteLine("Id | Name | Address");
Console.WriteLine("--------------------");
// Select is a Linq method to fetch data from collections.
//ToArray<string>() is used to create a array of string.
//Join is use to Join array of string with new line (\n).
Console.WriteLine(string.Join("\n", Records1.Select(s1 => Convert.ToString(s1.Id) + " | " + Convert.ToString(s1.Name) + " | " + Convert.ToString(s1.Address)).ToArray<string>()));
Console.WriteLine("\nTable 2:");
Console.WriteLine("--------------------");
Console.WriteLine("Id | Name | Address");
Console.WriteLine("--------------------");
// Select is a Linq method to fetch data from collections.
//ToArray<string>() is used to create a array of string.
//Join is use to Join array of string with new line (\n).
Console.WriteLine(string.Join("\n", Records2.Select(s1 => Convert.ToString(s1.Id) + " | " + Convert.ToString(s1.Name) + " | " + Convert.ToString(s1.Address)).ToArray<string>()));
//Except method of Linq returns non match rows.
var NoMatch1 = Records1.Except(Records2);
var NoMatch2 = Records2.Except(Records1);
//Show Non matching records from table 1.
Console.WriteLine("\nNon Matching Records: From Table 1:");
Console.WriteLine("--------------------");
Console.WriteLine("Id | Name | Address");
Console.WriteLine("--------------------");
Console.WriteLine(string.Join("\n", NoMatch1.Select(s1 => Convert.ToString(s1.Id) + " " + Convert.ToString(s1.Name) + " " + Convert.ToString(s1.Address)).ToArray<string>()));
//show non matching records from table 2
Console.WriteLine("\nNon Matching Records: From Table 2:");
Console.WriteLine("--------------------");
Console.WriteLine("Id | Name | Address");
Console.WriteLine("--------------------");
Console.WriteLine(string.Join("\n", NoMatch2.Select(s1 => Convert.ToString(s1.Id) + " " + Convert.ToString(s1.Name) + " " + Convert.ToString(s1.Address)).ToArray<string>()));
Console.ReadLine();
}
}
}
Note: There may be other more easy ways to do this, even there may be situation were this method may not provide expected results. I showed this method cause this method resolved my problem of comparing two Datatables with small code and without using Loops.
