How to combine multiple datatable int one datatable?

Question

0.00/5 (No votes)

See more:

I want to compare multiple files using Windows Form Application in C#. These are the codes I've tried. The 3 foreach loop are working but when I add the 4th foreach loop it crashes without any errors. I wonder is there any other methods to combine multiple dataTables? I search through the internet they mostly are solutions for combining 2 dataTables.

As you can see I need to find the difference between the amounts from different table and make comparison.

Please help, thanks!

What I have tried:

public DataTable Comparison(DataTable A, DataTable B, DataTable C, DataTable D)
    {
        var tableC = new DataTable();

        tableC.Columns.Add(new DataColumn("Location"));
        tableC.Columns.Add(new DataColumn("Item Type"));
        tableC.Columns.Add(new DataColumn("Type"));
        tableC.Columns.Add(new DataColumn("PO Total Cost(Qty Received)"));
        tableC.Columns.Add(new DataColumn("Qty Received Actual Cost"));
        //tableC.Columns.Add(new DataColumn("Total Average Cost (Adjustment)"));
        //tableC.Columns.Add(new DataColumn("Qty Adjusted Average Cost"));
        tableC.Columns.Add(new DataColumn("Amount from FlatFile"));
        tableC.Columns.Add(new DataColumn("Amount (Transaction - Movement)"));
        tableC.Columns.Add(new DataColumn("Amount (Transaction - FlatFile)"));

        foreach (DataRow rowA in A.Rows)
        {
            foreach (DataRow rowB in B.Rows)
            {
                foreach (DataRow rowC in C.Rows)
                {                                                                                                
                    //foreach (DataRow rowD in D.Rows)
                    //{

                    if (rowA["Location"].ToString() == rowB["Location"].ToString() && rowA["Item Type"].ToString() == rowB["Item Type"].ToString() &&
                        rowA["Location"].ToString().Substring(0, 5).Trim() == rowC["Location"].ToString() && rowA["Item Type"].ToString() == rowC["Item_Type"].ToString())
                         //&& rowA["Location"].ToString() == rowD["Location"].ToString() && rowA["Item Type"].ToString() == rowD["Item Type"].ToString()
                         //&& rowB["Location"].ToString() == rowD["Location"].ToString() && rowB["Item Type"].ToString() == rowD["Item Type"].ToString())
                        {
                            var newRow = tableC.NewRow();

                            newRow["Location"] = rowB["Location"];
                            newRow["Item Type"] = rowB["Item Type"];
                            newRow["Type"] = rowC["Type"];
                            if (rowC["Type"].ToString() == "GRN")
                            {
                                newRow["PO Total Cost(Qty Received)"] = rowA["PO Total Cost(Qty Received)"];
                                newRow["Qty Received Actual Cost"] = rowB["Qty Received Actual Cost"];
                                newRow["Amount from FlatFile"] = rowC["Amount"];
                                newRow["Amount (Transaction - Movement)"] = Convert.ToDouble(rowA["PO Total Cost(Qty Received)"]) - Convert.ToDouble(rowB["Qty Received Actual Cost"]);
                                newRow["Amount (Transaction - FlatFile)"] = Convert.ToDouble(rowA["PO Total Cost(Qty Received)"]) - Convert.ToDouble(rowC["Amount"]);
                            }
                            else if (rowC["Type"].ToString() == "STK-ADJ")
                            {
                                //newRow["Qty Adjusted Average Cost"] = rowB["Qty Adjusted Average Cost"];
                                //newRow["Total Average Cost (Adjustment)"] = rowD["Total Average Cost3"];
                            }

                            tableC.Rows.Add(newRow);

                        }
                    }

                //}
            }
        }
        return tableC;
    }

Posted 8-Jan-19 20:17pm

Member 14112460

Updated 8-Jan-19 20:46pm

Add a Solution

2 solutions

Solution 1

See the answers here: How to combine more than one datatable to another datatable ?[^]

And here: asp.net - Merging the table from 3 dataset to 1 - Stack Overflow[^]

Another approach would be to first create a DataSet that selects from the tables you want, see: c# - How to fill Dataset with multiple tables? - Stack Overflow[^]

Posted 8-Jan-19 20:34pm

RickZeeland

Updated 8-Jan-19 20:49pm

v3

Comments

Member 14112460 9-Jan-19 2:37am

but in my excel files, I have two primary key, I need the same Location and Item Type then retrieve their amount and find the difference.

Maciej Los 9-Jan-19 2:47am

Please, see my answer.

Maciej Los 9-Jan-19 2:48am

Nice try, but i think OP needs something else. Please, see my answer, Rick.

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)

Maciej Los · Accepted Answer · 2019-01-08T20:46:00

Yes, you can join the tables by using Linq[^] and then you'll be able to compare row by row.

C#

var resultset = (from d1 in datatable1.AsEnumerable()
    join d2 in datatable2.AsEnumerable() on new {d1.Field<string>("Location"), d1.Field<string>("Item Type")} equals new {d2.Field<string>("Location"), d2.Field<string>("Item Type")} 
    join d3 in datatable3.AsEnumerable()  on new {d1.Field<string>("Location"), d1.Field<string>("Item Type")} equals new {d2.Field<string>("Location"), d2.Field<string>("Item Type")} 
    select new {
        Location = d1.Field<string>("Location"),
        ItemType = d1.Field<string>("Item Type"),
        Type = d1.Field<string>("Type"),
        Cost1 = d1.Field<double>("Cost"),
        Cost2 = d2.Field<double>("Cost"),
        Cost3 = d3.Field<double>("Cost")
    }).ToList();

foreach(var data in resultset)
{
    if (data.Type == "GRN")
    {
        //your logic here
    }
}

For further details, please see:
Querying DataSets (LINQ to DataSet) | Microsoft Docs[^]
LINQ to DataSet Examples | Microsoft Docs[^]
Creating a DataTable From a Query (LINQ to DataSet) | Microsoft Docs[^]
101 LINQ Samples in C#[^]