Hierarchical TableAdapters 301

One of my Hitchhiker’s Guide to Visual Studio and SQL Server (7^th Edition) readers commented that he was still having problems setting up a TableAdapter that was fed from stored procedures instead of base tables. With help from Steve Lasker and Beth Massi at Microsoft, I was able to get a fairly comprehensive application together that demonstrates most of the issues one encounters when attempting to build a forms-over-data application that uses stored procedures instead of tables as the rowset source. Although I don’t have enough space here to walk through this process step-by-step, I’ll give you a roadmap that should help you avoid the pitfalls.

As I see it, a fundamental problem with the drag-and-drop Data Source/TableAdapter code generators is that they assume you’re going to work with an entire base table—even for parent/child hierarchical schemas. Except for the choir roster, this is rarely the case when you expect the application to scale. In most cases, the DBA won’t let you have direct access to the base tables anyway, so you’ll need to use stored procedures to fetch and change the target tables—all of them in the hierarchy. Sure, it’s entirely possible to handle the task using stored procedures with the Data Source and TableAdapter, but you’ll have to do a lot more of the work manually.

When lecturing at SQL Server conferences, I often ask the attendees if they use stored procedures to access their data. Virtually all of the hands go up—at which time I ask the other folks to look around and reconsider their choice of data access paradigm. Yes, some are Access/JET developers and others are just getting started, so it’s understandable that they might not understand the benefits of stored procedures. Consider it’s not that stored procedures run faster than ad-hoc queries, it’s just that they are (hopefully) written to return focused rowsets instead of the entire table being accessed.

Getting Started

The example application I describe here is used to provide a parent/child/grandchild view of a Customer/Order/Item database. The bulk of the UI is generated via drag-and-drop with judicious tinkering here and there to deal with a few issues such as displaying columns the user need not see (like the Timestamp).

Here are the steps I used to create the example application:

1. Start Visual Studio 2005 and begin a new Windows Forms project. I’m using Team System, but you can use Visual Studio Standard or better. Nope, the Express Edition is missing some key features, so I don’t recommend it.

2. Open the Data Sources window and create a Data Connection that points to the SQL Server instance that has the target database.

3. The next dialog (as shown in Figure 1) permits you to choose the stored procedures used to fetch the parent and child rowsets for the TableAdapter objects you’ll be creating. These stored procedures are written to return focused subsets of the database. In the case of the Customer’s table, you select customers based on a user-supplied state code. The orders and items SELECT stored procedures fetch just orders associated with the selected customer and order. As you choose the database objects, don’t click-select the “root” elements such as “Tables”, “Views”, or “Stored Procedures”—this generates TableAdapter objects for all of the items in the list—not good.

   

   Figure 1: Data Source Configuration Wizard—Choosing the row source.

4. If you want to view these procedures, use the Visual Studio Server Explorer or SQL Server Management Studio to open the stored procedure definitions. No, you won’t be able to use the server explore until the wizard is done. The first of these stored procedures is designed to return customers from a selected state as shown in Figure 2.

   

   Figure 2: GetCustomersByState stored procedure

5. Click “Finish” on the Data Source Configuration Wizard. Nope, you’re far from finished, but that’s as far as the DSCW will take you. This builds a strongly typed TableAdapter for each of the rowsets returned by the stored procedures and adds a “CustomerDataSet.xsd” file to the project. The Data Sources window also exposes these new TableDef objects. Don’t do any dragging now—it’s too soon. That’s because you have not set up the relationships between the rowsets returned by the stored procedures. If you had created TableAdapter objects from the base tables, Visual Studio would have derived the DataRelation objects automatically. You’ll need to do that manually because you’re using stored procedures.

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.

1. 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.

   

   Figure 3: The TableAdapter Configuration Wizard step 1.

2. 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.
3. 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:

1. 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.



Figure 4: The Relation editor dialog.

2. 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.
3. 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.
4. 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 5: Setting the Relation dialog for the Orders and Items table.

Building the User Interface and Binding to the Rowsets

The next challenge is to take your new Hierarchical TableAdapter objects and get Visual Studio to generate the appropriate UI elements and data binding controls for them. The following steps build these controls using drag-and-drop techniques.

1. Return to the Solution Explorer and choose the Form1 designer. Increase the size of the Form to accommodate several large elements. I won’t lead you through the process of refining the Form because I’m sure you know how to do that by now.
2. Open the Data Sources window and note there is an important difference—the TableAdapter objects (as exposed as DataTables in the CustomerDataSet) are now shown in a hierarchy as defined by the DataRelation objects that were just created. Figure 6 shows this hierarchy.



Figure 6: The TableAdapter objects shown in a hierarchy.

3. If you don’t see the child DataTables in the hierarchy as shown, you’ve done something wrong. Just remember, when you drag from the Data Sources window, Visual Studio won’t be able to set up the correct data bindings unless you drag from the hierarchical diagram.

   Because you want the Customers part of the UI to be displayed as individual controls, you need to make some adjustments to the TableAdapter as exposed by the Data Sources window—before you drag it to the Form.

4. Click the parent DataTable “GetCustomersByState”. If the Form designer is topped, clicking any of these DataTable columns exposes a drop-down list that permits you to choose how you want the column exposed on the form (well, within limits). The default behavior is to lay the columns out in a DataGridView control. To use individual controls, choose “Details” from the dropdown list.
5. Because you want to expose the Photo column, choose “PictureBox” from the dropdown menu for this column. This should change the icon next to the Photo column to match this selection.
6. You’re ready to drag the GetCustomersByState DataTable to the Form. Click, drag, and drop the DataTable to the upper left corner of the form—but leave a bit of room for the BindingNavigator and FillToolStrip that are generated. Note that this generates five new controls and classes and adds them to the Form (as shown in Figure 7). Up to this point, Visual Studio has not added any of the TableAdapter classes to the Form—just to the project. These added elements include:
• The CustomerDataSet, which is a strongly typed DataSet that contains DataTable objects that instantiate instances of the TableAdapter classes generated by Visual Studio from the select queries.
• The GetCustomersByStateTableAdapter, which is drawn from the Data Sources TableAdapter.
• The GetCustomersByStateBindingSource, which maps the Data Source and the GetCustomersByState TableAdapter to the bound controls.
• The GetCustomersbyStateBindingNavigator, which exposes a UI element that permits the user to scroll through the rowset returned by the select query. Included in this toolbar are additional controls used to add, delete and update rows in the bound rowsets.
• The FillToolStrip control, which provides UI elements to capture the input parameters and a button used to invoke the Fill method (which executes the select query).



Figure 7: The Form UI is populated by the drag-and-drop operation.

7. Okay, now you’re ready to add the first child DataTable to the form. In this case, you want to expose the child data table in a DataGridView control so you don’t have to change anything on the DataTable dropdown list.
8. Now, here’s the tricky part—drag the GetOrdersByCustomer child DataTable from the Data Sources window. But, make sure that you drag the DataTable that’s shown in the top hierarchy—not the one from the bottom of the list of DataTable objects in the CustomerDataSet. As before, another group of controls and classes are added to the form including another BindingSource, TableAdapter, BindingNavigator, and FillToolStrip. The new BindingNavigator is wedged in at the top of the form.
9. Repeat the drag-and-drop process for the GetItemsByCustomerOrder DataTable—again, be sure to drag from the hierarchical DataTable directly beneath its parent—the GetOrdersByCustomer DataTable.
10. Because this example’s UI does not need the Orders or Items FillToolStrip controls, delete them from the Form.
11. Next, you need to make some changes to the DataGridView controls because they don’t observe the “do not show” settings (for some reason). Right-click the Orders DataGridView control and click “Edit columns”. Select the TimeStamp column in the left-hand list and set the Visible property to “False” in the right-hand dialog. Repeat this for the Items data grid as well.
12. You need to make another subtle but very important change to the generated controls to help deal with the PK/FK relationships and the default behavior of the data binding mechanism. Select the GetOrdersByCustomerTableAdapter and GetItemsByCustomerOrderTableAdapter and use the Properties dialog to set the ClearBeforeFill property to False. You leave the GetCustomersByStateTableAdapter object’s ClearBeforeFill set to True because you want it emptied of all previous rows whenever the user clicks the Fill button in the FillToolStrip.

Your Form should now look something like Figure 8. Sure, I tuned it up somewhat to make it a bit easier to read.

Figure 8: The Form UI after the drag-and-drop operations.