Hierarchical TableAdapters 301
Reconfiguring the TableAdapter Objects Using the Designer
Because you selected three stored procedures that return selected Customer, Order, and Items rowsets, you need to reconfigure the TableAdapter objects to deal with these rowsets correctly. The following steps walk through the process that must be repeated for each of the three TableAdapter objects generated by the Data Sources wizard.
- From the Solution Explorer, double-click the CustomerDataSet.xsd file. This opens the TableAdapter Designer. Starting with the GetCustomersByState TableAdapter (which returns the root parent Customers rowset), right-click the top of the window and choose "Configure". This opens the TableAdapter Configuration Wizard (TACW) as shown in Figure 3. Here, you need to point to the correct Insert, Update, and Delete stored procedures that are called by the TableAdapter Update method.
- Click "Next" and, because the code does not use the DataTable option, it's okay to disable it. Click Next to continue; this constructs the code needed to link your TableAdapter to the appropriate stored procedures. Click "Finish" to commit the operations.
- Repeat these steps (2-4) for the two child rowsets—Orders and Items.
Defining Client-Side Inter-Rowset Relationships
Because SQL Server cannot define relationships between independent rowsets (and neither can Visual Studio), it will be up to you and Visual Studio to define the DataRelation objects between the three rowsets generated by the TableAdapter objects. Once implemented, these relationships can establish PK/FK constraints that prevent changes to the database that would break referential integrity rules. That is, the constraints prevent parents being deleted when they still have children and children from being added where there is no related parent. These (client-side) constraints also can prevent duplicate rows (based on the primary key columns) from being added to the database.
These steps walk you through the process of creating these client-side DataSet DataRelation objects:
- Starting with the parent rowset (GetCustomersByState), click the primary key column (CustID). Once you've selected (just) the PK column(s), drag to the left, hesitate to let Visual Studio generate the pointer and drag the pointer to the child table's TableAdapter (GetOrdersByCustomer) and drop. This opens the Relation dialog (as shown in Figure 4) that links the two TableAdapter objects by PK/FK columns.
- Set each Foreign Key Column to match a column in the Key Columns list (which should contain all PK columns for the Parent TableAdapter). In my designs, these column names are the same from parent to child but they don't have to be and sometimes aren't when you have a sadistic DBA.
- Set the "Choose what to create" options as shown—"Both Relation and Foreign Key Constraint" as well as the rules. All need to be set to "Cascade". This makes sure that, if a parent row is deleted, the child row(s) also are deleted. In this case, it means if a customer is deleted, all orders are also deleted—that can be bad if you aren't careful.
- Next, you need to repeat the process for the next parent/child relationship—in this case, the Orders TableAdapter is the parent and the Items table is the child. Either try to get the drag-and-drop linkage to work or simply right-click the top border of the parent TableAdapter windows and choose "Add | Relation". Be very careful—the TableAdapter objects are listed in alphabetical order, so it's easy to choose the wrong TableAdapter as the parent or child. In this case, because there are two parts to the PK in the parent rowset (Orders), two Key Columns are paired with two Foreign Key Columns in the child table. Again, make sure you set the rules to cascade changes.
Figure 4: The Relation editor dialog.
Figure 5: Setting the Relation dialog for the Orders and Items table.
Page 2 of 5