Werner,
Good question. Let's start with a query to retrieve customer
information:
SELECT * FROM Customers WHERE Country = 'Canada'
Now, how do you get just the orders for these customers? The simplest
solution is to use a query that looks like:
SELECT O.* FROM Orders O, Customers C
WHERE O.CustomerID = C.CustomerID
AND C.Country = 'Canada'
You could also use a JOIN query or a query with an IN clause and a
sub-select. You can use keep building on this approach for "grandchild"
tables, like the Order Details table:
SELECT OD.* FROM [Order Details] OD, Orders O, Customers C
WHERE OD.OrderID = O.OrderID
AND O.CustomerID = C.CustomerID
AND C.Country = 'Canada'
If you're working with a provider and database (like SQL Server) that
support batch queries, you can batch these queries together and fetch all
of the rows in a single call to DataAdapter.Fill. I've included some
sample C# & SqlClient code you could try.
DataSets are designed to hold both current and original values so you
can submit pending changes using optimistic concurrency to ensure you don't
accidentally overwrite another user's changes.
I hope this information proves helpful.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.
string strConn, strSQL;
strConn = "Data Source=(local);Initial Catalog=Northwind;" +
"Trusted_Connection=Yes;";
strSQL = "SELECT C.* FROM Customers C WHERE C.Country = @Country;" +
"SELECT O.* FROM Orders O, Customers C " +
"WHERE O.CustomerID = C.CustomerID AND C.Country = @Country;" +
"SELECT OD.* FROM [Order Details] OD, Orders O, Customers C " +
"WHERE OD.OrderID = O.OrderID AND O.CustomerID = C.CustomerID " +
"AND C.Country = @Country";
SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn);
da.SelectCommand.Parameters.Add("@Country", SqlDbType.NChar, 15);
da.SelectCommand.Parameters[0].Value = "Canada";
//Supply DataTable names for the results
da.TableMappings.Add("Table", "Customers");
da.TableMappings.Add("Table1", "Orders");
da.TableMappings.Add("Table2", "Order Details");
//Fill the DataSet
DataSet ds = new DataSet();
da.Fill(ds);
//Add relations
ds.Relations.Add("Customers_Orders",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"]);
ds.Relations.Add("Orders_Details",
ds.Tables["Orders"].Columns["OrderID"],
ds.Tables["Order Details"].Columns["OrderID"]);
//Print out the results
foreach (DataRow rowCustomer in ds.Tables["Customers"].Rows)
{
Console.WriteLine("{0} {1}", rowCustomer["CustomerID"],
rowCustomer["CompanyName"]);
foreach (DataRow rowOrder in rowCustomer.GetChildRows("Customers_Orders"))
{
Console.WriteLine(" {0} {1} {2}", rowOrder["CustomerID"],
rowOrder["OrderID"], rowOrder["OrderDate"]);
foreach (DataRow rowDetail in rowOrder.GetChildRows("Orders_Details"))
Console.WriteLine(" {0} {1} {2}", rowDetail["OrderID"],
rowDetail["ProductID"], rowDetail["Quantity"]);
Console.WriteLine();
}
Console.WriteLine();
}
