Joining 2 tables together and looping through using 2 different datasets

Question

I have the following 2 queries in a C# windows app from database tables on different SQL servers. I need to get the Order, LineNo and Quantity from one table and the price for that order and line no within another table on a different server table to the loop through and insert the results into a new table.

Everything works fine apart from I cannot get this price value for each line order as its in another table

How can I join the two tables up? will somthing like below work?

Note in the loop the variable to get the price

decimal decPrice = (decimal)dsqry2.Tables[0].Rows[intCountOrders]["Price"];

is from another dataset

string strQuery1 = "SELECT ORDER, LINENO, QUANTITY FROM ORDERS";

SqlDataAdapter qry1Adapter = new SqlDataAdapter(strQuery1, sqldb);

DataSet dsqry1 = new DataSet();

qry1Adapter.Fill(dsqry1);

string strQuery2 = "SELECT ORDER, LINENO, PRICE FROM PRICES";

SqlDataAdapter qry2Adapter = new SqlDataAdapter(strQuery2, sql2db);

DataSet dsqry2 = new DataSet();

qry2Adapter.Fill(dsqry2);

if (dsqry1.Tables[0].Rows.Count > 0)
{
    int intCountOrders = 0;

    while (intCountOrders < dsqry1.Tables[0].Rows.Count)
    {
         string strPartNo = dsqry1.Tables[0].Rows[intCountOrders]["Order"].ToString();
         string strLineNo = dsqry1.Tables[0].Rows[intCountOrders]["LineNo"].ToString();
         int intQuantity = (int)dsqry1.Tables[0].Rows[intCountOrders]["Quantity"];

         decimal decPrice = (decimal)dsqry2.Tables[0].Rows[intCountOrders]["Price"];

http://stackoverflow.com/questions/331265/how-to-use-datarelation-to-perform-a-join-on-two-datatables-in-a-dataset — Andrey Morozov, Jul 21 '13 at 19:45

COLD TOLD · Answer 1 · 2013-07-21T19:49:13.457

1

you can try using the merge

dsqry1.Merge(dsqry2);

http://msdn.microsoft.com/en-us/library/aa984388(v=vs.71).aspxenter

you can also try somethink like this

var joinedData =(from c in  dsqry1.Tables[0].AsEnumerable() select c).Union(from c 
in dsqry2.Tables[0].AsEnumerable() select c);

edited Jul 21 '13 at 19:49

answered Jul 21 '13 at 19:43

COLD TOLD

13,513
3
35
52

Thanks, it works fine apart from I get a can not cast error on the Price field (the only field being merged from dsqry2), any ideas on why this would be its from a float data type field – user1302054 Jul 24 '13 at 10:27

Joining 2 tables together and looping through using 2 different datasets

1 Answers1