CODEWITHSUNDEEP
c#
hanzi_ru
hanzi_ru

Reputation: 153

compare two datatables with multiple columns with a unique id

I have written a program to compare two datatables with unique ids and create another datatable to insert certain columns which have the same id in common. I have demonstrated my requirement below.

These are the tables that needs to be compared:

enter image description here

enter image description here

And i need the output as below

enter image description here

but i receive an empty table as the result. I cannot understand where have i gone wrong. Could you please help me on this. I have provided my coding below.Please not that quantity and input are two datatables

DataTable result = new DataTable();
result.Columns.AddRange(new DataColumn[2] { new DataColumn("id"), new DataColumn("qty") });         


            foreach (DataRow row1 in input.Rows)
            {
                foreach (DataRow row2 in quantity.Rows)
                {
                    if (row1["id"].ToString() == row2["id"].ToString())
                    {
                        result.ImportRow(row2);
                    }
                    else
                    {
                        result.ImportRow(row1);
                    }
                }
            }

        return result;

Upvotes: 0

Views: 2953

Answers (2)

Sadique
Sadique

Reputation: 22821

You need a Left Join of 2 Data tables.

        DataTable dtinput = new DataTable();
        DataTable dtquantity = new DataTable();

        dtinput.Columns.Add("id",typeof(int));
        dtinput.Rows.Add("2");
        dtinput.Rows.Add("4");
        dtinput.Rows.Add("7");

        dtquantity.Columns.Add("id", typeof(int));
        dtquantity.Columns.Add("qty", typeof(int));
        dtquantity.Rows.Add("1", "12");
        dtquantity.Rows.Add("2", "13");
        dtquantity.Rows.Add("3", "5");
        dtquantity.Rows.Add("4", "6");
        dtquantity.Rows.Add("7", null);

        var results = from table1 in dtinput.AsEnumerable()
                      join table2 in dtquantity.AsEnumerable()
                      on (int)table1["id"] equals (int)table2["id"]
                      into outer
                      from row in outer.DefaultIfEmpty<DataRow>()
                      select row;
        DataTable dt = results.CopyToDataTable();

This diagram should help you in future:

enter image description here

Upvotes: 5

Oscar Bralo
Oscar Bralo

Reputation: 1907

Try something like this:

var result = input.Rows.Where(x => quantity.Rows.Amy(y => x == y));

I hope this helps!

Upvotes: 0

Related Questions