Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
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
Updated 7-Jan-18 1:06am
v5
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).

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
    }

}
 
Share this answer
 
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!
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());
                }
            } 

        }
 
    }
}
 
Share this answer
 
Comments
Maciej Los 5-Jan-18 15:50pm    
5ed, even if OP does not explain why some records should e or not should be in final table.
Karthik_Mahalingam 5-Jan-18 20:06pm    
Thank you maciej.
Long time no see.

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();
}
 
Share this answer
 
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.

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