How to compare two data tables and get the different records into third data table.

Question

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 28-Jul-13 10:45am

sivared

Updated 15-Dec-20 20:25pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Winston Madiano · Answer 1 · 2013-07-28T14:30:00

Solution 1

Try the link below

looping two datatables[^]

Posted 28-Jul-13 14:30pm

Winston Madiano

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

sivared · Answer 2 · 2013-07-30T19:29:00

Solution 2

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.

Posted 30-Jul-13 19:29pm

sivared

Rajendra Galande · Answer 3 · 2020-12-15T20:25:00

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;
      }