Perform JOIN in dataset?

[Christine Y.]

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 Ligthert [MVP]]

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.G. Ryan - MVP]

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

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 Y.]

OK nevermind, decided to use only one database.

 

[W.G. Ryan - MVP]

Christine - are you sure ? I'll gladly work through this if the complexity
is why you opted for doing just one db ;-)

 

[Adrian Moore]

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

 

[Sahil Malik [MVP]]

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.

 

[Christine Y.]

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 Y.]

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 Malik [MVP]]

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