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

User · Answer

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 lt DataRow gt        private IEnumerable lt String gt  g TestColumns      public void SetCompareColumns IEnumerable lt String gt  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      quot Name quot    quot DoB quot       DataTable celebrities   SomeDataTableSource    DataTable politicians   SomeDataTableSource2     List lt DataRow gt  celebrityPoliticians   celebrities AsEnumerable   Intersect politicians AsEnumerable    comp  ToList

User · Answer

Try to make use of linq to Dataset    from b in table1 AsEnumerable         select new   id   b Field lt int gt   id     Except           from a in table2 AsEnumerable                 select new  id   a Field lt int gt   id       Check this article   Comparing DataSets using LINQ

User · Answer

lt summary gt          https   stackoverflow com a 45620698 2390270         Compare a source and target datatables and return the row that are the same  different  added  and removed          lt  summary gt           lt param name  dtOld  gt DataTable to compare lt  param gt           lt param name  dtNew  gt DataTable to compare to dtOld lt  param gt           lt param name  dtSame  gt DataTable that would give you the common rows in both lt  param gt           lt param name  dtDifferences  gt DataTable that would give you the difference lt  param gt           lt param name  dtAdded  gt DataTable that would give you the rows added going from dtOld to dtNew lt  param gt           lt param name  dtRemoved  gt DataTable that would give you the rows removed going from dtOld to dtNew lt  param gt      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  gt  0  dtDifferences Merge dtNew AsEnumerable   Except dtOld AsEnumerable    DataRowComparer Default  CopyToDataTable lt DataRow gt                  if  dtOld Rows Count  gt  0  dtDifferences Merge dtOld AsEnumerable   Except dtNew AsEnumerable    DataRowComparer Default  CopyToDataTable lt DataRow gt                  if  dtOld Rows Count  gt  0  amp  amp  dtNew Rows Count  gt  0  dtSame   dtOld AsEnumerable   Intersect dtNew AsEnumerable    DataRowComparer Default  CopyToDataTable lt DataRow gt                 foreach  DataRow row in dtDifferences Rows                                if  dtOld AsEnumerable   Any r   gt  Enumerable SequenceEqual r ItemArray  row ItemArray                        amp  amp   dtNew AsEnumerable   Any r   gt  Enumerable SequenceEqual r ItemArray  row ItemArray                                          dtRemoved Rows Add row ItemArray                                     else if  dtNew AsEnumerable   Any r   gt  Enumerable SequenceEqual r ItemArray  row ItemArray                        amp  amp   dtOld AsEnumerable   Any r   gt  Enumerable SequenceEqual r ItemArray  row ItemArray                                          dtAdded Rows Add row ItemArray                                                     catch  Exception ex                        Debug WriteLine ex ToString

User · Answer

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 lt 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  quot ResultDataTable quot           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  lt  firstColumns Length  i                          firstColumns i    ds Tables 0  Columns i                      DataColumn   secondColumns   new DataColumn ds Tables 1  Columns Count           for  int i   0  i  lt  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  lt  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

User · Answer

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

User · Answer

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

User · Answer

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  lt  tbl1 Rows Count  i                  for   int c   0  c  lt  tbl1 Columns Count  c                          if   Equals tbl1 Rows i  c   tbl2 Rows i  c                            return false                        return true

[c#] How to compare 2 dataTables

Examples related to c#

Examples related to .net

Examples related to datatable