Querying Two data tables asp.net

Question

I have 2 tables each with same fields basically containing

table1.ItemCode table1.Qty

table2.ItemCode table2.Qty

i am querying these two tables from sql by the following command

SELECT  c.Code ,
        t1.Code ,
        t1.Qty ,
        t2.Code ,
        t2.Qty
FROM    ( SELECT    Code
          FROM      dbo.Table1
          UNION
          SELECT    Code
          FROM      dbo.Table2
        ) c
        LEFT OUTER JOIN dbo.Table1 t1 ON c.Code = t1.Code
        LEFT OUTER JOIN dbo.Table2 t2 ON c.Code = t2.Code
WHERE   t1.Code IS NULL
        OR t2.Code IS NULL
        OR t1.Qty <> t2.Qty

this query provides me with the item codes that exist in both tables

that have only different quantities

for example if item: x has qty 2 and in the second table Item x has qty 4 this item would show as: x 2 4

however if Item x has qty 2 and in the second table also the same qty this item will not appear in the result

the problem is that in my situation these 2 tables are two data Tables in my asp.net

project

i need to execute the same query but on these two data tables

how can that be done or is their any other possible solution to get my result from these 2

data tables

Not an answer, but starting with converting your DataTables to IEnumerable ( dt.Rows.Cast(); ) you should be able to query using linq. — jbl, Jan 22 '13 at 14:53
@SteveB i havent tried because i know that you need linq to query a datatable but i am new to development and i am not familiar with the langauge — nayef harb, Jan 22 '13 at 15:18
Please, at least, show a bit of code. We don't know how you structurate your tables, etc... — Steve B, Jan 22 '13 at 15:26
[Check this link](http://stackoverflow.com/questions/9938524/querying-datatable-with-where-condtion), may be helpful — Pugazh, Jan 22 '13 at 17:33

score 0 · Answer 1 · answered Apr 04 '15 at 08:58

This is quite straightforward.

DataTable table1; 
DataTable table2;

//Initialize your DataTables here

var result = (from row1 in table1.AsEnumerable()
                join row2 in dataTable2.AsEnumerable() on row1["Code"] equals row2["Code"]
                where !object.Equals(row1["Qty"], row2["Qty"])
                select new { Code = row1["Code"], table1Qty = row1["Qty"], table2Qty = row2["Qty"] })
                .ToArray();

Rows from the two tables are joined on Code.

join row2 in dataTable2.AsEnumerable() on row1["Code"]

Subsequently, rows with the same Qty values are filtered

where !object.Equals(row1["Qty"], row2["Qty"])

Querying Two data tables asp.net

1 Answers1