Click here to Skip to main content
14,837,944 members
Please Sign up or sign in to vote.
2.33/5 (2 votes)
See more:
I have two datatables from databases in C#.Each data table have 59 columns. I have to compare those two data tables and return the third datatable into third table with new records inserted in table1, and updates in table1. I have tried many ways. I did not find any solution. I have tried Data relation logic to compare and retrieve the result. It worked for any tables except for the tables with more than 32 columns. Please help me on this. Thanks in advance.
Posted
Updated 15-Dec-20 20:25pm

Try the link below

looping two datatables[^]
   
Comments
sivared 29-Jul-13 0:04am
   
Thanks. But I am using the following code. Can you help me on this. If possible please provide me C# code. for looping through data tables.
public DataTable compareanotherlogic(DataTable FirstDatatable,DataTable SecondTable)
{

DataTable InsertTable = new DataTable("FinalInserts");

for (int i = 0; i < FirstDatatable.Columns.Count; i++)
{
InsertTable.Columns.Add(FirstDatatable.Columns[i].ColumnName, FirstDatatable.Columns[i].DataType);
}
var table3 = FirstDatatable.Copy();
table3.AcceptChanges();
table3.Merge(SecondTable);

var distinctRows = from row in table3.AsEnumerable()
where row.RowState != DataRowState.Modified
select row;

var distintTable = distinctRows.CopyToDataTable();

return distintTable;
}
sivared 29-Jul-13 0:09am
   
public DataTable CompareTables(DataTable first, DataTable second)
{
first.TableName = "FirstTable";

second.TableName = "SecondTable";

//Create Empty Table

DataTable table = new DataTable("Difference");

try
{
//Must use a Dataset to make use of a DataRelation object
using (DataSet ds = new DataSet())
{
ds.Tables.AddRange(new DataTable[] { first.Copy(), second.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 r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);

ds.Relations.Add(r);

//Create columns for return table

for (int i = 0; i < first.Columns.Count; i++)
{

table.Columns.Add(first.Columns[i].ColumnName, first.Columns[i].DataType);

}


//If First Row not in Second, Add to return table.

table.BeginLoadData();



foreach (DataRow parentrow in ds.Tables[0].Rows)
{

DataRow[] childrows = parentrow.GetChildRows(r);

if (childrows == null || childrows.Length == 0)

table.LoadDataRow(parentrow.ItemArray, true);

}

table.EndLoadData();

}
}
catch (Exception ex)
{

throw ex;

}

return table;
}

It is working for only tables with less than 32 columns only. If a table has more than 32 columns, it is not working. Please improve this solution to work for any number of columns. Thanks in advance.
Thanks7872 29-Jul-13 0:31am
   
You have copy pasted code from some where and now need help to edit it?

compare two datatables [microsoftdotnetsolutions.blogspot.in]
sivared 29-Jul-13 0:46am
   
Yes Rohan. I used the same code.I am beginner in C#. I am not expert. But it is not working for tables more than 32 columns.
Nivedita_Parihar 5-Aug-14 2:24am
   
Did anyone got the alternative for this problem. Even i am using DataRelation for comparing datatables but not abl to resolve this problem
I did table splitting into two one with less than 32 columns and the other with remaining columns. I compared the two tables from two databases and finally joined them into single.
   
public DataTable CompareTables(DataTable tbl1, DataTable tbl2)
      {
          DataTable dt3 = new DataTable();
          for (int i = 0; i < tbl1.Columns.Count; i++)
          {
              dt3.Columns.Add(tbl1.Columns[i].ColumnName);
          }
          int found = 0;

          for (int i = 0; i < tbl1.Rows.Count; i++)
          {
              found = 0;
              for (int k = 0; k < tbl2.Rows.Count; k++)
              {
                  if (Equals(tbl1.Rows[i][0], tbl2.Rows[k][0]))
                  {
                      found = 1;
                      DataRow dr = dt3.NewRow();
                      int hasValue = 0;
                      for (int c = 0; c < tbl1.Columns.Count; c++)
                      {
                          if (!Equals(tbl1.Rows[i][c], tbl2.Rows[k][c]))
                          {
                              dr[tbl2.Columns[c].ColumnName] = tbl2.Rows[k][c];
                              hasValue = 1;
                          }
                      }
                      if (hasValue == 1)
                      {
                          dt3.Rows.Add(dr);
                          hasValue = 0;
                      }
                  }
              }
              if (found == 0)
              {
                  DataRow dr = dt3.NewRow();
                  for (int c = 0; c < tbl1.Columns.Count; c++)
                  {
                      dr[tbl2.Columns[c].ColumnName] = tbl1.Rows[i][c];
                  }
                  dt3.Rows.Add(dr);
              }
          }

          return dt3;
      }
   
Comments
CHill60 16-Dec-20 3:50am
   
Who (7 years ago) said the datatables only contained strings? And the use of nested loops was also suggested 7 years ago.
An uncommented code dump does not make a good solution. You should add commentary that explains why your solution is different to those already posted - or stick to answering recent questions where the OP still needs help

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900