Distributed Data into Common Dataset

  • Thread starter Thread starter Ed Warren
  • Start date Start date
E

Ed Warren

Problem:
I have two databases db1, db2

db1 contains a table with a keyfield ID (ms Access -- oleDbconnection1)
db2 contains a table with a keyfield ID (SQL server -- SqlConnection1)

I have generated two datasets from these tables

DS1 and DS2

What I need is a Dataset (DS3) implementing the following logic

Select all the ID's from DS1 that have no matching ID's in DS2 then put them
in DS3


If I had the two tables in the same database I would have the following
query:


SELECT DS1.ID
FROM DS1 LEFT JOIN DS2 ON DS1.ID = DS2.ID
WHERE (((DS2.ID) Is Null));

Question, How do I do this in ADO.Net when the tables are not in the same
database (require different connection objects)


Thanks in advance

Ed Warren
 
Try this (off the top of my head, sorry if there's any typos):

DataRow[] rows;
DataRow row;

for (int i=0; i < ds1.Tables[0].Rows.Count)
{
rows = ds2.Tables[0].Select("keyfield = " + ds1.Tables[0]["keyfield"])
if (rows.Length == 0)
{
row = ds3.Tables[0].NewRow();
row["keyfield"] = ds1.Tables[0]["keyfield"];
ds3.Tables[0].Rows.Add(row);
}
}

~~Bonnie
 
Thanks for the suggestion, I think that will do it, however, I was hoping
there was a more 'elegant' solution using Ado.net.

Ed Warren.


Bonnie Berent said:
Try this (off the top of my head, sorry if there's any typos):

DataRow[] rows;
DataRow row;

for (int i=0; i < ds1.Tables[0].Rows.Count)
{
rows = ds2.Tables[0].Select("keyfield = " + ds1.Tables[0]["keyfield"])
if (rows.Length == 0)
{
row = ds3.Tables[0].NewRow();
row["keyfield"] = ds1.Tables[0]["keyfield"];
ds3.Tables[0].Rows.Add(row);
}
}

~~Bonnie

Ed Warren said:
Problem:
I have two databases db1, db2

db1 contains a table with a keyfield ID (ms Access -- oleDbconnection1)
db2 contains a table with a keyfield ID (SQL server -- SqlConnection1)

I have generated two datasets from these tables

DS1 and DS2

What I need is a Dataset (DS3) implementing the following logic

Select all the ID's from DS1 that have no matching ID's in DS2 then put
them
in DS3


If I had the two tables in the same database I would have the following
query:


SELECT DS1.ID
FROM DS1 LEFT JOIN DS2 ON DS1.ID = DS2.ID
WHERE (((DS2.ID) Is Null));

Question, How do I do this in ADO.Net when the tables are not in the same
database (require different connection objects)


Thanks in advance

Ed Warren
Click to expand...
Click to expand...
 
Ed Warren said:
Thanks for the suggestion, I think that will do it, however, I was hoping
there was a more 'elegant' solution using Ado.net.
Click to expand...

I doubt it ... at least I can't think of anything else. This is probably as
"elegant" as it gets. <g>

~~Bonnie

Ed Warren.


Bonnie Berent said:
Try this (off the top of my head, sorry if there's any typos):

DataRow[] rows;
DataRow row;

for (int i=0; i < ds1.Tables[0].Rows.Count)
{
rows = ds2.Tables[0].Select("keyfield = " + ds1.Tables[0]["keyfield"])
if (rows.Length == 0)
{
row = ds3.Tables[0].NewRow();
row["keyfield"] = ds1.Tables[0]["keyfield"];
ds3.Tables[0].Rows.Add(row);
}
}

~~Bonnie

Ed Warren said:
Problem:
I have two databases db1, db2

db1 contains a table with a keyfield ID (ms Access -- oleDbconnection1)
db2 contains a table with a keyfield ID (SQL server -- SqlConnection1)

I have generated two datasets from these tables

DS1 and DS2

What I need is a Dataset (DS3) implementing the following logic

Select all the ID's from DS1 that have no matching ID's in DS2 then put
them
in DS3


If I had the two tables in the same database I would have the following
query:


SELECT DS1.ID
FROM DS1 LEFT JOIN DS2 ON DS1.ID = DS2.ID
WHERE (((DS2.ID) Is Null));

Question, How do I do this in ADO.Net when the tables are not in the same
database (require different connection objects)


Thanks in advance

Ed Warren
Click to expand...
Click to expand...
Click to expand...
 
Back
Top