multipal datatables

[jvcoach23]

I have a dataset that holds data from a sql command from server1. This dataset holds some stats on users and has a column called dbid. I'd like to use another database and go against server2 to bring back the dbid and the dbname. The result I want is to be able to use the stats that are on server1 and the dbname on server2 joing the two tables together in code. I don't want to use a linked server to get this info, I"d like to have vb do the join in a dataset. Can someone help me out. I've been doing the research and think I need to use dataTableMapping but can't get my mind around it. So the dataset.table that I have are one called stats and one called database. Any help would be great.

 

thanks

shannon

[PlausiblyDamp]

Probably the best way would to be load the data into two seperate DataTables within the same DataSet. You could then create a DataRelation and add this to the DataSet's Relations collection.

This would allow you to write code which could then navigate the relationship or alternatively the grid control for windows forms will display the relationship as well.

[jvcoach23]

ok.. that makes sense.. you wouldn't have an short example you could post do you..

[PlausiblyDamp]

Bit rough and ready but should give you the idea. In practice I would be more inclined to use stored procedures rather than hard coding the SELECT.... statements.

You may also want to consider using a strongly typed DataSet rather than how I've done it below....

'Just create 2 connections (1 per server)
Dim conn As New SqlConnection("Initial Catalog=Northwind;Integrated Security=true;Data Source=(local)")
'assign the correct connection to each data adapter
Dim daCustomers As New SqlDataAdapter("SELECT * FROM Customers", conn)
Dim daOrders As New SqlDataAdapter("SELECT * FROM Orders", conn)

Dim ds As New DataSet
daCustomers.Fill(ds, "Customers")
daOrders.Fill(ds, "Orders")

Dim dr As New DataRelation("CustomerOrders",     ds.Tables("Customers").Columns("CustomerID"), ds.Tables("Orders").Columns("CustomerID"))
ds.Relations.Add(dr)

DataGrid1.DataSource = ds.Tables("Customers")

[jvcoach23]

thanks for the help so far.. hope your still willing to help.

 

I see how the relation is made.. now to have the dataset return a combination of the two tables.. do you have to use mapping.. got any examples of that. I"m going to play around with the strongly typed..never used those before.. still learning. perhaps they are the answer.. anyway.. if you have input your willing to share. that woudl be great

 

thanks

shannon