Populating a dataset with a single select (with join)

[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

 

[William Ryan eMVP]

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