Retrieving heirarchical data from SQL into a DataSet

[DJ Miller]

We have a stored procedure that returns a data set that is the result of
several Joined tables. Is there any way to have that data fill an ADO.Net
DataSet object in hierarchical form, so that the DataSet has the same number
of DataTables as there are tables in the SP, with DataRelations already set
up?

Everything I've read so far indicates that an ADO.Net DataSet can be
structured with tables and relationships that look almost exactly like
tables and foreign key relationships in a SQL Server database, but there's
no method for simply telling ADO.Net to look at a set of SQL Server tables
and structure itself accordingly. It seems like something is missing
here...

 

[Joe Fallon]

I highly recommend ADO.Net by David Sceppa.
Very clearly written.

My understanding is that a SP based on joined tables will return a single
result set to a single ADO.Net table.
If there is a 2nd (3rd, 4th...) result set in the SP each is alos placed in
a single table.

So if you want the effect of a dataset looking like a mini-db, you have to
change your methodology.
1. build the required tables in code and set up all datatypes, defaults,
relationships, etc.
2. Add all the empty tables to a dataset.
3. Run single table based queries (or SPs) to fill each table in the
dataset.
4. Now, when you want to update the server, you can pass back the correct
tables.

There is more to it than that, but that is the idea.

 

[Ian Ringrose]

You could use "for xml" in your select.
Then fill the dataset from the xml that is returned.

This will not work for other databases.

 

[Michael Lang]

The next step Joe mentions is that you need to set up your constraints
between the tables. See the Relations property of the DataSet...

http://msdn.microsoft.com/library/d...frlrfsystemdatadatasetclassrelationstopic.asp

Also see the Constraints property of the DataTable...

http://msdn.microsoft.com/library/d...fsystemdatadatatableclassconstraintstopic.asp

Michael Lang, MCSD