automate creation of multi-table select queries

[Jon Sequeira]

I'm looking for a streamlined way to select data from multiple related SQL
tables into a typed DataSet with constraints that require all linked rows to
be retrieved.

There are a couple of questions here.

1) Can multiple tables be returned by one DataAdapter.Fill operation? (I
guess this supposes there's a way to return multiple tables with one
SqlCommand.)

Here's a simple example of what I'd like to do. Suppose two tables, an order
table and an order_item table:

order
-----
id
order_date
etc

order_item
----------
product_id
order_id [a foreign key to order.id]
etc

I'd like to create a query builder that will know to fetch the appropriate
rows from order_item when a query is made against order. For example, say
the query

SELECT * FROM [order] WHERE [order].customer_name LIKE '%Jon%'

returns orders with ids 1, 6, and 345. I'd like to automatically generate a
second query that retrieves the related rows from order_item. I've written
this in the business layer, but I'd like the multiple queries to execute in
one stored procedure if possible, to save database round trips.

And, ideally, this effect would cascade to other tables related to
order_item and so on.

Any insights are much appreciated.

Thanks.

Jon

 

[Neil McKechnie]

Interesting question... never thought of trying that
before. However, I have now and it does work.

For example, I have a stored procedure:

CREATE PROCEDURE dbo.GetOrderInfo

AS
Select * from Orders
Select * from [Order Details]
RETURN


I drag and drop this from the server explorer and a
command object is build. Set the SelectCommand property
of a data adaptor to point to the command, and then add a
grid and a dataset to the form.

Add the following code to the load event of the form:

SqlDataAdapter1.Fill(DataSet1)
DataGrid1.SetDataBinding(DataSet1, "")

When the grid is displayed, there are two datatables in
the dataset called Table (containing Orders) and Table1
(containing Order Details).

By changing the TableMappings in the dataadaptor, you can
name these DataTables appropriately. You can generate
typed datasets from this etc. However, I can't yet see
how updates, deletes, and inserts could work.


Of course my test is using pre-defined queries, but you
could equally well use parameters in the queries to select
specific orders and related order items into your dataset.


Regards,
Neil.

 

[Neil McKechnie]

Modified stored procedure which is closer to what you want:

CREATE PROCEDURE dbo.GetOrderInfo
( @CompanyName nvarchar(255) )
AS
Select Orders.* from Orders, Customers
where Orders.CustomerID = Customers.CustomerID
AND Customers.CompanyName like @CompanyName
Select [Order Details].* from [Order Details], Orders,
Customers
where Orders.CustomerID = Customers.CustomerID
AND [Order Details].OrderID = Orders.OrderID
AND Customers.CompanyName like @CompanyName
RETURN

Otherwise there is no difference from before - the stored
procedure returns multiple data tables when executed from
a Fill method of a Data Adaptor.

Regards,

Neil