Populating a dataset with a single select (with join)

  • Thread starter Thread starter Klas Mellbourn
  • Start date Start date
K

Klas Mellbourn

I have the following datamodel (implemented in SQL Server)

header -< detail

That is, I have two tables, "header" and "detail". There is a foreign key
from detail to header so that for each header there can be many details.

I have created a corresponding (strongly typed) dataset, with the two tables
and the relationship. (To be perfectly clear: one dataset containing two
tables)

Now, due to programmatic and performance reasons, I would like to populate
BOTH the tables in the dataset with a SINGLE sql select statement
(containing a join and further conditions). That is, I would like to
populate both tables using a single select statement similar to this:

SELECT Header.*, Detail.*
FROM Header INNER JOIN
Detail ON Header.ID = Detail.ID
WHERE (Detail.Foo > 52)

Is this possible using ADO.NET (or DAL for that matter)? How?

Thanks,
Klas Mellbourn
 
If you use the join like that, you'll complicate the update dramatically.
Instead, you can just use both SQL statements one after another - then fill
the dataset (since you are using SQL Server which supports Batch queries).
You'll have two tables filled this way

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
 
Back
Top