Selecting data from dataset

[bewa]

Hi,

Is there a way to select data from 2 tables ( join ) in a dataset?

I have 1 dataset, filled with two tables that contain data from 2 different servers. Is there a way to do a join select from both, for example "SELECT * FROM ds1.Table[1] inner join ds1.Table[2] on .... "

Thanks.

[DanielaTm]

No, but you can always create a stored procedure with all data you want and have the results in dataset.

[bewa]

That's just the issue. It are two different SQL servers. As far as I know you can't create stored procedures that get data from another server, or am I wrong?

[klintan]

Well, query in SQL Server can get data from another SQL Server.

To do it in C# code, you must add a data relation:

ds1.Relations.Add(ds1.Tables[1].Columns[...],ds.Tables[2].Columns[...]);

and then add columns to the child table

ds1.Tables[2].Columns.Add("NewCol",typeof(string), Parent.ParentCol);

You can then use the table or define a DataView for the table.

[DanielaTm]

You could also use OpenQuery and OpenDatabase functions to get data from another server.

http://msdn2.microsoft.com/en-us/library/ms188427.aspx