Compare multiple DataTables and show matched records in ASP.Net using C# and VB.Net

collegew...

SOLVED CLOSED ACTIVE ANSWERED
User: collegewebmasters0
Posted: on Jan 27, 2022 09:55 PM
Forum: Data Controls
Answer: 1
Views: 1421
Sample Code: Download

Is there a way to compare three datatables?

The three datatables will have the same columns, but I want to check the rows of SNAME in them which exist and which are not in the rest of the tables.

Also, the values of SNAME column and its respective all column values in all three are to be displayed in the output table as shown below.

Is there a tempting way to implement this?

**Table1**

DIP | SNAME | T1_350 | T1_1000 |

-------------------------------------

GOOD | A001 | 225.365 | 338.047 |

GOOD | A002 | 119.254 | 445.254 |

GOOD | A003 | 145.212 | 365.236 |

GOOD | A004 | 265.958 | 330.558 |

GOOD | A005 | 235.908 | 340.568 |

**Table2**

DIP | SNAME | T2_350 | T2_1000 |

-------------------------------------

GOOD | A001 | 244.223 | 368.554 |

GOOD | A002 | 112.211 | 487.587 |

GOOD | A003 | 225.565 | 302.548 |

GOOD | A004 | 154.542 | 421.364 |

GOOD | A005 | 166.254 | 365.356 |

**Table3**

DIP | SNAME | T3_350 | T3_1000 |

-------------------------------------

GOOD | A001 | 230.203 | 378.554 |

GOOD | A002 | 114.251 | 477.547 |

GOOD | A003 | 295.545 | 342.598 |

GOOD | A005 | 136.294 | 375.366 |

Output Should be:

**TableOut**

DIP | SNAME | T1_350 | T1_1000 | T2_350 | T2_1000 | T3_350 | T3_1000 |

-----------------------------------------------------------------------------

GOOD | A001 | 225.365 | 338.047 | 244.223 | 368.554 | 230.203 | 378.554 |

GOOD | A002 | 119.254 | 445.254 | 112.211 | 487.587 | 114.251 | 477.547 |

GOOD | A003 | 145.212 | 365.236 | 225.565 | 302.548 | 295.545 | 342.598 |

GOOD | A005 | 235.908 | 340.568 | 166.254 | 365.356 | 136.294 | 375.366 |

what i tried :

DataRow drRow;
for(int i=0;i<=dt3.Rows.Count;i++)
{
    drRow = dtOut.NewRow();
    drRow["DIP"] = dt3.Rows[i]["DIP"].ToString();
    drRow["SNAME"] = dt3.Rows[i][1].ToString();
    drRow["T1_350"] = dt1.Rows[i][2];
    drRow["T1_1000"] = dt1.Rows[i][3];
    drRow["T2_350"] = dt2.Rows[i][2];
    drRow["T2_1000"] = dt2.Rows[i][3];
    drRow["T3_350"] = dt3.Rows[i][2];
    drRow["T3_1000"] = dt3.Rows[i][3];
    dtOut.Rows.Add(drRow);
    dtOut.AcceptChanges();
}

it not work as expected. it mismatch values due to SNAME Missing Rows Value.

Pls suggest a better solution.

Thanks in Advn.

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Jan 28, 2022 06:25 AM
Report

Hi collegewebmas...,

Use Linq Join to compare multiple DataTable. Refer below code.

HTML

<asp:GridView runat="server" ID="gvDetails"></asp:GridView>

Namespaces

using System.Data;
using System.Linq;

VB.Net

Imports System.Data
Imports System.Linq

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt1 = new DataTable();
        dt1.Columns.AddRange(new DataColumn[4] { new DataColumn("DIP"),
                                                 new DataColumn("SNAME"),
                                                 new DataColumn("T1_350"),
                                                 new DataColumn("T1_1000")});
        dt1.Rows.Add("GOOD", "A001", "225.365", "338.047");
        dt1.Rows.Add("GOOD", "A002", "119.254", "445.254");
        dt1.Rows.Add("GOOD", "A003", "145.212", "365.236");
        dt1.Rows.Add("GOOD", "A004", "265.958", "330.558");
        dt1.Rows.Add("GOOD", "A005", "235.908", "340.568");

        DataTable dt2 = new DataTable();
        dt2.Columns.AddRange(new DataColumn[4] { new DataColumn("DIP"),
                                                 new DataColumn("SNAME"),
                                                 new DataColumn("T2_350"),
                                                 new DataColumn("T2_1000")});
        dt2.Rows.Add("GOOD", "A001", "244.223", "368.554");
        dt2.Rows.Add("GOOD", "A002", "112.211", "487.587");
        dt2.Rows.Add("GOOD", "A003", "225.565", "302.548");
        dt2.Rows.Add("GOOD", "A004", "154.542", "421.364");
        dt2.Rows.Add("GOOD", "A005", "166.254", "365.356");

        DataTable dt3 = new DataTable();
        dt3.Columns.AddRange(new DataColumn[4] { new DataColumn("DIP"),
                                                 new DataColumn("SNAME"),
                                                 new DataColumn("T3_350"),
                                                 new DataColumn("T3_1000")});
        dt3.Rows.Add("GOOD", "A001", "230.203", "378.554");
        dt3.Rows.Add("GOOD", "A002", "114.251", "477.547");
        dt3.Rows.Add("GOOD", "A003", "295.545", "342.598");
        dt3.Rows.Add("GOOD", "A005", "136.294", "375.366");

        DataTable dtOut = new DataTable();
        dtOut.Columns.AddRange(new DataColumn[8] { new DataColumn("DIP"),
                                                   new DataColumn("SNAME"),
                                                   new DataColumn("T1_350"),
                                                   new DataColumn("T1_1000"),
                                                   new DataColumn("T2_350"),
                                                   new DataColumn("T2_1000"),
                                                   new DataColumn("T3_350"),
                                                   new DataColumn("T3_1000")});

        var result = from d1 in dt1.AsEnumerable()
                        join d2 in dt2.AsEnumerable() on d1["SNAME"].ToString() equals d2["SNAME"].ToString()
                        join d3 in dt3.AsEnumerable() on d1["SNAME"].ToString() equals d3["SNAME"].ToString()
                        select new { d1, d2, d3 };

        foreach (var item in result)
        {
            DataRow dr = dtOut.NewRow();
            dr["DIP"] = item.d1["DIP"];
            dr["SNAME"] = item.d1["SNAME"];
            dr["T1_350"] = item.d1["T1_350"];
            dr["T1_1000"] = item.d1["T1_1000"];
            dr["T2_350"] = item.d2["T2_350"];
            dr["T2_1000"] = item.d2["T2_1000"];
            dr["T3_350"] = item.d3["T3_350"];
            dr["T3_1000"] = item.d3["T3_1000"];
            dtOut.Rows.Add(dr);
        }

        gvDetails.DataSource = dtOut;
        gvDetails.DataBind();
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt1 As DataTable = New DataTable()
        dt1.Columns.AddRange(New DataColumn(3) {
                             New DataColumn("DIP"),
                             New DataColumn("SNAME"),
                             New DataColumn("T1_350"),
                             New DataColumn("T1_1000")})
        dt1.Rows.Add("GOOD", "A001", "225.365", "338.047")
        dt1.Rows.Add("GOOD", "A002", "119.254", "445.254")
        dt1.Rows.Add("GOOD", "A003", "145.212", "365.236")
        dt1.Rows.Add("GOOD", "A004", "265.958", "330.558")
        dt1.Rows.Add("GOOD", "A005", "235.908", "340.568")

        Dim dt2 As DataTable = New DataTable()
        dt2.Columns.AddRange(New DataColumn(3) {
                             New DataColumn("DIP"),
                             New DataColumn("SNAME"),
                             New DataColumn("T2_350"),
                             New DataColumn("T2_1000")})
        dt2.Rows.Add("GOOD", "A001", "244.223", "368.554")
        dt2.Rows.Add("GOOD", "A002", "112.211", "487.587")
        dt2.Rows.Add("GOOD", "A003", "225.565", "302.548")
        dt2.Rows.Add("GOOD", "A004", "154.542", "421.364")
        dt2.Rows.Add("GOOD", "A005", "166.254", "365.356")

        Dim dt3 As DataTable = New DataTable()
        dt3.Columns.AddRange(New DataColumn(3) {
                             New DataColumn("DIP"),
                             New DataColumn("SNAME"),
                             New DataColumn("T3_350"),
                             New DataColumn("T3_1000")})
        dt3.Rows.Add("GOOD", "A001", "230.203", "378.554")
        dt3.Rows.Add("GOOD", "A002", "114.251", "477.547")
        dt3.Rows.Add("GOOD", "A003", "295.545", "342.598")
        dt3.Rows.Add("GOOD", "A005", "136.294", "375.366")

        Dim dtOut As DataTable = New DataTable()
        dtOut.Columns.AddRange(New DataColumn(7) {
                               New DataColumn("DIP"),
                               New DataColumn("SNAME"),
                               New DataColumn("T1_350"),
                               New DataColumn("T1_1000"),
                               New DataColumn("T2_350"),
                               New DataColumn("T2_1000"),
                               New DataColumn("T3_350"),
                               New DataColumn("T3_1000")})

        Dim result = From d1 In dt1.AsEnumerable()
                        Join d2 In dt2.AsEnumerable() On d1("SNAME").ToString() Equals d2("SNAME").ToString()
                        Join d3 In dt3.AsEnumerable() On d1("SNAME").ToString() Equals d3("SNAME").ToString()
                        Select New With {d1, d2, d3}

        For Each item In result
            Dim dr As DataRow = dtOut.NewRow()
            dr("DIP") = item.d1("DIP")
            dr("SNAME") = item.d1("SNAME")
            dr("T1_350") = item.d1("T1_350")
            dr("T1_1000") = item.d1("T1_1000")
            dr("T2_350") = item.d2("T2_350")
            dr("T2_1000") = item.d2("T2_1000")
            dr("T3_350") = item.d3("T3_350")
            dr("T3_1000") = item.d3("T3_1000")
            dtOut.Rows.Add(dr)
        Next

        gvDetails.DataSource = dtOut
        gvDetails.DataBind()
    End If
End Sub

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Compare multiple DataTables and show matched records in ASP.Net using C# and VB.Net

Company

Explore

Follow us

Contact Us