CODEWITHSUNDEEP
c#sqldatasetdataadapterjoin
user1302054
user1302054

Reputation: 15

Joining 2 tables together and looping through using 2 different datasets

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"];

Upvotes: 0

Views: 509

Answers (1)

COLD TOLD
COLD TOLD

Reputation: 13579

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);

Upvotes: 1

Related Questions