SELECT from DataSet Tables

[crazyvonzipper]

HI.

I have a situation where we need to pull data from multiple data sources into one dataset.

Once we have the data in one dataset we would like to somehow manage to execute some sort of [complex] select query on the data in order to generate a new DataTable object with the linked values.

This is tricky because the linking is not always the same, and the column Field Names might also differ.

EG>
[DataTable1]
- [PersonId]
- [FirstName]
- [Surname]
- [Gender]

.....

[DataTable2]
- [ID]
- [pID]
- [DNA_Type]
- [DNA_structure]

Now assume [DataTable1] resides in a Microsoft SQL Server database, while [DataTable2] resides in an access database.

-> I cannot import the data into MS SQL solely because it has to be used from the various data sources.

I am able to select all the values from [DataTable1] & [DataTable2] respectively - and add them to a dataset.

Since they are now in the dataset, what can I do to select a linked set of rows into a third datatable?

EG [DataTable1.PersonId] = [DataTable2.pID]

Any help would be greatly appreciated.

PS.

LINQ does not seem to fullfill my needs since I might end up having roughly 12 tables which needs linking.

[sandeepparekh9]

i think dataset doest not allow select from multiple datatable, correct me if i am wrong.

what could you do is,

iterate through each row of datatable1 ,
comparing each row with each row of other datatable2.

and copying the matched data to another datatable2.

[crazyvonzipper]

Hi @SandeepParekh9

I have tried that approach too but it seems to be too cumbersome.

I am developing in ASP.NET & C# in the server side.

i have tried the following:

1. Selecting all data from the differing datasources - loopting through them and adjusting the "linking" as the loop progresses.

2. I tried to user LINQ - but with LINQ the HTTPRequest bombs out due to virtual memory.

3. Importing all data to MS SQL and then linking all the tables up with a standard T SQL query - This approach worked, however it seems a bit slow.

...

As my stress levels increased and time flew by, I found a plugin for VS called QueryDataSet.

It seems to be able to do what I want it to do. but the source thereof is written in VB.NET and it is also quite constly.

After reading through their website I thought that there must obviously then be some sort of technique to run T SQL queries against a collection of tables

(The apprach makes sense to me considering a dataset is pretty much a collection of tables.)

Surely there must be a way of writing script that can be run against the dataset?

[Michael27]

Did someone try This