Distributed Data into Common Dataset

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

BonnieBerentCMVP
Sat Feb 19 00:11:02 CST 2005

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" wrote:

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

Ed
Sat Feb 19 03:54:23 CST 2005

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 [C# MVP]" <BonnieBerentCMVP@discussions.microsoft.com> wrote
in message news:8CF2683B-A7C7-4884-A740-DC2EEA5820B0@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
>>

BonnieBerentCMVP
Sat Feb 19 09:07:03 CST 2005

"Ed Warren" wrote:

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

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 [C# MVP]" <BonnieBerentCMVP@discussions.microsoft.com> wrote
> in message news:8CF2683B-A7C7-4884-A740-DC2EEA5820B0@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>
>