none
How to compare two dataTables

    Question

  •  Hi

    I have to dataTable objects, i want to compare if they contains identical data or not, I used:
    dt1.Equals(dt2); //it didn't work
    i tried also:
    dt1 == dt2; // but it didn't work either

    can someone help me, i want to check if the have the same data or not

    thanks
    Monday, October 27, 2008 10:23 AM

Answers

  • Hi mahmoud mourad,

    Try the following code:

    1         private void button1_Click(object sender, EventArgs e)  
    2         {  
    3            // Something to do with the Initialization of the FirstDataTable and SecondDataTable  
    4  
    5             DataTable dt;  
    6             dt = getDifferentRecords(FirstDataTable, SecondDataTable);  
    7  
    8             if (dt.Rows.Count == 0)  
    9                 MessageBox.Show("Equal");  
    10             else 
    11                 MessageBox.Show("Not Equal");  
    12         }
    13
    14
    15
    16         #region Compare two DataTables and return a DataTable with DifferentRecords  
    17         /// <summary>  
    18         /// Compare two DataTables and return a DataTable with DifferentRecords  
    19         /// </summary>  
    20         /// <param name="FirstDataTable">FirstDataTable</param>  
    21         /// <param name="SecondDataTable">SecondDataTable</param>  
    22         /// <returns>DifferentRecords</returns>  
    23         public DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable)  
    24         {  
    25             //Create Empty Table  
    26             DataTable ResultDataTable = new DataTable("ResultDataTable");  
    27  
    28             //use a Dataset to make use of a DataRelation object  
    29             using (DataSet ds = new DataSet())  
    30             {  
    31                 //Add tables  
    32                 ds.Tables.AddRange(new DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy() });  
    33  
    34                 //Get Columns for DataRelation  
    35                 DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];  
    36                 for (int i = 0; i < firstColumns.Length; i++)  
    37                 {  
    38                     firstColumns[i] = ds.Tables[0].Columns[i];  
    39                 }  
    40  
    41                 DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];  
    42                 for (int i = 0; i < secondColumns.Length; i++)  
    43                 {  
    44                     secondColumns[i] = ds.Tables[1].Columns[i];  
    45                 }  
    46  
    47                 //Create DataRelation  
    48                 DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);  
    49                 ds.Relations.Add(r1);  
    50  
    51                 DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);  
    52                 ds.Relations.Add(r2);  
    53  
    54                 //Create columns for return table  
    55                 for (int i = 0; i < FirstDataTable.Columns.Count; i++)  
    56                 {  
    57                     ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType);  
    58                 }  
    59  
    60                 //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.  
    61                 ResultDataTable.BeginLoadData();  
    62                 foreach (DataRow parentrow in ds.Tables[0].Rows)  
    63                 {  
    64                     DataRow[] childrows = parentrow.GetChildRows(r1);  
    65                     if (childrows == null || childrows.Length == 0)  
    66                         ResultDataTable.LoadDataRow(parentrow.ItemArray, true);  
    67                 }  
    68  
    69                 //If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.  
    70                 foreach (DataRow parentrow in ds.Tables[1].Rows)  
    71                 {  
    72                     DataRow[] childrows = parentrow.GetChildRows(r2);  
    73                     if (childrows == null || childrows.Length == 0)  
    74                         ResultDataTable.LoadDataRow(parentrow.ItemArray, true);  
    75                 }  
    76                 ResultDataTable.EndLoadData();  
    77             }  
    78  
    79             return ResultDataTable;  
    80         }
    81         #endregion  
    82  



    Kind Regards,
    Guo Surfer
    Wednesday, October 29, 2008 7:09 AM

All replies

  • Hi mahmoud mourad,

    Try the following code:

    1         private void button1_Click(object sender, EventArgs e)  
    2         {  
    3            // Something to do with the Initialization of the FirstDataTable and SecondDataTable  
    4  
    5             DataTable dt;  
    6             dt = getDifferentRecords(FirstDataTable, SecondDataTable);  
    7  
    8             if (dt.Rows.Count == 0)  
    9                 MessageBox.Show("Equal");  
    10             else 
    11                 MessageBox.Show("Not Equal");  
    12         }
    13
    14
    15
    16         #region Compare two DataTables and return a DataTable with DifferentRecords  
    17         /// <summary>  
    18         /// Compare two DataTables and return a DataTable with DifferentRecords  
    19         /// </summary>  
    20         /// <param name="FirstDataTable">FirstDataTable</param>  
    21         /// <param name="SecondDataTable">SecondDataTable</param>  
    22         /// <returns>DifferentRecords</returns>  
    23         public DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable)  
    24         {  
    25             //Create Empty Table  
    26             DataTable ResultDataTable = new DataTable("ResultDataTable");  
    27  
    28             //use a Dataset to make use of a DataRelation object  
    29             using (DataSet ds = new DataSet())  
    30             {  
    31                 //Add tables  
    32                 ds.Tables.AddRange(new DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy() });  
    33  
    34                 //Get Columns for DataRelation  
    35                 DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];  
    36                 for (int i = 0; i < firstColumns.Length; i++)  
    37                 {  
    38                     firstColumns[i] = ds.Tables[0].Columns[i];  
    39                 }  
    40  
    41                 DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];  
    42                 for (int i = 0; i < secondColumns.Length; i++)  
    43                 {  
    44                     secondColumns[i] = ds.Tables[1].Columns[i];  
    45                 }  
    46  
    47                 //Create DataRelation  
    48                 DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);  
    49                 ds.Relations.Add(r1);  
    50  
    51                 DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);  
    52                 ds.Relations.Add(r2);  
    53  
    54                 //Create columns for return table  
    55                 for (int i = 0; i < FirstDataTable.Columns.Count; i++)  
    56                 {  
    57                     ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType);  
    58                 }  
    59  
    60                 //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.  
    61                 ResultDataTable.BeginLoadData();  
    62                 foreach (DataRow parentrow in ds.Tables[0].Rows)  
    63                 {  
    64                     DataRow[] childrows = parentrow.GetChildRows(r1);  
    65                     if (childrows == null || childrows.Length == 0)  
    66                         ResultDataTable.LoadDataRow(parentrow.ItemArray, true);  
    67                 }  
    68  
    69                 //If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.  
    70                 foreach (DataRow parentrow in ds.Tables[1].Rows)  
    71                 {  
    72                     DataRow[] childrows = parentrow.GetChildRows(r2);  
    73                     if (childrows == null || childrows.Length == 0)  
    74                         ResultDataTable.LoadDataRow(parentrow.ItemArray, true);  
    75                 }  
    76                 ResultDataTable.EndLoadData();  
    77             }  
    78  
    79             return ResultDataTable;  
    80         }
    81         #endregion  
    82  



    Kind Regards,
    Guo Surfer
    Wednesday, October 29, 2008 7:09 AM
  • Hi Guo Surfer

    Thanks for Your code it is very much helpful for me

    thanks & Regards
    Dhinesh paramasivam
    Monday, December 08, 2008 6:43 AM
  • I got an error when add relationship for data columns -- "Cannot have more than 32 columns"

    DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);  

    I have 54 columns in the datatable.

    Any ideas to fix this? Please Help! Thanks you!

    Thursday, May 13, 2010 3:51 PM
  • Actually, I only need to compare the first 4 column values, but I do need to add different rows( whole row ,52 columns ) to the result datatable. How do I do that?
    Thursday, May 13, 2010 4:04 PM
  • Hi,

    I encounter the same need, that is comparing on more than 32 columns. Did you find a workaround please?

    Friday, December 24, 2010 4:28 PM
  • You can try this function
    ComareDataTables(DTable1,DTable2);

    private bool CompareDataTables(DataTable DT1, DataTable DT2)
            {
                if ((DT1 == null) && (DT2 == null))
                    return true;
                else if ((DT1 != null) && (DT2 != null))
                {
                    if (DT1.Rows.Count == DT2.Rows.Count)
                    {
                        if (DT1.Columns.Count == DT2.Columns.Count)
                        {
                            for (int i = 0; i < DT1.Rows.Count; i++)
                            {
                                for(int j = 0; j<DT1.Columns.Count; j++)
                                {
                                    if (DT1.Rows[i][j].ToString() != DT2.Rows[i][j].ToString())
                                        return false;
                                }
                            }
                            return true;
                        }
                        else
                            return false;
                    }
                    else
                        return false;
                }
                else
                    return false;
            }
    Thursday, March 29, 2012 6:35 PM
  • using System;
    using System.Data;
    using System.Linq;
    
    class Program
    {
        static void CompareRows(DataTable table1, DataTable table2)
        {
    	foreach (DataRow row1 in table1.Rows)
    	{
    	    foreach (DataRow row2 in table2.Rows)
    	    {
    		var array1 = row1.ItemArray;
    		var array2 = row2.ItemArray;
    
    		if (array1.SequenceEqual(array2))
    		{
    		    Console.WriteLine("Equal: {0} {1}", row1["Drug"], row2["Drug"]);
    		}
    		else
    		{
    		    Console.WriteLine("Not equal: {0} {1}", row1["Drug"], row2["Drug"]);
    		}
    	    }
    	}
        }
    
        static DataTable GetTable1()
        {
    	DataTable table = new DataTable();
    	table.Columns.Add("Dosage", typeof(int));
    	table.Columns.Add("Drug", typeof(string));
    	table.Columns.Add("Patient", typeof(string));
    
    	table.Rows.Add(25, "Indocin", "David");
    	table.Rows.Add(50, "Enebrel", "Cecil");
    	return table;
        }
    
        static DataTable GetTable2()
        {
    	DataTable table = new DataTable();
    	table.Columns.Add("Dosage", typeof(int));
    	table.Columns.Add("Drug", typeof(string));
    	table.Columns.Add("Patient", typeof(string));
    
    	table.Rows.Add(21, "Combivent", "Janet");
    	table.Rows.Add(50, "Enebrel", "Cecil");
    	table.Rows.Add(10, "Hydralazine", "Christoff");
    	return table;
        }
    
        static void Main()
        {
    	CompareRows(GetTable1(), GetTable2());
        }
    }
    
    Output
    
    Not equal: Indocin Combivent
    Not equal: Indocin Enebrel
    Not equal: Indocin Hydralazine
    Not equal: Enebrel Combivent
    Equal: Enebrel Enebrel
    Not equal: Enebrel Hydralazine

    scsm 2012 answer

    Sunday, September 02, 2012 8:06 AM
  • Dear mahmoud mourad

    Thanks a lot for this solution!

    Perfect

    Thursday, January 31, 2013 10:17 AM