Perform JOIN in dataset?

I would like to pull data from two different databases into one dataset
(using a DataAdapter with different commands and connections, and
filling that one set with the different results). With this data, I
need to perform a JOIN to get the records from those two databases that
have a common id (WHERE table1.id = table2.id). How can I do this? I
couldn't do a JOIN with my original query that was used in the
SelectCommand portion of the DataAdapter because the OracleCommand only
takes one connection (to one database).

I don't want to get too fancy, so if I can keep it simple it would be
better for me. I'm also using C# and not VB.

Thanks!
Christine

Cor
Wed Oct 26 11:18:41 CDT 2005

Christine,

In my opinion you need to get with net 1.x two datasets with tables with
exactly the same names and use a merge, after that you have added to both
tables the right and same columns and given them the same names.

You need to have in both the same primary key which is as that in the
datatables.

If it is for Net 2.0 than it is easier because in that you can merge direct
two datatables where the names don't have to be the same.

I don't know if there are easier solutions.

I hope this gives helps.

Cor

W
Wed Oct 26 11:24:00 CDT 2005

Christine:

You can use a DataRelation to bind the tables to each other, and then you
can find the parent row you need, and use GetChildRows to iteratively grab
the related rows.
http://www.knowdotnet.com/articles/datarelation.html
http://search.msn.com/results.aspx?q=GetChildRows&FORM=I7AW
"Christine Y." <ceyusi13@gmail.com> wrote in message
news:1130342599.464627.92960@o13g2000cwo.googlegroups.com...
>I would like to pull data from two different databases into one dataset
> (using a DataAdapter with different commands and connections, and
> filling that one set with the different results). With this data, I
> need to perform a JOIN to get the records from those two databases that
> have a common id (WHERE table1.id = table2.id). How can I do this? I
> couldn't do a JOIN with my original query that was used in the
> SelectCommand portion of the DataAdapter because the OracleCommand only
> takes one connection (to one database).
>
> I don't want to get too fancy, so if I can keep it simple it would be
> better for me. I'm also using C# and not VB.
>
> Thanks!
> Christine
>

Christine
Wed Oct 26 13:08:26 CDT 2005

Thanks for the reply. Do I define this DataRelation after I fill my
DataSet? After reading the online info, I'm still confused on using
DataTables, DataColumns, DataRows, etc. Do I have to define each
column and specify rows in the DataSet in order to be able to
eventually use a DataRelation?

I've been filling one single DataSet based on different commands, which
are in turn based on different queries and connections. How do I go
about... I guess defining a database within my DataSet in order to use
the DataRelation?

Thanks again,
Christine

Christine
Wed Oct 26 17:28:41 CDT 2005

OK nevermind, decided to use only one database.

W
Wed Oct 26 20:53:45 CDT 2005

Christine - are you sure ? I'll gladly work through this if the complexity
is why you opted for doing just one db ;-)
"Christine Y." <ceyusi13@gmail.com> wrote in message
news:1130365721.102194.164130@g47g2000cwa.googlegroups.com...
> OK nevermind, decided to use only one database.
>

Adrian
Thu Oct 27 03:46:39 CDT 2005

Christine,

You might be interested in the assembly I've been working on at
http://www.queryadataset.com. Besides INNER JOINS, it lets you perform
complex SQL SELECT statements including UNION, OUTER JOINS, GROUP BY,
HAVING, ORDER BY, sub-queries, etc against the tables in a dataset.

Adrian Moore
http://www.queryadataset.com


"Christine Y." <ceyusi13@gmail.com> wrote in message
news:1130342599.464627.92960@o13g2000cwo.googlegroups.com...
>I would like to pull data from two different databases into one dataset
> (using a DataAdapter with different commands and connections, and
> filling that one set with the different results). With this data, I
> need to perform a JOIN to get the records from those two databases that
> have a common id (WHERE table1.id = table2.id). How can I do this? I
> couldn't do a JOIN with my original query that was used in the
> SelectCommand portion of the DataAdapter because the OracleCommand only
> takes one connection (to one database).
>
> I don't want to get too fancy, so if I can keep it simple it would be
> better for me. I'm also using C# and not VB.
>
> Thanks!
> Christine
>

Sahil
Thu Oct 27 22:56:43 CDT 2005

>> Do I define this DataRelation after I fill my DataSet?

Not necessary - though your fill will happen faster without any
DataRelations present.

> Do I have to define each
> column and specify rows in the DataSet in order to be able to
> eventually use a DataRelation?

Only Columns.

> I've been filling one single DataSet based on different commands, which
> are in turn based on different queries and connections.

DataRelation dr = new DataRelation(..)
DataSet.relations.add(dr) <-- really it's that easy.

> ... I guess defining a database within my DataSet in order to use
> the DataRelation?

DataSet != Database !! :), so that don't make sense.


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------



"Christine Y." <ceyusi13@gmail.com> wrote in message
news:1130350106.669762.231660@g43g2000cwa.googlegroups.com...
> Thanks for the reply. Do I define this DataRelation after I fill my
> DataSet? After reading the online info, I'm still confused on using
> DataTables, DataColumns, DataRows, etc. Do I have to define each
> column and specify rows in the DataSet in order to be able to
> eventually use a DataRelation?
>
> I've been filling one single DataSet based on different commands, which
> are in turn based on different queries and connections. How do I go
> about... I guess defining a database within my DataSet in order to use
> the DataRelation?
>
> Thanks again,
> Christine
>

Christine
Fri Oct 28 14:21:46 CDT 2005

Thanks for the help.

>From what I've read, however, DataSets are kind of like mini
representations of a database, just in memory.... so is that wrong?
DataSets have DataTables, with DataRows and DataColumns... to me that
sounds like a small database representation.

Christine

Christine
Fri Oct 28 14:26:48 CDT 2005

Thanks for the help. What I meant about relating a DataSet to a
database: from what I've read, DataSets are kind of like mini
representations of a database, just in memory.... so is that wrong?
DataSets have DataTables, with DataRows and DataColumns... to me that
sounds like a small database representation of your data.

Christine

Sahil
Fri Oct 28 21:50:55 CDT 2005

Christine,

I like to say .. Databases are similar to Datasets. More or less your
understanding is correct, but as long as you remember that Datasets are a
tabular representation of completely in-memory data, and relations between
those tabular data.

I'd dare not say Datasets are the same as databases - because by that
definition dataviews are the same as views in a database - but the
differences between database views and dataviews is quite a bit.

I know I am splitting hairs, but just being crystal clear :)


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------



"Christine Y." <ceyusi13@gmail.com> wrote in message
news:1130527608.274857.299730@g43g2000cwa.googlegroups.com...
> Thanks for the help. What I meant about relating a DataSet to a
> database: from what I've read, DataSets are kind of like mini
> representations of a database, just in memory.... so is that wrong?
> DataSets have DataTables, with DataRows and DataColumns... to me that
> sounds like a small database representation of your data.
>
> Christine
>