Build a TableAdapter application fed by stored procedures
A developer recently asked me how to set up a TableAdapter application that’s fed by stored procedures instead of base tables. Stored procedures optimize application, database, and development team performance as well as simplify maintenance. However, using them as the rowset source for forms-over-data applications is thinly documented and requires some manual setup and custom coding— especially when you’re working with stored proceduregenerated rowsets that have an implicit hierarchy, such as those drawn from tables with parent–child relationships. With guidance from Microsoft’s Steve Lasker and Beth Massi, I created a comprehensive demonstration application that illustrates most of the problems you can encounter when trying to build such an application. In this article, I’ll use this sample application to explain how to create the solution as well as the behind-the-scenes mechanisms that make it work.
I used Visual Studio (VS) 2005 to build this application because it automatically generates most of the plumbing code, which you shouldn’t have to modify or debug. In addition, VS also generates the binding components and UI controls for you. Although code generators can generate controls and navigation code you might not need, cleaning up these extra UI elements is fairly safe and easy. Just remember to back up your work frequently when working with VS. I’ve had to start from scratch several times after my changes caused unrecoverable application errors.
What’s the Big Deal?
Simply put, a TableAdapter is a VS-generated class that exposes a rowset as a strongly typed DataTable that you can update and handle the same as you would a DataAdapter. You’ve probably seen forms-over-data applications demonstrated many times using base tables. But the typical base-table approach to creating production applications can have a dramatic impact on server performance and application scalability. If you simply create a new VS data source and click a table in the list, VS builds a SELECT * FROM MyTable query that unconditionally returns all the rows in the table— even though your user might need only a few rows. Although pulling all 50,000 (or five million) rows from the server into client memory is possible, users might not like the wait time.
Any application-development strategy you choose should focus the initial query on a subset of the rows. For example, instead of fetching all the customers in the database, get only customers who meet the usersupplied criteria. In fact, the user might not want any rows at all. I recommend performing queries only after users provide a few parameter values describing what they’re looking for. In the case of the sample application, which contains only a few thousand customers, I asked the user to specify a state and a last name to narrow down the initial rowset to a manageable number.
Although you can use custom-written Fill queries to refocus the data, they make the applications harder to maintain if the query logic is too sophisticated. If the logic changes, you have to recompile, retest, and redeploy your applications.
Stored procedures let you set up and save database queries that optimize your application performance and allow for easy maintenance. One note about building an application using the approach I describe: Although you can create the TableAdapter from a rowset derived from a join, this method can add a lot of complexity. If you’re simply adding columns to each row, you can write custom stored procedures to update the affected tables. Just make sure that ADO.NET’s Update method, which is called behind the scenes when the TableAdapter Update method is invoked, returns a RowsAffected value of 1. You might even have to implement your own TableAdapter partial class, which is beyond the scope of this article.
If the join returns more than one row for a single primary key—such as when you join Customers with Addresses into a single rowset and a customer has several addresses—it can confuse the client-side databinding mechanism. You might find it easier to keep your queries simple and use other mechanisms to achieve the same result, such as performing a clientside join of the related data columns, which is what I do with the Addresses DataTable in the Web-exclusive sidebar “Additional Features,” InstantDoc ID 97180.
Before I describe how to build the sample application, let’s lay down some ground rules. First, you can use this approach with both Active Server Pages (ASP) and Windows Forms applications (with a few exceptions that I mention along the way). My example is a Windows Forms application written in Visual Basic (VB) and designed to access SQL Server. The application includes little hand-written code, and some of my custom code was even leveraged from VS-generated code that I relocated or simply tuned. However, C# programmers will need to convert the VB code.
In addition, I’ve set up the database tables in this example with server-side primary key–foreign key relationships configured to prevent duplicates and ensure that parent–child relationships are correctly maintained. It’s important to establish these referential integrity constraints to make sure the application code doesn’t destroy the data.
Let’s get started with the preparation steps required to create the sample application. First, start VS 2005 and begin a new Windows Forms project. I used VS Team System, but you can use VS Standard Edition or later. However, VS Express Edition is missing some key features that I leveraged, so I don’t recommend using it for serious development work. In some VS configurations, the project isn’t actually saved until you click Save All, so save your project immediately to make sure the directory is created and the files you save along the way are correctly persisted.
Next, open the Data Sources window, which you can launch by clicking Data, Show Data Sources. Click Add New Data Source, and select Database from the Choose a Data Source Type menu. Select or create a Data Connection that points to the SQL Server instance that has the target database. In this case, I pointed to the server hosting the Customers database created for the sample application. (This database and the complete VS project are available online at www.sqlmag.com, InstantDoc ID 97182.) Save the Connection String to the app.config file so that the Table- Adapter Configuration Wizard can leverage it when it needs to build ADO.NET connection objects behind the scenes.
The Choose Your Database Objects dialog box, which Web Figure 1 (www.sqlmag.com, InstantDoc ID 97182) shows, lets you select the stored procedures that you want to use to return the rowsets for the TableAdapter objects you’ll create. In this case, you’d select the GetCustomerbyState, GetItemsbyCustomer- Order, and GetOrdersbyCustomer stored procedures. Whatever you do, never select root elements such as Tables, Views, or Stored Procedures; doing so generates TableAdapter objects for all the items in the list, which isn’t good.
You want to access the data through parameterdriven stored procedures written to return a focused rowset. To view these stored procedures, use VS’s Server Explorer or SQL Server Management Studio (SSMS) to open the stored procedure definitions. Note that you can’t use Server Explorer until the Data Source Configuration Wizard is finished. Web Listing 1 shows the first stored procedure, which returns customers from a selected state.
Continue to page 2
Now, click Finish in the Data Source Configuration Wizard. Although you’re far from finished, that’s as far as the wizard will take you. However, this process builds a strongly typed TableAdapter for each rowset that the stored procedures return and adds the CustomerDataSet. xsd file to the project. The Data Sources window also exposes the new TableDef objects.
The behavior of the Data Sources window changes depending on the currently exposed editing window. For example, if you set focus to the Form UI design window Form1.vb (Design), you can choose how individual TableAdapter objects in the Data Sources window are used when generating UI controls. But don’t start dragging any objects yet because you haven’t manually set up relationships between the rowsets returned by the stored procedures. Note that if you had created TableAdapter objects from the base tables, VS would have derived the DataRelation objects automatically.
Also note that the TableAdapter Fill method is automatically configured to capture any input parameters you’ve defined. In this case, the Fill method will need values supplied for the @StateWanted and @ NameHint parameters.
Reconfiguring TableAdapter Objects
Because you chose three stored procedures that return selected Customer, Order, and Items rowsets, you need to reconfigure the TableAdapter objects to handle these rowsets correctly. The following steps walk you through the process of doing so, which you’ll repeat for each of the three TableAdapter objects that the Data Source Configuration Wizard generated.
First, double-click the CustomerDataSet.xsd file in VS’s Solution Explorer to open the TableAdapter Designer. Starting with the GetCustomersByState TableAdapter, which returns the root parent Customers rowset, right-click the top of the window and select Configure. Doing so opens the TableAdapter Configuration Wizard, which Figure 1 shows. In this wizard, you can point to the correct Insert, Update, and Delete stored procedures that the TableAdapter Update method calls. Be sure to choose the correct stored procedures—mixing them up can have bad consequences. I used VS to generate the initial version of these procedures. Once VS (or you) create the stored procedures, you can define the appropriate input parameters, output rowsets, and RETURN values. From there, you can add custom logic as needed.
Note how the initial rowset columns from the Get- CustomersByState stored procedure are mapped to the Insert command’s source columns. If the columns that the stored procedures return don’t match all the required columns exposed by the SELECT query, a warning message will be displayed.
Now, click Next. Because the code doesn’t use the DataTable option, it’s okay to disable it. Click Next again to construct the code needed to link your Table- Adapter to the appropriate stored procedures, and then click Finish to commit the operations. Repeat these steps for the two child rowsets, Orders and Items.
Defining Client-Side Inter-Rowset Relationships
Because SQL Server doesn’t define relationships between independent rowsets, it’s up to you and VS to define the DataRelation objects between the three rowsets generated by your TableAdapter objects. This process is fairly easy once you figure out how to select the correct primary key–foreign key index values in the UI. (As I noted earlier, these relationships are generated automatically when you build data sources based on base tables that have established server-side relationships.)
When implemented, these relationships establish primary key–foreign key constraints that prevent changes to the database that would break referential integrity rules. That is, the relationships prevent parents from being deleted when they still have children and children from being added where no related parents exist. These constraints can also prevent duplicate rows (based on the primary key columns) from being added to the database.
Most DBAs set up these constraints whenever they create a relational database. You can do so by using VS’s database diagramming tool, which you can find in Server Explorer’s Database Diagrams folder. This tool works much like the TableAdapter Configuration Wizard to let you specify not only indexes, but also primary key–foreign key relationships.
Now, let’s look at how you create these client-side DataSet DataRelation objects. Starting with the parent rowset (GetCustomersByState), click the primary key column (CustID) and drag it to the left. If you drag down, you’ll select additional columns, which is what you want to do for the GetOrdersByCustomer Table- Adapter because there are two columns that define the primary key. After you’ve selected only the primary key columns, drag them to the left, hesitate to let VS generate the pointer, and then drag the pointer to the child table’s TableAdapter (i.e., GetOrdersByCustomer) and drop. Doing so opens the Relation dialog box, which links the two TableAdapter objects by primary key and foreign key columns, as Figure 2 shows.
In the Relation dialog box, set each foreign key column to match a column in the Key Columns list, which should contain all primary key columns for the parent Table- Adapter. In my design, these column names are the same from parent to child, but they don’t have to be. Also set the Choose what to create option to Both Relation and Foreign Key Constraint and set all rules to Cascade to ensure that if a parent row is deleted, the child row(s) are also deleted.
You need to repeat this process for the next parent–child relationship. You can either drag and drop or simply right-click the top border of the parent TableAdapter window and select 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 primary key 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.
Continue to page 3
Building the UI and Binding to the Rowsets
The next challenge is to have VS generate the appropriate UI elements and data-binding controls for your new hierarchical TableAdapter objects. The following steps let you build these controls using drag-and-drop techniques.
First, return to VS’s Solution Explorer, and select 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.) Open the Data Sources window, and note that the TableAdapter objects (exposed as DataTables in the Customer- DataSet) are now shown in a hierarchy as specified by the DataRelation objects that you defined.
If you don’t see the child DataTables in the hierarchy as shown in Web Figure 2, you’ve done something wrong. Just remember, VS can’t set up the correct data bindings unless you drag objects from the hierarchical diagram in the Data Sources window. But you’re not ready to do any dragging just yet. Because you want the Customers part of the UI to display 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.
First, click the parent DataTable GetCustomersBy- State. If the Form Designer is the top window, clicking any of these DataTable columns exposes a drop-down list that lets you select how you want the column to be exposed on the form (within limits). The default behavior is to lay out the columns in a DataGridView control. To use individual controls, select Details from the drop-down list.
Let’s say you also want to hide the TimeStamp column from the user. Because you selected the Details option, you can do so from the Data Sources window. Just click the TimeStamp column and select None from the list if it isn’t the default.
In this example, you also want to expose the Photo column. Select PictureBox from the drop-down menu for this column, which will change the icon next to the Photo column to match your selection. You can also create a custom UI control and select it from this dialog box.
You’re now ready to drag the GetCustomersBy- State DataTable to the form. Click, drag, and drop the DataTable to the upper left corner of the form, but leave some room for the BindingNavigator and FillToolStrip that will be generated. So far, VS hasn’t added any of the TableAdapter classes to the form, only to the project. However, dragging the DataTable to the form generates five new controls and classes and adds them to the form, as Web Figure 3 shows. These added elements include
- The CustomerDataSet, which is a strongly typed DataSet containing DataTable objects that instantiate instances of the TableAdapter classes that VS generates from the Select queries
- The GetCustomersByStateTableAdapter, which is drawn from the DataSources TableAdapter
- The GetCustomersByStateBindingSource, which maps the DataSource and the GetCustomersBy- StateTableAdapter to the bound controls
- The GetCustomersbyStateBindingNavigator, which exposes a UI element that lets users scroll through the rowset returned by the Select query; included in this toolbar are additional controls that can be 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 to invoke the Fill method, which executes the Select query
Now, you can add the first child DataTable to the form. In this case, you want to expose the child DataTable in a DataGridView control, so you don’t have to change anything on the DataTable drop-down list.
Now comes the tricky part—dragging the GetOrdersByCustomer child DataTable from the Data Sources window. Just make sure 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. Dragging the child DataTable to the form adds another group of controls and classes to the form, including another BindingSource, TableAdapter, BindingNavigator, and FillToolStrip.
Repeat the drag-and-drop process for the GetItems- ByCustomerOrder DataTable—again, be sure to drag from the hierarchical DataTable directly beneath its parent, which is the GetOrdersByCustomer DataTable. Because this example doesn’t need the Orders or Items FillToolStrip controls, delete them from the form.
Now, you need to make some changes to the DataGridView controls because they don’t observe the “do not show” settings. If you don’t change the DataGridView controls, the TimeStamp columns will appear in the UI and cause problems as the grid code tries to render the binary value that the user shouldn’t see in the first place. Right-click the Orders DataGrid- View control, and click Edit columns. Select the Time- Stamp column in the left-hand list, and set the Visible property to False in the right-hand dialog box. Repeat this process for the Items data grid. Note that you can use this dialog box to reorder columns and set custom formatting and special behaviors.
You need to make another subtle but important change to the generated controls to help handle the primary key–foreign key relationships and the default behavior of the data-binding mechanism. Select the GetOrders- ByCustomerTableAdapter and GetItemsByCustomer- OrderTableAdapter, and use the Properties dialog box to set the ClearBeforeFill property to False. However, leave the GetCustomersBy- StateTableAdapter object’s ClearBeforeFill property set to True because you want it emptied of all previous rows whenever the user clicks the Fill button in the FillToolStrip.
Tuning and Augmenting the Code
Although VS generates code to support some of the drag-and-drop operations, there are missing links and blocks of code that you need to add to get the project to work as expected. Part of the problem is because the example uses used stored procedures and deletes two of the FillToolStrip controls, which provide another way to navigate between rows in the parent DataTable and related children DataTables. I deleted the controls so that the application pulls only the appropriate children for a selected parent row. Let’s walk through the application at a functional basis to see where you need to fill in the blanks.
Implementing Form_Load. Microsoft’s developers and I don’t always agree on connection strategies, but in this case, I think I can fully justify my approach. I open the Connection objects that the TableAdapter objects use and leave them open for the life of the application, as Web Listing 2 shows. By doing so, you don’t have to wait for an available connection when you want to scroll to another customer, execute the Fill methods, or post an update to the database. The default behavior has the Fill and Update methods open the Connection objects just before the query (or Data Manipulation Language—DML—operation) is executed. With this approach, the Connection pool must locate a suitable matching connection, and the SQL Server must reset and reconfigure the connection each time it’s referenced. Not only does this process take time, but it makes debugging the operations by using SQL Server Profiler harder because the trace stream is clogged with detritus that’s not needed for Windows Forms applications—especially those that don’t scale beyond a few hundred users.
Implementing the Fill button. The FillToolStrip control captures the two input parameters (i.e., State Wanted, Name Hint) and exposes a Fill button that the user can click to populate the list of customers. As implemented, the FillToolStripButton_Click event executes the Get- CustomersByStateTableAdapter.Fill method, passing in parameters from the FillToolStrip TextBox controls. However, this operation doesn’t automatically populate the child rowsets, so you’ll also need to call the Orders and Items Fill methods. Each method is a focused query that returns orders for only a specific customer and items from only a specific order. You also need to add an exception handler for when there are no customers within the given range. To ensure that the user doesn’t try to add rows before the initial rowset population, I disabled the BindingNavigator and reenabled it when the query returned at least one row. Web Listing 3 shows the code for this routine.
Implementing the DataError event. If you aren’t careful, you’ll include columns in your DataGridView that it can’t handle, including an unrecognized binary value such as a TimeStamp. If you don’t hide this column or if your code (or user) generates a value the DataGridView can’t handle, you’ll need to implement the DataError event. This code can do anything you want, including cancel the problem-causing operation. Web Listing 4 shows my routine, which handles the DataError events for both DataGridView controls, dumping exceptions to the Debug window so that I can review them later.
Populating rowsets for the child DataTables. When a user selects a certain customer to view and the list of customers is initially populated, you need to populate the lists of applicable child orders and items. Two event handlers, which are triggered by the BindingSource PositionChanged event, manage this process. These events fire when the user selects another customer or another order for a specific customer. Each of the event handler routines call the appropriate TableAdapter Fill method, passing the current customer ID and order ID to focus the query on rows related only to that customer, as Web Listing 5 shows.
Saving data to the database. You also need to implement the SaveItem_Click event, as exposed by the BindingNavigator ToolStrip control, for when a user (or your code) wants to save changes. Part of this code is implemented for you by the drag-and-drop operations, but these operations deal with only the top-level parent control. Saving the data to the database is done in two phases. The first phase validates and commits any changes in the bound controls to the underlying DataTable row through the Validate and EndEdit methods, which Web Listing 6 shows.
The second phase of the update operation steps through the parent/child/grandchild hierarchy (e.g., Customer/Order/Item) and posts any changes to the database. These operations must occur in the correct order to satisfy the primary key–foreign key constraints that you defined in the TableAdapter Designer. 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. You implement the constraints by coding DataRelation objects that define how the relationships are enforced. Don’t remember coding any of these objects? You didn’t—the TableAdapter Configuration Wizard did it for you when you mapped the primary key and foreign key relationships. Just dig into the CustomerDataSet.Designer.vb file to see the definitions.
Setting server-side constraints. Of course, you should also implement similar constraints on SQL Server by setting up primary key–foreign key constraints and cascading operations where appropriate. In most databases, the DBA ensures that these constraints are implemented so that you can’t accidentally break the database’s referential integrity. The tools for defining these relationships are included in VS’s Server Explorer (through the database diagramming tool) or in SSMS’s Object Explorer. I defined these constraints when I created the Customer database so that if my VS code makes a mistake, the database isn’t affected.
These constraints can also help make handling hierarchical updates easier because you can program them to cascade the deletes. For example, you can program the constraints so that when you delete the customer Fred, all of his orders and the items associated with those orders are also deleted. However, you should use triggers or other safeguards to monitor this kind of operation and prevent customers from being arbitrarily deleted—especially if they still owe you money.
Using transactions to protect referential integrity. Binding these separate mission-critical operations into a single atomic transaction makes a lot of sense. You can find many articles about using transactions, but I think the simplest approach is to use the System.Transactions namespace, which lets the .NET Framework handle transactions and reduces the amount of code you have to write to implement them. Web Listing 7 shows the code that steps through the hierarchy and posts changes to the database. It also illustrates the use of the System.Transactions.TransactionScope class.
The updates add any new parents (e.g., Customers) first, followed by any new children (e.g., Orders and then Items). Combined with the add operations, you can include the changes to existing parents and children because they don’t affect the hierarchy. However, I don’t recommend changing the primary key—that change can be safely implemented only by deleting the original row and adding a new row with the new primary key.
After the add and change operations are done, you’re ready to execute the delete operations. In each case, you use the ADO.NET Select method to filter for just the rows you need based on the RowState property. When all operations are complete, you let the .NET Framework know that the transaction should be committed by calling the Complete event; otherwise, the transaction is automatically rolled back.
The Road Less Traveled
The sample application can also import pictures, handle new row initialization, and provide a list of addresses through the Addresses DataTable, which the Web-exclusive sidebar “Additional Features” describes. As you can see, you can create an application that can manage hierarchically related rowsets derived from almost any source—even stored procedures. There are a few stumbling blocks along the way because I don’t think Microsoft expected developers to take this route. Although it’s easier to reference the base tables, that approach makes one big assumption: that the DBA will let you. Most DBAs hide and protect the base tables very carefully. You’ll find that this technique is more palatable to DBAs and even lets you change the stored procedure logic as long as you don’t change the signature, which is the pattern of input parameters and output columns being returned.
I’ve also had a chance to look at VS 2008, which includes a TableAdapterManager class that promises to make the development process easier. The new class is designed to replace much of the code that I had to write for the UpdateHierarchy routine with a single method call. Stay tuned for an article on the new dataaccess features exposed in VS 2008 and SQL Server 2008.