SELECT parent with all child rows with Typed Datasets

[morleyc]

Hi, i have seen some example code which uses a stored parameter that
performs two selects and returns a SafeDataReader, a customer is
retrieved along with all of their orders. The stored procedure is
below:

SELECT CustomerID, CompanyName, ContactName, Country
FROM Customers
WHERE (CustomerID = @CompanyID)

SELECT OrderID, ShippedDate, Freight
FROM Orders
WHERE (CustomerID = @CompanyID)

What i need is a multi-table query but i want to use the typed
datasets in Visual Studio 2005. However, I cant seem to add that in
the dataset designer, i can only create queries for selecting single
values and not selecting rows as i need (that option is greyed out).

So currently i am doing the following, in code:

CustomersTableAdapter sessions = new CustomersTableAdapter ();
int customerID = sessions.GetDataByCustomerID( search_criteria_here )
[0].CustomerID;

OrdersTableAdapter details = new OrdersTableAdapter ();
OrdersDataTable orders = details.GetDataBySessionID(customerID);

Should i move to technology which supports stored procedures or can i
have my cake and eat it and have the same functionality but with the
typed DataSets? Is the stored procedure more efficient for selecting
parent and related child records?

Thanks in advance,

Chris

 

[Miha Markic]

Create appropriate adapter in code, it won't harm you ;-)

 

[PlatinumBay]

morleyc,

You should be able to create a typed DataSet from a stored procedure. This
would give you both the multi-table query, and the strongly-typed dataset.

From http://www.pcreview.co.uk/forums/thread-1209067.php:

"A single call to fill on the dataadapter can fill multiple tables. Of
course you have to return multiple select's from your query.

If you have a stored procedure for example that returns three results:
select * from table1
select * from table2
select * from table3

When you call fill on the dataadapter and pass in a dataset, that dataset by
default will now have three new tables; Table1, Table2 and Table3. If you
don't like those names, then you can use the tablemappings property to
change them."

Hope this helps,


Steve

 

[PlatinumBay]

morleyc,

You should be able to create a typed DataSet from a stored procedure. This
would give you both the multi-table query, and the strongly-typed dataset.

From http://www.pcreview.co.uk/forums/thread-1209067.php:

"A single call to fill on the dataadapter can fill multiple tables. Of
course you have to return multiple select's from your query.

If you have a stored procedure for example that returns three results:
select * from table1
select * from table2
select * from table3

When you call fill on the dataadapter and pass in a dataset, that dataset by
default will now have three new tables; Table1, Table2 and Table3. If you
don't like those names, then you can use the tablemappings property to
change them."

Hope this helps,


Steve