Fill related tables in dataset with one query?

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

Guest

Thought this would be simple, but apparently its not. I have a
strongly-typed dataset with tables that have relations set in the DataSet
designer. I've been stymied when attempting to fill this dataset using a
join query. Without TableMappings, nothing is filled in the dataset. I
can't use table mappings, however, because it won't allow me to split one
result set among four different tables. I can create a tablemapping for each
table in the dataset, but the "source table" is the same in each one--the
result set. No two table mappings can have the same source table, so it
fails. If I was grabbing all data, it wouldn't be such a problem, as I could
fill each table in turn. However, I'm performing a search at the same time,
which means only relevant data is returned. Pulling it all down and then
sorting through everything is not feasable, since the database could
hypothetically contain millions of rows. What should I do? Use a
DataReader and fill the dataset manually? TIA.
 
William - if you alias the tables then you should be able to use the
TableMappings. You can also use a DataReader though and use the NextResult
method to advance to the next query, witha while Reader.Read() as the inner
loop
 
Sql 2k. Query is:

SELECT @RoomID AS RoomID, @RoomName AS RoomName, Cabinets.CabinetID AS
CabinetID, Cabinets.CabinetName AS CabinetName, Drawers.DrawerID AS DrawerID,
Drawers.DrawerName AS DrawerName, Folders.FolderID AS FolderID,
Folders.FolderName AS FolderName
FROM Folders INNER JOIN Drawers ON Folders.DrawerID = Drawers.DrawerID INNER
JOIN Cabinets ON Drawers.CabinetID = Cabinets.CabinetID
WHERE (Folders.FolderName LIKE @SearchText) OR (Drawers.DrawerName LIKE
@SearchText) OR (Cabinets.CabinetName LIKE @SearchText)
ORDER BY Cabinets.CabinetName, Drawers.DrawerName, Folders.FolderName

First 5 lines of result is:

111 fubar 53271 Fubar School District 53750 Sales 57304 Contracts
111 fubar 53271 Fubar School District 53750 Sales 57309 General Documents
111 fubar 53271 Fubar School District 53750 Sales 60844 Invoices and
Documentation
111 fubar 53271 Fubar School District 53750 Sales 57308 Other Sales
111 fubar 53271 Fubar School District 53750 Sales 57305 Proposals

I'm working to get the result set down in the query now. Since it's a join,
if a CabinetName matches the query, I get every record in a join with that
cabinet record and every related drawer and folder record. I need to do
something, because the return result may contain tens of thousands of
records. I'm thinking a WHERE EXISTS on all related tables would cut it
down, but that's got to be horribly inefficient and may not work properly....
 
Right .. and I had suspected that may have been the problem :)

What you need are "results within a resultset".

Write up a quick small application, which fills a dataset using dynamic SQL.

Use northwind and set the commandtext to this --

"Select * from Customers ; Select * from Orders"

See - the above is TWO sql statements, not one.

If you had to do the same in a stored proc, simply have the stored proc
select the above two.

Then it will fill two distinct tables in a dataset :)

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
I thought you were going in this direction. What you suggest will not work
in my case, because I am searching the database. To do what you suggest
would require me to run a query that looks more like:
Select CabinetID, CabinetName
from Cabinets
Where CabinetName LIKE @SearchText
UNION
Select CabinetID, CabinetName
from Cabinets INNER JOIN Drawers ON Drawers.CabinetID = Cabinets.CabinetID
Where Drawers.DrawerName LIKE @SearchText
UNION
etc etc etc

I'd have to do something similar to get the drawers and folders right as
well. Seems like this is a HUGE performance penalty JUST to get my data. It
seems that MS should have allowed for normalization when reading into
relational datasets from flat result sets. How hard is it to say "Take
unique results from these two columns and place them in that datatable, take
unique results from the next two and put them in this datatable, and place
the last two in the last datatable." Instead, I have to put ONE result set
into ONE datatable, which prevents anyone from easily putting a subset of a
relational database into a relational dataset. Seems like a big hole in ADO
to me.
 
True, but wouldn't the penalty be the same (except for a slight optimization
benefit) from pushing the convoluded sql statements into a sp?
 
No


William Sullivan said:
True, but wouldn't the penalty be the same (except for a slight
optimization
benefit) from pushing the convoluded sql statements into a sp?
Click to expand...
 
Back
Top