CODEWITHSUNDEEP
vb.netdatatablecompare
sharkyenergy
sharkyenergy

Reputation: 4183

Check whether two datatables contain the same data

I have two datatables that have the same structure. But the data in them might be different.

I need a quick way to check if the data is the same or different. I do not need to know what is different as one of them is the "new" datatable, and the other is the "old" one. If there are differences then the user will have to decide if he wants the new or the old.

What is a good way to compare them?

I only know to check cell by cell and compare them but there must be a faster and smarter way of doing it.

Upvotes: 2

Views: 1726

Answers (2)

TnTinMn
TnTinMn

Reputation: 11801

If you view the two tables as two sets of DataRows (where order does not matter as opposed to the accepted answer), then you can use the Enumerable.Except Method using DataRowComparer.Default as the equality comparer to determine the DataRows that differ. Then use the Enumerable.Any Method to determine if any differences where found.

Private Shared Function TableRowsAreTheSame(table1 As DataTable, table2 As DataTable) As Boolean
  Dim bothNull As Boolean = table1 Is Nothing AndAlso table2 Is Nothing
  Dim bothNotNull As Boolean = table1 IsNot Nothing AndAlso table2 IsNot Nothing


  Dim equalColumnAndRowCounts As Func(Of Boolean) = Function() (table1.Rows.Count = table2.Rows.Count) AndAlso (table1.Columns.Count = table2.Columns.Count)
  Dim equalFieldTypes As Func(Of Boolean) = Function()
                                              Dim typeAreEqual As Boolean
                                              For index As Int32 = 0 To table1.Columns.Count - 1
                                                typeAreEqual = table1.Columns(index).DataType.Equals(table2.Columns(index).DataType)
                                                If Not typeAreEqual Then Exit For
                                              Next
                                              Return typeAreEqual
                                            End Function


  Dim equalFieldValues As Func(Of Boolean) = Function()
                                                ' get rows in table1 that are Not in table2
                                                Dim differringRows As IEnumerable(Of DataRow) = Enumerable.Except(table1?.AsEnumerable(), table2?.AsEnumerable(), DataRowComparer.Default)
                                                Return (Not differringRows.Any)
                                              End Function



  Return bothNull OrElse
      (bothNotNull AndAlso equalColumnAndRowCounts() AndAlso equalFieldTypes() AndAlso equalFieldValues())


End Function

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460340

Of course you need to check every cell, but you can use DataRowComparer.Equals:

Public Shared Function AreTablesEqual(t1 As DataTable, t2 As DataTable) As Boolean
    If t1 Is Nothing AndAlso t2 Is Nothing Then Return True
    If t1 Is Nothing OrElse t2 Is Nothing Then Return False
    If t1.Columns.Count <> t2.Columns.Count Then Return False
    If t1.Rows.Count <> t2.Rows.Count Then Return False

    For i As Int32 = 0 To t1.Rows.Count - 1
        If Not DataRowComparer.Default.Equals(t1.Rows(i), t2.Rows(i)) Then Return False
    Next

    Return True
End Function

If anyone searches for the C# version:

public static bool AreTablesEqual(DataTable t1, DataTable t2)
{
    if (t1 == null && t2 == null) return true;
    if (t1 == null || t2 == null) return false;
    if (t1.Columns.Count != t2.Columns.Count || t1.Rows.Count != t2.Rows.Count) return false;

    for (int i = 0; i < t1.Rows.Count; i++)
    {
        if (!DataRowComparer.Default.Equals(t1.Rows[i], t2.Rows[i])) return false;
    }

    return true;
}

Upvotes: 5

Related Questions