How to compare 2 dataTables

Question

I have 2 datatables and I just want to know if they are the same or not. By "the same", I mean do they have exactly the same number of rows with exactly the same data in each column, or not. I'd love to write (find) a method which accepts both tables and returns a boolean.

How can I compare 2 datatables in this way? Both have identical schemas.

Answer 1

 public static bool AreTablesTheSame( DataTable tbl1, DataTable tbl2)
 {
    if (tbl1.Rows.Count != tbl2.Rows.Count || tbl1.Columns.Count != tbl2.Columns.Count)
                return false;


    for ( int i = 0; i < tbl1.Rows.Count; i++)
    {
        for ( int c = 0; c < tbl1.Columns.Count; c++)
        {
            if (!Equals(tbl1.Rows[i][c] ,tbl2.Rows[i][c]))
                        return false;
        }
     }
     return true;
  }

Answer 2

If you were returning a DataTable as a function you could:

DataTable dataTable1; // Load with data
DataTable dataTable2; // Load with data (same schema)

// Fast check for row count equality.
if ( dataTable1.Rows.Count != dataTable2.Rows.Count) {
    return true;
}

var differences =
    dataTable1.AsEnumerable().Except(dataTable2.AsEnumerable(),
                                            DataRowComparer.Default);

return differences.Any() ? differences.CopyToDataTable() : new DataTable();

Answer 3

You would need to loop through the rows of each table, and then through each column within that loop to compare individual values.

There's a code sample here: http://canlu.blogspot.com/2009/05/how-to-compare-two-datatables-in-adonet.html

Answer 4

The OP, MAW74656, originally posted this answer in the question body in response to the accepted answer, as explained in this comment:

I used this and wrote a public method to call the code and return the boolean.

The OP's answer:

Code Used:

public bool tablesAreTheSame(DataTable table1, DataTable table2)
{
    DataTable dt;
    dt = getDifferentRecords(table1, table2);

    if (dt.Rows.Count == 0)
        return true;
    else
        return false;
}

//Found at http://canlu.blogspot.com/2009/05/how-to-compare-two-datatables-in-adonet.html
private DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable)
{
    //Create Empty Table     
    DataTable ResultDataTable = new DataTable("ResultDataTable");

    //use a Dataset to make use of a DataRelation object     
    using (DataSet ds = new DataSet())
    {
        //Add tables     
        ds.Tables.AddRange(new DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy() });

        //Get Columns for DataRelation     
        DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];
        for (int i = 0; i < firstColumns.Length; i++)
        {
            firstColumns[i] = ds.Tables[0].Columns[i];
        }

        DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];
        for (int i = 0; i < secondColumns.Length; i++)
        {
            secondColumns[i] = ds.Tables[1].Columns[i];
        }

        //Create DataRelation     
        DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);
        ds.Relations.Add(r1);

        DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);
        ds.Relations.Add(r2);

        //Create columns for return table     
        for (int i = 0; i < FirstDataTable.Columns.Count; i++)
        {
            ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType);
        }

        //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.     
        ResultDataTable.BeginLoadData();
        foreach (DataRow parentrow in ds.Tables[0].Rows)
        {
            DataRow[] childrows = parentrow.GetChildRows(r1);
            if (childrows == null || childrows.Length == 0)
                ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
        }

        //If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.     
        foreach (DataRow parentrow in ds.Tables[1].Rows)
        {
            DataRow[] childrows = parentrow.GetChildRows(r2);
            if (childrows == null || childrows.Length == 0)
                ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
        }
        ResultDataTable.EndLoadData();
    }

    return ResultDataTable;
}

Answer 5

Try to make use of linq to Dataset

(from b in table1.AsEnumerable()  
    select new { id = b.Field<int>("id")}).Except(
         from a in table2.AsEnumerable() 
             select new {id = a.Field<int>("id")})

Check this article : Comparing DataSets using LINQ

Answer 6

    /// <summary>
    /// https://stackoverflow.com/a/45620698/2390270
    /// Compare a source and target datatables and return the row that are the same, different, added, and removed
    /// </summary>
    /// <param name="dtOld">DataTable to compare</param>
    /// <param name="dtNew">DataTable to compare to dtOld</param>
    /// <param name="dtSame">DataTable that would give you the common rows in both</param>
    /// <param name="dtDifferences">DataTable that would give you the difference</param>
    /// <param name="dtAdded">DataTable that would give you the rows added going from dtOld to dtNew</param>
    /// <param name="dtRemoved">DataTable that would give you the rows removed going from dtOld to dtNew</param>
    public static void GetTableDiff(DataTable dtOld, DataTable dtNew, ref DataTable dtSame, ref DataTable dtDifferences, ref DataTable dtAdded, ref DataTable dtRemoved)
    {
        try
        {
            dtAdded = dtOld.Clone();
            dtAdded.Clear();
            dtRemoved = dtOld.Clone();
            dtRemoved.Clear();
            dtSame = dtOld.Clone();
            dtSame.Clear();
            if (dtNew.Rows.Count > 0) dtDifferences.Merge(dtNew.AsEnumerable().Except(dtOld.AsEnumerable(), DataRowComparer.Default).CopyToDataTable<DataRow>());
            if (dtOld.Rows.Count > 0) dtDifferences.Merge(dtOld.AsEnumerable().Except(dtNew.AsEnumerable(), DataRowComparer.Default).CopyToDataTable<DataRow>());
            if (dtOld.Rows.Count > 0 && dtNew.Rows.Count > 0) dtSame = dtOld.AsEnumerable().Intersect(dtNew.AsEnumerable(), DataRowComparer.Default).CopyToDataTable<DataRow>();
            foreach (DataRow row in dtDifferences.Rows)
            {
                if (dtOld.AsEnumerable().Any(r => Enumerable.SequenceEqual(r.ItemArray, row.ItemArray))
                    && !dtNew.AsEnumerable().Any(r => Enumerable.SequenceEqual(r.ItemArray, row.ItemArray)))
                {
                    dtRemoved.Rows.Add(row.ItemArray);
                }
                else if (dtNew.AsEnumerable().Any(r => Enumerable.SequenceEqual(r.ItemArray, row.ItemArray))
                    && !dtOld.AsEnumerable().Any(r => Enumerable.SequenceEqual(r.ItemArray, row.ItemArray)))
                {
                    dtAdded.Rows.Add(row.ItemArray);
                }
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine(ex.ToString());
        }
    }

Answer 7

Inspired by samneric's answer using DataRowComparer.Default but needing something that would only compare a subset of columns within a DataTable, I made a DataTableComparer object where you can specify which columns to use in the comparison. Especially great if they have different columns/schemas.

DataRowComparer.Default works because it implements IEqualityComparer. Then I created an object where you can define which columns of the DataRow will be compared.

public class DataTableComparer : IEqualityComparer<DataRow>
{
    private IEnumerable<String> g_TestColumns;
    public void SetCompareColumns(IEnumerable<String> p_Columns)
    {
        g_TestColumns = p_Columns; 
    }

    public bool Equals(DataRow x, DataRow y)
    {

        foreach (String sCol in g_TestColumns)
            if (!x[sCol].Equals(y[sCol])) return false;

        return true;
    }

    public int GetHashCode(DataRow obj)
    {
        StringBuilder hashBuff = new StringBuilder();

        foreach (String sCol in g_TestColumns)
            hashBuff.AppendLine(obj[sCol].ToString());               

        return hashBuff.ToString().GetHashCode();

    }
}

You can use this by:

DataTableComparer comp = new DataTableComparer();
comp.SetCompareColumns(new String[] { "Name", "DoB" });

DataTable celebrities = SomeDataTableSource();
DataTable politicians = SomeDataTableSource2();

List<DataRow> celebrityPoliticians = celebrities.AsEnumerable().Intersect(politicians.AsEnumerable(), comp).ToList();

Answer 8

How about merging 2 data tables and then comparing the changes? Not sure if that will fill 100% of your needs but for the quick compare it will do a job.

public DataTable GetTwoDataTablesChanges(DataTable firstDataTable, DataTable secondDataTable)
{ 
     firstDataTable.Merge(secondDataTable);
     return secondDataTable.GetChanges();
}

You can read more about DataTable.Merge()

here

Answer 9

There is nothing out there that is going to do this for you; the only way you're going to accomplish this is to iterate all the rows/columns and compare them to each other.

Answer 10

or this, I did not implement the array comparison so you will also have some fun :)

public bool CompareTables(DataTable a, DataTable b)
{
    if(a.Rows.Count != b.Rows.Count)
    {
        // different size means different tables
        return false;
    }

    for(int rowIndex=0; rowIndex<a.Rows.Count; ++rowIndex)
    {
        if(!arraysHaveSameContent(a.Rows[rowIndex].ItemArray, b.Rows[rowIndex].ItemArray,))
        {
            return false;
        }
    }

    // Tables have same data
    return true;
}

private bool arraysHaveSameContent(object[] a, object[] b)
{
    // Here your super cool method to compare the two arrays with LINQ,
    // or if you are a loser do it with a for loop :D
}

Answer 11

Well if you are using a DataTable at all then rather than comparing two 'DataTables' could you just compare the DataTable that is going to have changes with the original data when it was loaded AKA DataTable.GetChanges Method (DataRowState)

Answer 12

If you have the tables in a database, you can make a full outer join to get the differences. Example:

select t1.Field1, t1.Field2, t2.Field1, t2.Field2
from Table1 t1
full outer join Table2 t2 on t1.Field1 = t2.Field1 and t1.Field2 = t2.Field2
where t1.Field1 is null or t2.Field2 is null

All records that are identical are filtered out. There is data either in the first two or the last two fields, depending on what table the record comes from.