Filling a Parent/Child DS - Parent WHERE Clause

[Guest]

I need to fill a DataSet with Parent/Child data. I will use Northwind as an
example. The DS needs to have 2 data tables (Customers and Orders) and a data
relation to link them.

The Select Command of the data adapter for the Customer table has a complex
WHERE clause. I now need to fill the Orders DT for only the subset of
customers that was returned.

Is there any way to leverage ADO.NET to avoid needing to do something like
adding ' SELECT * FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM
Customers WHERE {Same where clause as parent DA here}' to the child data
adapter?

I don't want to fill the child order table with all the rows and then filter
it in code as this obviously would not scale well.

Ideally I would define the parent DA (with the where clause), then define
the child table with no where clause, define a data relation, and then call
the fill method on the whole thing, but this doesn't work, because you have
to fill the tables before you can define the relationship. (See the sample
code below.)

Is there a good way to deal with this problem?

Any help is greatly appreciated.

-DAN

CODE
------------------
//— Create the connection
string sCn = "Data Source=(local);Initial Catalog=northwind;User
ID=sa;Password=pword";
SqlConnection oCn = new SqlConnection (sCn);
DataSet oDs = new DataSet();

//— Fill the customer DataTable
string sSqlCustomer = "SELECT CustomerID, CompanyName, ContactName FROM
Customers WHERE CustomerID = 'ALFKI'";
SqlDataAdapter oDaCustomer = new SqlDataAdapter(sSqlCustomer, oCn);

//— Fill the order DataTable
string sSqlOrder = "SELECT CustomerID, OrderID, OrderDate FROM Orders";
SqlDataAdapter oDaOrder = new SqlDataAdapter(sSqlOrder, oCn);

//— Create the DataRelation and relate the customers to their orders
DataRelation oDr_Customer2Order = new DataRelation("Customer2Order",
oDs.Tables["Customer"].Columns["CustomerID"],oDs.Tables["Order"].Columns["CustomerID"]);
oDs.Relations.Add(oDr_Customer2Order);

oDaCustomer.Fill(oDs, "Customer");
oDaOrder.Fill(oDs, "Order");
------------------

 

[Mohamed Sharaf]

Hi Dan,
you didn't mention what is your presentation layer. Is it windows or
web application. Let's assume that you are using a windows applicaiton. did
you try to show customers names in a comboBox and when the user change the
selected item, you go and manipulate the child Dataset (orders) and bind it
to a datagrid. You can use the same approach in web forms as well.

Mohamed Sharaf

--------------------
| Thread-Topic: Filling a Parent/Child DS - Parent WHERE Clause
| thread-index: AcW2UFl0HUD/g4PVTQ2MrVQik8aQOg==
| X-WBNR-Posting-Host: 67.177.10.35
| From: =?Utf-8?B?RGFu?= <[email protected]>
| Subject: Filling a Parent/Child DS - Parent WHERE Clause
| Date: Sat, 10 Sep 2005 14:41:06 -0700
| Lines: 53
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.dotnet.framework.adonet:35486
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| I need to fill a DataSet with Parent/Child data. I will use Northwind as
an
| example. The DS needs to have 2 data tables (Customers and Orders) and a
data
| relation to link them.
|
| The Select Command of the data adapter for the Customer table has a
complex
| WHERE clause. I now need to fill the Orders DT for only the subset of
| customers that was returned.
|
| Is there any way to leverage ADO.NET to avoid needing to do something
like
| adding ' SELECT * FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM
| Customers WHERE {Same where clause as parent DA here}' to the child data
| adapter?
|
| I don't want to fill the child order table with all the rows and then
filter
| it in code as this obviously would not scale well.
|
| Ideally I would define the parent DA (with the where clause), then define
| the child table with no where clause, define a data relation, and then
call
| the fill method on the whole thing, but this doesn't work, because you
have
| to fill the tables before you can define the relationship. (See the
sample
| code below.)
|
| Is there a good way to deal with this problem?
|
| Any help is greatly appreciated.
|
| -DAN
|
| CODE
| ------------------
| //— Create the connection
| string sCn = "Data Source=(local);Initial Catalog=northwind;User
| ID=sa;Password=pword";
| SqlConnection oCn = new SqlConnection (sCn);
| DataSet oDs = new DataSet();
|
| //— Fill the customer DataTable
| string sSqlCustomer = "SELECT CustomerID, CompanyName, ContactName FROM
| Customers WHERE CustomerID = 'ALFKI'";
| SqlDataAdapter oDaCustomer = new SqlDataAdapter(sSqlCustomer, oCn);
|
| //— Fill the order DataTable
| string sSqlOrder = "SELECT CustomerID, OrderID, OrderDate FROM Orders";
| SqlDataAdapter oDaOrder = new SqlDataAdapter(sSqlOrder, oCn);
|
| //— Create the DataRelation and relate the customers to their orders
| DataRelation oDr_Customer2Order = new DataRelation("Customer2Order",
|
oDs.Tables["Customer"].Columns["CustomerID"],oDs.Tables["Order"].Columns["Cu
stomerID"]);
| oDs.Relations.Add(oDr_Customer2Order);
|
| oDaCustomer.Fill(oDs, "Customer");
| oDaOrder.Fill(oDs, "Order");
| ------------------
|