Filling multiple tables in Dataset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know this has been asked several times before, but...

Q: How can I fill multiple tables in a Dataset using a SINGLE .Fill?

Assume I have three tables in the data source - T1, T2 and T3 - that are
related "hierarchially" by FK's: T1 <--FK T2 <-- FK T3 The keys are
SQL2K auto-numbered "identity" columns.

I need to populate my Dataset with one row from T1 and its related child
rows from T2 and T3. Obviously, I can do this by invoking .Fill three times,
but that seems inefficient...

I've read posts that say to use a stored procedure to return three result
sets. OK, but how could a stored procedure EFFICIENTLY do this? When it
selects the row from T1, it will need to interrogate the returned identity
column in order to use it to select from T2 (and so-on and so-on). It could
use temp tables (or table variables), but this really seems to be a lot of
work to do something that could be done with a simple JOIN.

===> IMHO, it seems that .Fill needs to be able to populate multiple tables
in the Dataset FROM A SINGLE RESULTSET. If this were possible, then we could
just use a single SELECT with a JOIN (with or without a stored procedure).

It seems like we're introducing complexity (and more resource usage) into
the SQL "side" in order to workaround a limitation of ADO.NET...

...or have I totally missed something here?

DT
 
Hi David,


You can fill multi-tables to a dataset with one call:

Dap = new SqlDataAdapter("Select * From T1; Select * From
T2; Select * From T3", CONNECTION_STRING);
Dap.Fill(ds);

However, you can't use one select query, Select * From T1,
then automatically fill content of T2 and T3 into dataset.

HTH

Elton Wang
(e-mail address removed)
 
Elton,

Yes, I know that I can do multiple selects. But how do I "constrain" the
2nd through nth selects based on what is returned by the previous select?
Basically, I need to do a SELECT JOIN ... , but doing that would only return
one result set.

So, starting with the premise that doing a SELECT JOIN was the most
efficient from the database's perspective, I wrote a stored procedure that
does the following...

1) Defines a TABLE variable @tJoined with columns from all five tables
2) does a single INSERT/SELECT JOIN into @tJoined
3) does multiple SELECT's from @tJoined to return five result sets.

Multiple steps, but at least I'm only hitting the database with one JOINed
SELECT... but maybe there's some other/better way???

(SP fragment is below; error checking removed)

David

-- the table variable to hold the joined result
DECLARE @tJoined TABLE
(
H_PK INT,
H_Id NVARCHAR(50),
H_floor INT,
H_photos NVARCHAR(50),
H_orientation NVARCHAR(9),
H_name NVARCHAR(100),
H_description NVARCHAR(100),
H_comments NVARCHAR(100),
F_HPK INT,
F_PK INT,
F_number INT,
F_location INT,
F_plan NVARCHAR(50),
F_name NVARCHAR(100),
F_description NVARCHAR(100),
F_comments NVARCHAR(100),
L_FPK INT,
L_PK INT,
L_number INT,
L_view NVARCHAR(5),
L_x DEC,
L_y DEC,
L_name NVARCHAR(100),
L_description NVARCHAR(100),
L_comments NVARCHAR(100),
V_LPK INT,
V_PK INT,
V_direction NVARCHAR(5),
V_photo INT,
V_name NVARCHAR(100),
V_description NVARCHAR(100),
V_comments NVARCHAR(100),
P_VPK INT,
P_PK INT,
P_number INT,
P_file NVARCHAR(100),
P_name NVARCHAR(100),
P_description NVARCHAR(100),
P_comments NVARCHAR(100)
)

-- INSERT the JOINed data from five tables into the table variable
-- use "AS" to rename columns in order to prevent name collisions between
tables

INSERT INTO @tJoined
SELECT H.House_PK AS H_PK, H.id AS H_Id, H.floor AS H_floor, H.photos AS
H_photos, H.orientation AS H_orientation, H.name AS H_name, H.description AS
H_description, H.comments AS H_comments,
F.House_PK AS F_HPK, F.Floor_PK AS F_PK, F.number AS F_number, F.location AS
F_location, F.[plan] AS F_plan, F.name AS F_name, F.description AS
F_description, F.comments AS F_comments,
L.Floor_PK AS L_FPK, L.Location_PK AS L_PK, L.number AS L_number, L.[view]
AS L_view, L.x AS L_x, L.y AS L_y, L.name AS L_name, L.description AS
L_description, L.comments AS L_comments,
V.Location_PK AS V_LPK, V.View_PK AS V_PK, V.direction AS V_direction,
V.photo AS V_photo, V.name AS V_name, V.description AS V_description,
V.comments AS V_comments,
P.View_PK AS P_VPK, P.Photo_PK AS P_PK, P.number AS P_number, P.[file] AS
P_file, P.name AS P_name, P.description AS P_description, P.comments AS
P_comments
FROM House H INNER
JOIN Floor F ON H.House_PK = F.House_PK
JOIN Location L ON F.Floor_PK = L.Floor_PK
JOIN [View] V ON L.Location_PK = V.Location_PK
JOIN Photo P ON V.View_PK = P.View_PK
WHERE H.id = @HouseId (@HouseID is a parameter to the SP)

-- return the five result sets: House, Floor, Location, View and Photo
-- use "AS" to rename the fields back to their database names
-- ADO.NET DataAdapter will use the five result sets to Fill a Dataset...

SELECT DISTINCT H_PK AS House_PK, H_id AS id, H_floor AS floor, H_photos AS
photos, H_orientation AS orientation, H_name AS name, H_description AS
description, H_comments AS comments FROM @tJoined

SELECT DISTINCT F_HPK AS House_PK, F_PK AS Floor_PK, F_number AS number,
F_location AS location, F_plan AS [plan], F_name AS name, F_description AS
description, F_comments AS comments FROM @tJoined

SELECT DISTINCT L_FPK AS Floor_PK, L_PK AS Location_PK, L_number AS number,
L_view AS [view], L_x AS x, L_y AS y, L_name AS name, L_description AS
description, L_comments AS comments FROM @tJoined

SELECT DISTINCT V_LPK AS Location_PK, V_PK AS View_Pk, V_direction AS
direction, V_photo AS photo, V_name AS name, V_description AS description,
V_comments AS comments FROM @tJoined

SELECT DISTINCT P_VPK AS View_PK, P_PK AS Photo_PK, P_number AS number,
P_file AS [file], P_name AS name, P_description AS description, P_comments AS
comments FROM @tJoined


RETURN 0
 
Hi David,

Apparently your question actually is how to generate
sequential queries that only depend on one condition.

I don't think you have to create a 'Joined table' to
achieve the final goal. You can try following direct
queries:

Select H.* from House H Where H.id = @HouseId;

Select F.* From Floor F
JOIN House H ON H.House_PK = F.House_PK
Where H.id = @HouseId;

Select L.* From Location L
JOIN Floor F On F.Floor_PK = L.Floor_PK
JOIN House H ON H.House_PK = F.House_PK
Where H.id = @HouseId;

Select V.* From [View] V
JOIN Location L ON L.Location_PK = V.Location_PK
JOIN Floor F On F.Floor_PK = L.Floor_PK
JOIN House H ON H.House_PK = F.House_PK
Where H.id = @HouseId;

Select P.* From Photo P
JOIN [View] V ON V.View_PK = P.View_PK
JOIN Location L ON L.Location_PK = V.Location_PK
JOIN Floor F On F.Floor_PK = L.Floor_PK
JOIN House H ON H.House_PK = F.House_PK
Where H.id = @HouseId;


Of course, you should replace * with specified fields.


HTH

Elton

-----Original Message-----
Elton,

Yes, I know that I can do multiple selects. But how do I "constrain" the
2nd through nth selects based on what is returned by the previous select?
Basically, I need to do a SELECT JOIN ... , but doing that would only return
one result set.

So, starting with the premise that doing a SELECT JOIN was the most
efficient from the database's perspective, I wrote a stored procedure that
does the following...

1) Defines a TABLE variable @tJoined with columns from all five tables
2) does a single INSERT/SELECT JOIN into @tJoined
3) does multiple SELECT's from @tJoined to return five result sets.

Multiple steps, but at least I'm only hitting the database with one JOINed
SELECT... but maybe there's some other/better way???

(SP fragment is below; error checking removed)

David

-- the table variable to hold the joined result
DECLARE @tJoined TABLE
(
H_PK INT,
H_Id NVARCHAR(50),
H_floor INT,
H_photos NVARCHAR(50),
H_orientation NVARCHAR(9),
H_name NVARCHAR(100),
H_description NVARCHAR(100),
H_comments NVARCHAR(100),
F_HPK INT,
F_PK INT,
F_number INT,
F_location INT,
F_plan NVARCHAR(50),
F_name NVARCHAR(100),
F_description NVARCHAR(100),
F_comments NVARCHAR(100),
L_FPK INT,
L_PK INT,
L_number INT,
L_view NVARCHAR(5),
L_x DEC,
L_y DEC,
L_name NVARCHAR(100),
L_description NVARCHAR(100),
L_comments NVARCHAR(100),
V_LPK INT,
V_PK INT,
V_direction NVARCHAR(5),
V_photo INT,
V_name NVARCHAR(100),
V_description NVARCHAR(100),
V_comments NVARCHAR(100),
P_VPK INT,
P_PK INT,
P_number INT,
P_file NVARCHAR(100),
P_name NVARCHAR(100),
P_description NVARCHAR(100),
P_comments NVARCHAR(100)
)

-- INSERT the JOINed data from five tables into the table variable
-- use "AS" to rename columns in order to prevent name collisions between
tables

INSERT INTO @tJoined
SELECT H.House_PK AS H_PK, H.id AS H_Id, H.floor AS H_floor, H.photos AS
H_photos, H.orientation AS H_orientation, H.name AS H_name, H.description AS
H_description, H.comments AS H_comments,
F.House_PK AS F_HPK, F.Floor_PK AS F_PK, F.number AS F_number, F.location AS
F_location, F.[plan] AS F_plan, F.name AS F_name, F.description AS
F_description, F.comments AS F_comments,
L.Floor_PK AS L_FPK, L.Location_PK AS L_PK, L.number AS L_number, L.[view]
AS L_view, L.x AS L_x, L.y AS L_y, L.name AS L_name, L.description AS
L_description, L.comments AS L_comments,
V.Location_PK AS V_LPK, V.View_PK AS V_PK, V.direction AS V_direction,
V.photo AS V_photo, V.name AS V_name, V.description AS V_description,
V.comments AS V_comments,
P.View_PK AS P_VPK, P.Photo_PK AS P_PK, P.number AS P_number, P.[file] AS
P_file, P.name AS P_name, P.description AS P_description, P.comments AS
P_comments
FROM House H INNER
JOIN Floor F ON H.House_PK = F.House_PK
JOIN Location L ON F.Floor_PK = L.Floor_PK
JOIN [View] V ON L.Location_PK = V.Location_PK
JOIN Photo P ON V.View_PK = P.View_PK
WHERE H.id = @HouseId (@HouseID is a parameter to the SP)

-- return the five result sets: House, Floor, Location, View and Photo
-- use "AS" to rename the fields back to their database names
-- ADO.NET DataAdapter will use the five result sets to Fill a Dataset...

SELECT DISTINCT H_PK AS House_PK, H_id AS id, H_floor AS floor, H_photos AS
photos, H_orientation AS orientation, H_name AS name, H_description AS
description, H_comments AS comments FROM @tJoined

SELECT DISTINCT F_HPK AS House_PK, F_PK AS Floor_PK, F_number AS number,
F_location AS location, F_plan AS [plan], F_name AS name, F_description AS
description, F_comments AS comments FROM @tJoined

SELECT DISTINCT L_FPK AS Floor_PK, L_PK AS Location_PK, L_number AS number,
L_view AS [view], L_x AS x, L_y AS y, L_name AS name, L_description AS
description, L_comments AS comments FROM @tJoined

SELECT DISTINCT V_LPK AS Location_PK, V_PK AS View_Pk, V_direction AS
direction, V_photo AS photo, V_name AS name, V_description AS description,
V_comments AS comments FROM @tJoined

SELECT DISTINCT P_VPK AS View_PK, P_PK AS Photo_PK, P_number AS number,
P_file AS [file], P_name AS name, P_description AS description, P_comments AS
comments FROM @tJoined


RETURN 0
.
Click to expand...
 
Elton,

Yes, that would achieve the same results. But I wonder which is faster:
doing five selects against the database or doing one select into a table
variable, and then doing five selects against the table variable?

Hmmm....

David
 
Back
Top