get the differences in 2 DataSets c#

Question

I am writing a short algorithm which has to compare two DataSets, so that the differences between both can be further processed. I tryed accomplishing this goal by merging these two DataSets and get the resulting changes into a new DataSet.

My Method looks like this:

    private DataSet ComputateDiff(DataSet newVersion, DataSet oldVersion) 
    {
        DataSet diff = null;
        oldVersion.Merge(newVersion);
        bool foundChanges = oldVersion.HasChanges();
        if (foundChanges) 
        {
            diff = oldVersion.GetChanges();
        }
        return diff;
    }

The result of foundChanges is always false, even though the two DataSets have different values in it. Both DataSets have the same strukture. They consist of three DataTables which are the result of three querys in a Database. The merge works fine no problems with that.

My question is: Is there any reasonable explanation why the foundChanges variable is always false and if not would Linq provide a proper solution for this problem or do i have to determine the changes by iterating through the DataSets

Here are some further informations: The programming language is C# I am using .Net framework 4.0 I am developing on a Windows 8 Machine The Data as mentioned comes from a Database(MSSQL Server 2012 express) My DataSets or DataTables haven't got any PK's as far as i know.

Thanks in advance

Solution

I think the problem is that you don't understand .NET DataSets. A DataTable retains the "original" copy of each value loaded into it. When a value is changed, the DataTable is able to detect the change. Likewise, the DataTable keeps track of rows that have been added or deleted. The HasChanges() function simply crawls through the DataTables and checks to see if there's been any changes (changed value, new rows, deleted rows, etc.)

See the MSDN documentation:
http://msdn.microsoft.com/en-us/library/system.data.dataset.haschanges.aspx

Comparing two DataSets is tricky, and I'm not aware of any built-in function to handle this (since every programmer will have their own definition of "equivalence").

See:

• How to compare 2 dataTables on StackOverflow
• Comparing DataSets using LINQ on CodeProject

The code below will compare two DataTables by looking for added/deleted rows based on a Key column and modified rows by comparing the values of matching rows (again, based on the key). It would be fairly trivial to expand this to compare DataSets (by comparing similarly-named tables between DataSets).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataSetComparison
{
    class Program
    {
        static void Main( string[] args )
        {

            var l_table1 = new DataTable();
            l_table1.Columns.Add( "Key", typeof( int ) );
            l_table1.Columns.Add( "Name", typeof( string ) );
            l_table1.Columns.Add( "Age", typeof( int ) );

            var l_table2 = new DataTable();
            l_table2.Columns.Add( "Key", typeof( int ) );
            l_table2.Columns.Add( "Name", typeof( string ) );
            l_table2.Columns.Add( "Age", typeof( int ) );

            l_table1.Rows.Add( l_table1.NewRow() );
            l_table1.Rows[l_table1.Rows.Count - 1]["Key"] = 0;
            l_table1.Rows[l_table1.Rows.Count - 1]["Name"] = "Alfred Harisson";
            l_table1.Rows[l_table1.Rows.Count - 1]["Age"] = 36;
            l_table1.Rows.Add( l_table1.NewRow() );
            l_table1.Rows[l_table1.Rows.Count - 1]["Key"] = 1;
            l_table1.Rows[l_table1.Rows.Count - 1]["Name"] = "Matthew George";
            l_table1.Rows[l_table1.Rows.Count - 1]["Age"] = 41;
            l_table1.Rows.Add( l_table1.NewRow() );
            l_table1.Rows[l_table1.Rows.Count - 1]["Key"] = 2;
            l_table1.Rows[l_table1.Rows.Count - 1]["Name"] = "Franklin Henry";
            l_table1.Rows[l_table1.Rows.Count - 1]["Age"] = 33;

            l_table2.Rows.Add( l_table2.NewRow() );
            l_table2.Rows[l_table2.Rows.Count - 1]["Key"] = 0;
            l_table2.Rows[l_table2.Rows.Count - 1]["Name"] = "Alfred Harisson";
            l_table2.Rows[l_table2.Rows.Count - 1]["Age"] = 36;
            l_table2.Rows.Add( l_table2.NewRow() );
            l_table2.Rows[l_table2.Rows.Count - 1]["Key"] = 1;
            l_table2.Rows[l_table2.Rows.Count - 1]["Name"] = "Matthew George";
            l_table2.Rows[l_table2.Rows.Count - 1]["Age"] = 42; // Record 1 "modified"
            // Record 2 "deleted"
            // Record 3 "added":
            l_table2.Rows.Add( l_table2.NewRow() );
            l_table2.Rows[l_table2.Rows.Count - 1]["Key"] = 3;
            l_table2.Rows[l_table2.Rows.Count - 1]["Name"] = "Lester Kulick";
            l_table2.Rows[l_table2.Rows.Count - 1]["Age"] = 33;

            // Using table 1 as the control, find changes in table 2

            // Find deleted rows:
            var l_table2Keys = l_table2.Select().Select( ( r ) => (int) r["Key"] );
            var l_deletedRows = l_table1.Select().Where( ( r ) => !l_table2Keys.Contains( (int) r["Key"] ) );

            foreach ( var l_deletedRow in l_deletedRows )
                Console.WriteLine( "Record " + l_deletedRow["Key"].ToString() + " was deleted from table 2." );

            // Find added rows:
            var l_table1Keys = l_table1.Select().Select( ( r ) => (int) r["Key"] );
            var l_addedRows = l_table2.Select().Where( ( r ) => !l_table1Keys.Contains( (int) r["Key"] ) );

            foreach ( var l_addedRow in l_addedRows )
                Console.WriteLine( "Record " + l_addedRow["Key"].ToString() + " was added to table 2." );

            // Find modified rows:
            var l_modifiedRows = l_table2.Select()
                                         .Join(
                                            l_table1.Select(),
                                            r => (int) r["Key"],
                                            r => (int) r["Key"],
                                            ( r1, r2 ) => new
                                                {
                                                    Row1 = r1,
                                                    Row2 = r2
                                                } )
                                        .Where(
                                            values => !( values.Row1["Name"].Equals( values.Row2["Name"] ) &&
                                                         values.Row1["Age"].Equals( values.Row2["Age"] ) ) )
                                        .Select( values => values.Row2 );

            foreach ( var l_modifiedRow in l_modifiedRows )
                Console.WriteLine( "Record " + l_modifiedRow["Key"].ToString() + " was modified in table 2." );

            Console.WriteLine( "Press any key to quit..." );
            Console.ReadKey( true );

        }
    }
}

Console output:

Record 2 was deleted from table 2.
Record 3 was added to table 2.
Record 1 was modified in table 2.

Comments

[Frank Tudor]

Just in looking .contains, .exists could be useful to you.

[Nick Bray]

Cyborgx37 is right you can't easily do this. You would need to have knowledge of the keys of the dataset, otherwise you wouldn't be able to tell if a change is an update, a delete, or an insert.

[Paul Kienitz]

Why do I keep seeing people say that Merge + GetChanges works, when it does not?