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.

Tuning and Augmenting the Generated Code

Now comes the fun part—at least it separates the kids from the grownups when it comes to skill. Although Visual Studio has generated code to support some of the drag-and-drop operations, there are a number of missing links and blocks of code that you’ll need to add to get the project to work as expected. That’s partly due to the use of stored procedures and the fact that you deleted two of the FillToolStrip controls. Walk through the application on a function basis to see where you need to fill in the blanks.

Implementing the Fill Button

The FillToolStrip control captures the two input parameters (StateWanted and Name Hint) and exposes a button that the user can press to populate the list of customers. As implemented, the FillToolStripButton_Click event executes the GetCustomersByStateTableAdapter.Fill method—passing in the parameters from the FillToolStrip TextBox controls. Unfortunately, this does not automatically populate the child rowsets, so it’s necessary to call the orders and items Fill methods as well. Each of these methods are focused queries that only return orders for a specific customer and only items from a particular customer order. You also need to add an exception handler to deal with the case when there are no customers within the given range. To make sure the user does not try to add rows before the initial rowset population, I disabled the BindingNavigator and re-enabled it when the query returned at least one row. The code for this routine is shown in Figure 9.

Figure 9: The FillToolStripButton_Click event handler.

Handling Rowset Population for the Child DataTables

When the user chooses a specific customer to view and when the list of customers is initially populated, you need to populate the lists of applicable child orders and items. This process is handled in two event handlers that trigger off the BindingSource PositionChanged event. These events fire when the user chooses another customer or another order for a specific customer. In each of these routines, I call the appropriate TableAdapter Fill method—passing in the current customer ID and order ID to focus the query on just the rows related to this customer. The code is shown in Figure 10.

Figure 10: Handling the PositionChanged events to populate child rowsets.

Saving Data to the Database

When the user (or your code) decides that it’s time to save the changes made, you’ll need to implement the SaveItem_Click event as exposed by the BindingNavigator ToolStrip control. Yes, part of this code is implemented for you by the drag-and-drop operations, but it does not deal with any but the top-level parent control. Saving the data to the database is done in two phases. The first phase validates and commits any changes made in the bound controls to the underlying DataTable row via the Validate and EndEdit methods. These routines are codes as shown in Figure 11:

Figure 11: Implementing the save phase one: Calling Validate and EndEdit.

Phase two of the update operation steps through the parent/child/grandchild hierarchy (Customer/Order/Item) and posts any changes to the database. As I describe in my book, these operations must take place in the correct order to satisfy the PK/FK constraints as you defined in the TableAdapter Designer. Yes, these are client-side constraints enforced by ADO.NET Framework classes that prevent your code from deleting parents that still have children and adding children with no parents. The constraints are implemented behind the scenes by DataRelation objects that define how the relationships are to be enforced. Don’t remember coding any? Well, you didn’t—the TableAdapter Configuration Wizard did it for you. Dig into the CustomerDataSet.Designer.vb file and you’ll see where these are defined. Are these constraints the same as those already implemented in the database? Perhaps, but only perhaps.

Figure 12: Updating the hierarchical DataSet

Summary

As you can see, it’s entirely possible to create an application that can handle hierarchically related rowsets derived from virtually any source—even stored procedures. There are a few stumbling blocks along the way because Microsoft didn’t expect ordinary developers to take this route. Yes, it’s easier to reference the base tables, but that makes a couple of assumptions: the DBA will permit base table access and you aren’t concerned with scalability. Most DBAs hide and protect the base tables more carefully than the box of chocolates kept hidden in the back of the fourth filing cabinet on the left. You’ll find that this stored procedure approach is more palatable to the DBA and even permits you to change the logic in the stored procedure as long as you don’t change the signature—the pattern of input parameters and output columns being returned. Your performance and scalability will be a lot better as well.

About the Author

William (Bill) Vaughn is an industry-recognized author, mentor, and subject-matter expert on Visual Studio, SQL Server, Reporting Services, and data access interfaces. He’s worked in the computer industry for over thirty-five years—working with mainframe, minicomputer, and personal computer systems as a developer, manager, architect, trainer, marketer, support specialist, writer, and publisher. In 2000, after 14 years at Microsoft, Bill stepped away to work on his books, mentoring, and independent training seminars. He’s written seven editions of the Hitchhiker’s Guide to Visual Basic and SQL Server and three editions of ADO.NET and ADO Examples and Best Practices for Visual Basic (and C#) Programmers. He and Peter Blackburn also wrote the critically acclaimed Hitchhiker’s Guide to SQL Server 2000 Reporting Services. Bill is a top-rated speaker and frequents conferences all over the world including TechEd, Visual Studio/SQL Connections, DevTeach, and many others. He’s also written a wealth of articles for magazines such as MSDN, SQL Server Magazine, Visual Basic Programmer’s Journal, .NET Magazine, and many others as well as a regular editorial for Processor magazine. Bill spends considerable time answering questions on the public newsgroups and speaking at INETA user group meetings all over the country and at other speaking venues all over the world. He’s available for consulting, mentoring, or custom training. See www.betav.com or www.betav.com/blog/billva for his current schedule and course catalog.