I'm not sure how well this is known, but a recent question and discussion on our NZ.Net mailing list makes me think that it might be worth a blog post, so here goes.

For an example I'll use a trivial data structure (from NorthWind).  Hopefully you can scale up the complexity to get the point!

Now say we have noticed our profits on Green Widgets are dropping but we are still shipping as many as we always have.  We suspect some foul play by one or more of our sales staff.  So, we need to find all the customers who have ordered Green Widgets in the 12 months and all the employees who made the sales.

A stored proc like this will return all the data we are interested in:

ALTER PROCEDURE dbo.FindDodgeySales
 (
  @months int,
  @productid int
 )
AS
 SET NOCOUNT ON 
 declare @d datetime
 declare @ords table (orderid int, customerid int, employeeid int)
 
 set @d = dateadd(m, - @months, getdate())
 
 insert @ords(orderid, customerid, employeeid)
 select distinct o.orderid, o.customerid, o.employeeid
 from orders o
 inner join [order details] od on od.orderid = o.orderid
 where orderdate > @d and od.productid = @productid
  
 select e.* 
 from employees e
 inner join @ords oo on e.employeeid = oo.employeeid
 
 select c.* 
 from customers c 
 inner join @ords oo on c.customerid = oo.customerid
 
 RETURN 

To retrieve this into a dataset, we use a SQLDataAdapter.  When you drop one of these on your form or component or whatever, you will see the Data Adapter wizard as normal.  Select the connection then choose 'Use existing stored procedures'.

Then select the stored proc for the select statement and click Finish:

Now you will need to modify the table mappings in the properties for the data adapter:

From here you select the dataset to use for mapping and for each table returned by the stored proc you map this to the appropriate table in the dataset.

Now, calling da.Fill(dataset) will fill all the tables.  Brilliant !

One trick - if you have constraints in your dataset, you need to make sure the tables you fill don't violate these constraints.  At runtime, you can turn constraints off while you fill by setting dataSet.EnforceConstraints = false.

I created this demo using VS2005 but it's basically identical to VS2003.

Enjoy!