Hierarchical TableAdapters 301
One of my Hitchhiker's Guide to Visual Studio and SQL Server (7th 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.
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:
- 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.
- Open the Data Sources window and create a Data Connection that points to the SQL Server instance that has the target database.
- 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.
- 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
- 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.