Compare two datatables in C#

Question

1.00/5 (1 vote)

See more:

I have two datatables and i need to compare two columns and delete some results .

datatable 1

ID      COD     Nº     letter

83       65      0        abc

21       28      0        abc

12       32      3        asd

12       55      8        asd

datatable 2

ID      DETAIL

15       33

21       2

12       3

i need to compare column ID of datatable 1 and datatable 2 and get in a new datatable the result:

datatable 3
ID      DETAIL     COD     Nº     letter

21         2         28      0        abc

12         3         32      3        asd

12         3         55     8        asd

What I have tried:

c#, linq and for, foreach and other tips...

var filter = from firstDt in ds.AsEnumerable()where !(from secondDt in dsDC.AsEnumerable() select secondDt["ID"]).Contains(firstDt["ID"].ToString())
                    select firstDt; 
                    DataTable resultDt = filter.CopyToDataTable();

Posted 5-Jan-18 3:34am

Member 13608602

Updated 7-Jan-18 1:06am

v5

Add a Solution

Comments

CHill60 5-Jan-18 9:40am

In the section "What I have tried" you are supposed to share the code that you have tried. You need to show us what you are using and tell us what went wrong so that we can help you. We are not going to write your code for you!

Member 13608602 5-Jan-18 9:45am

I tried that: but it not works

var filter = from firstDt in ds.AsEnumerable()where !(from secondDt in dsDC.AsEnumerable() select secondDt["Name"]).Contains(firstDt["Name"].ToString())
select firstDt;
DataTable resultDt = filter.CopyToDataTable();

Maciej Los 5-Jan-18 9:46am

In what aspect datatable 3 is new? Can you share your logic? Please, explain what causes that records with ID 21, 12, 12 are new... Why ID 15 is not treated as new record?

Member 13608602 5-Jan-18 9:50am

ID 15 is not threated becouse it is not in dtatable1(ID)

Maciej Los 5-Jan-18 15:49pm

But ID 83 is in datatable 1, but not in result datatable... (Use Reply widget, if you want to notify me about your comment).

3 solutions

Solution 1

Looks like a simple nested loop that you need:

C#

foreach() // loop over all rows of the table2 and get ID (ID_2)
{
    foreach() // loop over all rows of the table1 and get ID (ID_1)
    {
              if (ID_2 == ID_1)
                 // write this row in the table3
    }

}

Posted 5-Jan-18 3:47am

Leo Chapiro

Updated 5-Jan-18 3:48am

v2

Comments

Member 13608602 5-Jan-18 10:13am

but i need to add a detail column to datatable 3 and delete de rows dont mismatch with datatable 1, i did't kown how to do it.

Maciej Los 5-Jan-18 15:50pm

Great idea in pseudo-code. +5!

Leo Chapiro 6-Jan-18 5:01am

Thank you, Maciej!

Solution 3

This is a modified version of solution 2 with two examples, the first uses Linq fluent syntax and the second uses query syntax.

C#

private static void Main(string[] args)
{
    var dt1 = new DataTable();
    dt1.Columns.Add("ID", typeof(long));
    dt1.Columns.Add("COD", typeof(int));
    dt1.Columns.Add("No", typeof(int));
    dt1.Columns.Add("letter", typeof(string));
    dt1.Rows.Add(83, 65, 0, "abc");
    dt1.Rows.Add(21, 28, 0, "abc");
    dt1.Rows.Add(12, 32, 3, "asd");
    dt1.Rows.Add(12, 55, 88, "asd");

    var dt2 = new DataTable();
    dt2.Columns.Add("ID", typeof(long));
    dt2.Columns.Add("DETAIL", typeof(string));
    dt2.Rows.Add(15, 33);
    dt2.Rows.Add(21, 2);
    dt2.Rows.Add(12, 3);

    DataTable dtTemp = dt1.Clone();
    dtTemp.Columns.Add("DETAIL");

    DataTable resultDataTable =
        dt1.AsEnumerable()
            .Join(
                dt2.AsEnumerable(),
                f => f.Field<long>("ID"),
                s => s.Field<long>("ID"),
                (f, s) => new { f, s })
            .Select(
                a =>
                    dtTemp.LoadDataRow(
                        new object[]
                        {
                            a.f.Field<long>("ID"),
                            a.f.Field<int>("COD"),
                            a.f.Field<int>("No"),
                            a.f.Field<string>("letter"),
                            a.s.Field<string>("DETAIL")
                        },
                        false))
            .CopyToDataTable();
    //using query syntax
    DataTable resultDataTable2 = (from dataRows1 in dt1.AsEnumerable()
    join dataRows2 in dt2.AsEnumerable()
      on dataRows1.Field<long>("ID") equals dataRows2.Field<long>("ID")
        select
            dtTemp.LoadDataRow(
                new object[]
                {
                    dataRows1.Field<long>("ID"),
                    dataRows1.Field<int>("COD"),
                    dataRows1.Field<int>("No"),
                    dataRows1.Field<string>("letter"),
                    dataRows2.Field<string>("DETAIL")
                },
                false)).CopyToDataTable();
}

Posted 7-Jan-18 1:06am

George Swan

Updated 7-Jan-18 21:55pm

v4

Comments

Maciej Los 7-Jan-18 7:42am

Very nice Linq solution! 5ed!

One note: some fields are typeof(int) instead of string ;)
I really appreciate of LoadDataRow method.

George Swan 8-Jan-18 3:56am

Thanks Maciej. I agree that all the fields are not strings but the OP didn't say what type they were. I've updated the answer to include a version using query syntax and best-guessed the types.

Add a Solution

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

Karthik_Mahalingam · Accepted Answer · 2018-01-05T04:34:00

try

using System.Data;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("ID");
            dt1.Columns.Add("COD");
            dt1.Columns.Add("No");
            dt1.Columns.Add("letter");
            dt1.Rows.Add(83, 65, 0, "abc");
            dt1.Rows.Add(21, 28, 0, "abc");
            dt1.Rows.Add(12, 32, 3, "asd");
            dt1.Rows.Add(12, 55, 88, "asd");

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("ID");
            dt2.Columns.Add("DETAIL");
            dt2.Rows.Add(15, 33);
            dt2.Rows.Add(21, 2);
            dt2.Rows.Add(12, 3);

            DataTable dtFinal = dt1.Clone();
            dtFinal.Columns.Add("DETAIL");
            foreach (DataRow row in dt1.Rows)
            {
                DataRow[] rowsTarget = dt2.Select(string.Format("ID={0}",row["ID"]));
                if (rowsTarget != null && rowsTarget.Length > 0)
                {
                    string detail = rowsTarget[0]["DETAIL"].ToString();
                   var lst =  row.ItemArray.ToList();
                    lst.Add(detail);
                    dtFinal.Rows.Add(lst.ToArray());
                }
            } 

        }
 
    }
}

Compare two datatables in C#

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Compare two datatables in C#

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us