Multiple Data Sources

[Damien McGivern]

Hi,
I'm not very clued up on ADO.Net so forgive me if this is an obvious
question.

Is it possible to create a single dataset from multiple data sources?

I have a main database which I am only allowed read-only access and I'm not
able to get extra tables added. I want to use the user information stored on
the main database with an extra database that I'll have full access over -
is this possible and if so could someone give me some pointers as to how to
implement it.

Using version 1.1 of the .Net framework

Cheers
Dee

 

[Miha Markic]

Hi Damien,

Sure, dataset is database independent.
Just conifugure dataadapters properly(data is filled/stored through
dataadapters).

 

[William Ryan]

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 .
Ideally, get them both because they are both packed with some really great
stuff and the authors both have a really cool way of communicating the
information.

HTH,

Bill

 

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