Thursday, March 28, 2013

Compare DataTables with LINQ and C#

8 comments



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.

8 comments:

  1. HI, this is nice
    but can you please show a code where two data tables can be compared case sensitively.
    e.g.
    Table 1 has '1', 'Gurav', 'Kumar'
    Table 2 has '1', 'gurav', 'Kumar'

    result should be 'Matched but Not equal'

    likewise
    Table 1 has '1', 'Gurav', 'Kumar'
    Table 2 has '1', 'Gurav', 'Kumar'

    result should be 'Matched and equal'

    and 3rd case.

    Table 1 has '1', 'Gurav', 'Kumar'
    Table 2 has '1', 'Gaurav', 'Kumar'

    result should be 'Not Matched'

    Please tell me if this possible to tell like this

    Thanks

    ReplyDelete
    Replies
    1. Current code compares case sensitively.

      Delete
    2. Thanks for reply.
      you say the current code compares case sensitively but please tell me how I set flag for 3 cases.

      flag = 1; (when both matches and are equal)
      flag = 2; (when both matches and are not equal)
      flag = 3; (when both do not match)

      Delete
    3. Linq Except method just used to get not matching records. so it can be used just to check whether two table match or not. to perform something that you want then the CompareDataTables() can be tweaked as follows:

      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 Records3 = dt1.AsEnumerable().Select(s1 => new { Id = s1["Id"].ToString().ToLower(), Name = s1["Name"].ToString().ToLower(), Address = s1["Address"].ToString().ToLower() });
      var Records4 = dt2.AsEnumerable().Select(s1 => new { Id = s1["Id"].ToString().ToLower(), Name = s1["Name"].ToString().ToLower(), Address = s1["Address"].ToString().ToLower() });


      var NoMatch1 = Records1.Except(Records2);
      var NoMatch2 = Records2.Except(Records1);
      var NoMatch3 = Records3.Except(Records4);

      if (NoMatch1.Any() && !NoMatch3.Any()) // case 1
      {
      Console.WriteLine("Matched but Not equal");
      }
      else if(!NoMatch1.Any() && !NoMatch2.Any()) // case 2
      {
      Console.WriteLine("Matched and equal");
      }
      else if (NoMatch1.Any() && NoMatch2.Any()) // case 3
      {
      Console.WriteLine("Not Matched");
      }

      Console.ReadLine();
      }

      Hope this helps.

      Delete
  2. Great Thanks for your help and time....

    nice example
    Thanks

    ReplyDelete
  3. Hi,
    just realized that it compares entire table. Can't it compare row wise and set flag for each row.

    E.g. Row1 in Table 1 be compared with All rows of Table 2, then if there is any match/mismatch then flag be set for this row.

    same process for 2nd row in Table 1 and so on...

    Please tell me how it can be done
    Thanks

    ReplyDelete
    Replies
    1. if you need to compare row wise then you can use foreach loop method to compare datatables. below link may help.
      http://www.dotnetperls.com/datatable-compare-rows

      Delete