Returning complex data relationships from a stored procedure.

[Peter Rilling]

To minimize database calls, I would like to have a single stored procedure
return all the data necessary to display on my page. This information is
related from several different tables and what I would like to do is to
match the information loaded into a DataSet with the information in the
database. For instance, suppose that I have a Customer table and an Order
table. I would like to return a single customer and all their orders in a
single shot to display. I would like to take advantage of the DataSets
ability of handle relationships. What is the accepted practice for turning
the information from a stored procedure into a DataSet with complex
relationships? Is there any automatic process that will do this?

 

[Nicholas Paldino [.NET/C# MVP]]

Peter,

There isn't any automatic process to handle this. However, it's not
that hard to do. First, your stored procedure should return two result
sets. Those result sets are the parent and the child rows (do not join them
into one mega-table, keep them separate).

Then, on the client side, use the data set designer to create the typed
data set which includes the two tables. If the relationship between them
(foreign key and primary key) exists on the server, then when you drag the
tables to the data set designer, the relationship should be created for you
as well. If not, then create it yourself in the designer.

Then, all you have to do is create your data adapter with your call to
your stored procedure. Once you do that, you just pass an instance of your
typed data set to your adapter, and it should load the data correctly.

Hope this helps.