Querying related tables within a dataset

[Damien McGivern]

This was originally posted under the subject: Multiple Data Sources

Haven't had much time to work on this lately but now understand the multiple
data sources problem. However I should have also explained that I need to
have this data related and the somehow pull a new data table from the
related data tables.

I was trying it out with some test code where I have 2 DataTables within a
single DataSet

DataTable 1 - users
userID - auto number (int) (pk)
userName - string

DataTable 2 - modules
moduleID - auto number (int) (pk)
userID - int (fk)
moduleName - string

and a relation linking the users.userID to modules.userID


Is it possible to query the DataSet and pull users and their modules
something like

SELECT users.userID, users.userName, modules.moduleID, modules.moduleName
FROM users INNER JOIN modules ON users.userID = modules.userID;

if so could I please get a quick example.


Cheers
McGiv

Damien:

I totally agree with Miha, but if you are new to ADO.NET, let me add a few
things:

The whole concept of a Dataset/DataTable is that they are totally decoupled
from a RDBMS. While a DataTable in a Dataset might look exactly like one in
your database, that's incidental. The DataAdapter is charged with moving
data back and forth to the datasource, so abstractly, think of a DataAdapter
as something like a car. It can take you to virtually unlimited different
places but a car doesn't have to take you anywhere and you never have to
leave where you are at.

You can create a Dataset for instance, who has a table created from an
Oracle Database, another from a SqlServer Database, another from a Sybase
database, another from a Delimmited Text file, another from an XML file and
another from an Array that you just whipped up in your app. Basically, as
long as you are willing to abide by the rules of populating a DataTable, you
can do whatever you want with it. For instance, you are probably familiar
with the DataAdapter.Fill method, in this article I wrote,
http://www.knowdotnet.com/articles/exceldatasource.html I'm pulling from an
Excel WorkSheet. I could easily use a different dataadapter to take this
datatable and move the data to a SQL Server. If you check out
www.connectionstrings.com you'll see a bunch of the different data sources
you can use. The other neat thing is that lets say that I just filled a
table of myDataSet using the method above. Then, I have a table that
corresponds in a 'parent child' manner but it sits on a SqlServer Db. I can
use a DataRelation to link them to each other and enforce integrity even
though they have absolutely no physical relationship in the real world. You
could even take the datatable I made from Excel and using a DataColumn, add
an autoincrement field(identiy in SqlServer), set it's seed and it's
increment value. The sky is really the limit.

If you want to do any serious ADO.NET programming, pick up Bill Vaughn's
excellent book http://www.betav.com/ or David Sceppa's equally great book

http://www.amazon.com/exec/obidos/tg/detail/-/0735614237/qid=1070839351/sr=1>-1/ref=sr_1_1/103-2895383-9036666?v=glance&s=books .

 

[IbrahimMalluf]

Hello Damien

I didn't see the original "Multiple Data Sources" post but I have some
simpler suggestions than trying to do these things on the client side.

You can create your joined query on the server side if you are using
SQL-Server as the main data source. If your other data is comming from
Oracle or other data sources you can link those other sources to SQL-Server
and treat them alnost like native sources. Got to books online and look up
Linked Servers.

If your data is all originating from SQL-Server databases the task is even
easier. If your application is going to be doing a lot of cross platform
work between disparate datasources I would talk to the DBA about linking
these sources up on the server side. It will make your client services code
less vulnerable to data source changes.


--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx

This was originally posted under the subject: Multiple Data Sources

Haven't had much time to work on this lately but now understand the multiple
data sources problem. However I should have also explained that I need to
have this data related and the somehow pull a new data table from the
related data tables.

I was trying it out with some test code where I have 2 DataTables within a
single DataSet

DataTable 1 - users
userID - auto number (int) (pk)
userName - string

DataTable 2 - modules
moduleID - auto number (int) (pk)
userID - int (fk)
moduleName - string

and a relation linking the users.userID to modules.userID


Is it possible to query the DataSet and pull users and their modules
something like

SELECT users.userID, users.userName, modules.moduleID, modules.moduleName
FROM users INNER JOIN modules ON users.userID = modules.userID;

if so could I please get a quick example.


Cheers
McGiv

one

http://www.amazon.com/exec/obidos/tg/detail/-/0735614237/qid=1070839351/sr=1>-1/ref=sr_1_1/103-2895383-9036666?v=glance&s=books .