Populate Dataset with tables from multiple databases

[rmatthew]

Hello folks!

 

This is my first post here so hope I follow convention:

 

What I am attempting to do is to gather data from multiple tables from 2 different databases into one dataset. Does anybody have a suggestion.

 

Thank you in advance.

[Nerseus]

Are you using SQL Server? You can join across databases in the JOIN clause in your SQL SELECT statement, such as:

 

SELECT t1.Column1, t2.Column2
FROM Database1..Table1 t1
INNER JOIN Database2..Table2 t2 ON t1.KeyCol = t2.KeyCol

 

The above uses the ".." syntax, which says skip the owner (usually dbo) of the table, more or less.

 

If the databases are on different servers, you can set up a link server and still do the above.

 

If you can't do the above, you can use the DataSet's Merge method to merge 2 different DataSet's into one.

 

-ner

[rmatthew]

Actually the project spec. should be that the program pulls datasets from two different databasese (possibly of different types - via a ODBC connection - 1 could be access - 1 could be SQL etc.) I need to combine tables from them.

 

What has happened is that during the years the customer has migrated some data and not others. They have moved tables etc. between here and there. For my particular application I am using two Access databases BUT I can see it comming where I will need to modify it to access to dissimilar ones.

[Thinker]

I believe your only hope is to link the other odbc datasources to

either Access (linked table) or SQL Server (linked server). If it

requires separate connection strings, you aren't going to get the

data into one dataset.

[Nerseus]

As I said above, you could always use the Merge method, but you wouldn't be able to Update the database when you were through - it would only allow you to view the two different tables at once through one DataSet. After the tables (or DataSets) are merged, you could manually add a DataRelation to implement a heirarchical view or whatever you needed. If you need both table's data to be seen as one table, you can merge the individual rows into one DataTable - similar to a fancy UNION :)

 

-ner

[rmatthew]

Looks as if merging the datasets and creating the relation is the way to go. I do need the 'union' to get one table/dataset if possible. Can you run a query on a merged dataset and create another dataset/table (like if the tables where in the same database to start with?

 

example:

 

Pull table1 from database1 into a dataset1

Pull table2 from database2 into a dataset2

Merge dataset2 into dataset1

 

Query the merged dataset (dataset1) to create a 'union' type query into a third dataset and dispose of the prior 2?

[Nerseus]

If I understand you, your "query" of the DataSet is to get them into one DataTable? That should be what Merge does - copies rows from one DataTable (say table2) into another DataTable (say table1). You have options of what to do when the columns aren't exactly the same (ignore, replace, etc.).

 

From your "example", what's wrong with disposing of DataSet2 and just using DataSet1? What kind of query are you trying to do?

 

If it's to filter rows, you could use a RowFilter (if it's a fairly simple WHERE clause type of filter).

 

-ner

[rmatthew]

I have a table what has 'personal information' it contains things like firts, middle and last name, employee id, etc.

 

I have another table that contains some other information but only has their employee id.

 

Both of these tables are in completely different databases.

 

What I want to do is add the first and last name etc. fields with the other information.

 

 

NOW - this is simplified as there are actually 4-5 tables from different databases that need to eventually end up togeather.

 

there doesn't need to be any edits or updates to the database. It is strictly for viewing / reporting.

[Nerseus]

Ah, sounds like you'll need to write your own "merge" type of function. Nothing built into DataSets will do what you want directly.

 

You'll probably have to loop through the columns of table2, add them to table1. Then loop through the rows of table2, filter table1 based on the employeeID of table2, then add the data from table2 to table1 manually.

 

Have fun!

-Nerseus

[robertsams23]

following link shows data from multiple tables using dataset.

 

http://vb.net-informations.com/dataset/dataset-multiple-tables-sqlserver.htm

 

robert.