Compare Datatables & Merge Changes

Question

I've got two datatables (A, B) with the same structure. I need to compare each row of A with that of B and the resultant Datatable C should have the row in A and the changes of that row in B below it. For rows which are identical (same values in A & B) the resulting Datatable shouldn't have these rows.

So the resulting Datatable should have each row in A and its non-identical row in B under it. Resulting table shouldn't have identical rows.

Can anyone please help me with C# code.

Answer 1

the easiest way is a cross-database union:

create table merged
(select * from db1.t) union (select * from db2.t)

only unique rows are returned. to compare records, select rows that share the same key (the key column in merged will be non-unique).

select * from merged order by key

orders the results in the order you wanted.

select * from merged where key in
(select key from merged group by key having count(*) > 1)
order by key

will return only mismatched rows.

Answer 2

It looks like you want to compute the symmetric difference of two DataSets. We can do this using a bit of LINQ, an equality comparer, and a few extension methods. The code is tested and working.

class Program
{
    static void Main()
    {
        var a = new DataTable {Columns = {{"FirstName", typeof (string)}, {"Age", typeof (int)}}, Rows = {{"Alice", 31}, {"Bob", 42}}};
        var b = new DataTable {Columns = {{"FirstName", typeof (string)}, {"Age", typeof (int)}}, Rows = {{"Alice", 31}, {"Carol", 53}}};
        var diffs = a.SymmetricDifference(b);
        Console.Write(diffs.Rows.Count);
    }
}

public static class DataTableExtensions
{
    public static DataTable SymmetricDifference(this DataTable a, DataTable b)
    {
        var diff = a.Clone();
        foreach (var person in a.AsPersonList().SymmetricDifference(b.AsPersonList()))
        {
            diff.Rows.Add(person.FirstName, person.Age);
        }

        return diff;
    }

    private static IEnumerable<Person> SymmetricDifference(this IEnumerable<Person> a, IEnumerable<Person> b)
    {
        return a.SymmetricDifference(b, new PersonComparer());
    }

    private static IEnumerable<T> SymmetricDifference<T>(this IEnumerable<T> a, IEnumerable<T> b, IEqualityComparer<T> comparer)
    {
        return a.Except(b, comparer).Concat(b.Except(a, comparer));
    }

    private static IEnumerable<Person> AsPersonList(this DataTable table)
    {
        return table.AsEnumerable().Select(row => row.AsPerson()).ToList();
    }

    private static Person AsPerson(this DataRow row)
    {
        return new Person
                   {
                       FirstName = row.Field<string>("FirstName"),
                       Age = row.Field<int>("Age")
                   };
    }
}

public class PersonComparer : IEqualityComparer<Person>
{
    public bool Equals(Person a, Person b)
   {
        return a.FirstName == b.FirstName && a.Age == b.Age;
   }

   public int GetHashCode(Person item)
   {
        return StringComparer.InvariantCultureIgnoreCase.GetHashCode(item.FirstName)
               + StringComparer.InvariantCultureIgnoreCase.GetHashCode(item.Age);
   }
}

public class Person
{
    public string FirstName;
    public int Age;
}