Update Alternatives: Using the TableAdapter Configuration Wizard

Okay, so it's tough to use the CommandBuilder (and, thus, the DACW or the TableAdapter) when your SELECT queries get a little complex. What other choices do you have when you need to generate and execute the action commands? It really depends on your overall update strategy.

Updating with the TableAdapter

If you want to use bound controls and let the DataAdapter or TableAdapter Update method handle the changes, consider that the DataAdapter and TableAdapter are table-based, not set-based, paradigms. They're designed to work with a rowset "bound" to a specific (single) table, so they might not really be suitable at all. Sure, it's possible to simply reference a single table with a (fairly) simple SELECT statement and let the TableAdapter configuration wizard fill in the appropriate T-SQL code to update the database. Yes, this code is generated by the CommandBuilder at design time and supplemented by Visual Studio to return changed data from the new and updated rows.

If the Data Source fetch query steps over the line as far as the CommandBuilder is concerned, the TableAdapter drag-and-drop paradigm leaves the "Save" icon disabled as shown in Figure 12.6, and the "Create methods to send updates..." option disabled in the TableAdapter configuration wizard (TACW), as shown in Figure 12.7.

Figure 12.6. The Visual Studio drag-and-drop code generator does not always enable update logic.

Figure 12.7. The TACW won't let you choose to automatically generate update logic in some cases.

In this case, the TACW references a simple parameter-based stored procedure to fetch the initial rowset. The StateWanted and MaxRows input parameters are handled in the UI, but the "Save" icon is grayed out.

If you insist on using the Update command against several related tables, you'll have to write custom code to handle each table separately and figure out how to override the default behavior by trapping the OnRowUpdated, RowUpdating, and/or RowUpdated events and fire your own procedures to perform the changes as described later in this chapter.

Updating Tables with the TableAdapter

Yes, if your SELECT statement is simple, Visual Studio and the TACW can generate all of the action commands. You can even specify how concurrency is managedleveraging the new UpdateOption property. To access this option:

1.	Create a TableAdapter that specifies a simple SELECT in the fetch query. In this example, I simply checked the "Customers" table in the list of available tables, views, and stored procedures. This generates a SELECT query that includes all columns from the table without a WHERE clause. Of course, this also assumes that you plan to choose a mechanism to limit the number of input rows. I'll revisit that issue in a minute.
2.	Open the TableAdapter in the Data Sources window by right-clicking on the DataSet and choosing "Edit DataSet with Designer".
3.	Right-click on the DataSet designer dialog and choose "Configure". This opens a dialog that permits you to alter the SELECT statement generated by the TACW and set the Advanced options, as shown in Figure 12.8. Figure 12.8. Setting advanced concurrency options for a simple SELECT query.
4.	The Advanced Options dialog (shown in Figure 12.9) permits you to change how the TACW generates the action queries and other advanced options. Figure 12.9. The TACW Advanced Options dialog. As you can see, the Advanced Options dialog does not expose all of the UpdateOption property settings. It does automatically sense if there is a TimeStamp column in the fetch query, and if there is, the TACW uses this column to manage concurrency. You can also choose to disable the post-change fetch, which returns the newly added or updated row to keep the local TableAdapter object's rowset in sync with the database row.

Using Stored Procedures for Action Commands

So, how can you specify your own stored procedures to fetch and update the data using the TableAdapter configuration wizard? That's not too hard; just follow along.

1.	Create a TableAdapter and specify the appropriate stored procedure to fetch the rows. Note that the TACW will help manage any input parameters for you when you use drag-and-drop to populate UI elements on the form. This is done by choosing a stored procedure from the list of available objects (Tables, Views, Stored Procedures) when initially setting up the TableAdapter.
2.	Open the Data Sources window in Visual Studio and right-click on the selected Data Source TableAdapterchoose "Configure DataSet with Wizard."
3.	Choose the appropriate stored procedure from the list of visible stored procedures, as shown in Figure 12.10, and click "Finish." Figure 12.10. Select the rowset-fetching stored procedure.
4.	You're now ready to specify the action command stored procedures. Of course, these procedures depend on the columns returned by the initial fetch stored procedure, so you'll need to make sure that the correct columns are returned. Right-click on the TableAdapter again and choose "Edit DataSet with Designer".
5.	In the TableAdapter designer, right-click on the TableAdapter and choose "Configure". This opens a TableAdapter Configuration Wizard dialog, shown in Figure 12.11. Using the drop-down menus, choose appropriate stored procedures for each action command to implement. Click "Next" or "Finish" to commit these changes. Figure 12.11. Choose appropriate stored procedures for each action command.
6.	At this point, the TACW generates the TableAdapter code to configure and execute the selected action commands.

Now, when your application calls the TableAdapter Update method, the appropriate stored procedure is called. This includes populating the stored procedure parameters collection, invoking the stored procedure, performing a post-action query to update client-side values, and posting these changes to any bound controls.

Managing Generated Parameters

So, if you set up the DataAdapter or TableAdapter with the Visual Studio wizards, quite a bit of code is generated for you to handle several housekeeping functions. For example, I talked about how it's important to fetch the newly created identity PK or TimeStamp value post-insert or update operations. ADO.NET is designed to be prepared for these values either in a row (per action command) or via OUTPUT parameters. To get ADO.NET to automatically post these values back to the client-side rows, your code must bind Parameter objects to the source columns. As I said, the wizards automatically generate this code for you, as shown in Figure 12.12. Ah, this code has been modified to fit on the pageit was all on one (long) line before I added the space underscore(_) breaks. Note that the Parameter definition includes pointers to the SourceColumn and SourceVersion. This permits ADO.NET to postback the new TimeStamp (in this case) to the source table after the InsertCommand has executed.

Figure 12.12. TableAdapter configuration wizardgenerated code to post back the new TimeStamp value.

This code is found in the project, buried under the generated DataSet as a .vb file. I suggest you dig into this file to see what the wizard generated for you. There will come a time when you'll want to clone some of this functionality yourselfjust use this generated code as a template. A few notes here:

• Note that the wizards don't try to make this easy to readit's designed to run quickly. Your code could use a With statement to make it far shorter and more succinct.

• The code also spells out each of the object references. Your Imports statement can deal with object addressing in far fewer words.

• The Parameters are all defined as InputOutput because of an (ancient) bug in the interfaces that prevents ADO from determining the real direction for OUTPUT parameters. There is nothing to stop you from setting the correct direction.

IMHO

No, it's not a good idea to change any generated source code.

Configuring Parameters with Visual Studio

No, it's not a good idea to manually change generated source code. If you do, you'll invariably find your changes have been replacedVisual Studio regenerates code at every opportunity and late at night while you're sleeping (or it seems so). Yes, you can safely extend the generated code (partial) class with your own class code, but that's beyond where I want to go in this book. I also expect Microsoft to change how these partial classes are managed in the next version, so I'll hold off writing about it until then.

It is a good idea to use the copious IDE wizards and property pages to modify the attributes used to generate the TableAdapter and DataAdapter code. Consider that the TACW (and DACW) simply gather these attributes from you and the database schema to generate the code. You can choose to start over and rebuild the TableAdapter or DataAdapter from scratch, but you might find it easier to simply tune the attributes using the TableAdapter Properties dialog. In this case, I've drilled into the same TableAdapter whose generated code is shown in Figure 12.13.

Figure 12.13. The TableAdapter Properties dialog.

Getting to this dialog is really pretty easyopen the .xsd DataSet in "designer" mode, right-click on the TableAdapter (CustomersTable, in this case), and choose "Properties." As you can see, this Properties dialog permits you to edit every aspect of the generated code, including the fetch query and the action queries used to fetch and change the underlying DataTable. You can use this property page to change what the wizards generate and go well beyond the options provided in the wizard dialogs. For example, if you want to use an ad hoc for the SelectCommand and InsertCommand but a stored procedure for the other action commands, you can configure that from here.

When you drill into the Parameters (Collection) item in the Properties dialog, you're presented with another dialog to fine-tune the individual Parameter object property settings (as shown in Figure 12.14).

Figure 12.14. Configuring Parameter properties.

Notice how the Parameters Collection Editor dialog permits you to set the @NewTimeStamp properties dialog directly. Again, note that this column is "bound" back to the source table. So, when ADO.NET executes the InsertCommand (via the Update method), the newly assigned TimeStamp value is applied back to the source table.