Complex Updates with Server-Side Logic

When working with more complex relational data structures, especially where you must update more than just a simple parent-child hierarchy, a common approach is to build and execute a set of stored procedures that handle the action command operations so the transaction, update, and collision-detection logic is all handled on the servermuch as we just did using the TableAdapter Configuration wizard. Frankly, this is how updates are handled in many applications, as it is least dependant on the vagarities of the data access interfaces.

In these situations, you can build a set of Command objects to capture the parameters required for the underlying action commands implemented as stored proceduresbasically, mirroring what was done by the TACW in the previous examples. Yes, this is a lot of trouble and requires that you build some fairly brittle code. This means if the stored procedure signatures change, you'll find yourself back modifying the code to adapt the stored procedure changes to your application. Of course, the TableAdapter approach is just as brittlebut in that case, you only have to figure out how to refresh the code generated by the TableAdapter Configuration wizardnot a simple feat, as I discussed in Chapter 6.

Using Events to Execute Your Own Update Logic

If you plan to use the SqlDataAdapter Update method, you'll have an opportunity to call your own server-side executable code or simply execute your own action commands by trapping the RowUpdating event. This is not an option when working with the TableAdapter unless you add these events yourself. When you're working with UI controls bound to a DataTable, the DataAdapter Update method can be very attractive. Sure, it does not do much, so it would not be that hard to replicate its functionality, but this is code you have to write, debug, and train your organization to use. In this case, it's your responsibility to handle every aspect of the update process, including:

• Populating input and input/output parameter values

• Executing the appropriate Command (s) based on the DataRow RowState property

• Capturing the resultset RowsAffected value and updated rowsets from the action commands

• Testing RowsAffected for an acceptable value

• Posting changes to the underlying DataTable rows

• Resetting each DataRow object's RowState property to reflect the change, assuming it worked, or call AcceptChanges once all rows have been processed

• Handling concurrency and other exceptions

• Repeating, as necessary, for all DataTable changes

Tip

The TableAdapter does not expose the RowUpdating or RowUpdated events unless you code them yourself.

Once you execute the Update method, calling one or more stored procedures instead of (or in addition to) the DataAdapter action commands is done by "hooking" the DataAdapter Update method events. When the SqlDataAdapter Update method is executed, two events are fired as each data row is updatedunless you're in "batch" mode, which I discuss in a minute.

The sequence of operations is as follows:

1. The values in the DataRow to be updated are moved to the parameter values passed by the Command Parameters collection.

2. The RowUpdating event is raised. Here, you have an opportunity to execute your custom codeeven other update operationsbefore the action command is passed to the server for execution. You can also prevent the action command from being executed. Arguments passed to this event include^[2]

   ^[2] These arguments are different for batch modeI'll get to that in the next section.

   • Command: The Command to be executed.

   • Errors: Errors generated by the provider when the command executes. Since the Command has not executed yet, it's not clear what errors would appear in the RowUpdating event. This makes more sense in the RowUpdated event.

   • Row: The DataRow to be updated.

   • StatementType: The type of statement to be executed (update, insert, delete, batch, or select).

   • Status: Determines the action to take when exiting the OnRowUpdating event handler. These include:

     • Continue: The DataAdapter is to execute the CommandText.

     • ErrorsOccurred: Tell ADO.NET that the Update operation failed.

     • SkipAllRemainingRows: Tell ADO.NET that no further rows should be processed by the Update method.

     • SkipCurrentRow: Tell ADO.NET that the current row is not to be processed by the Update method.

3. If the action argument of the RowUpdating event handler is set to Continue, the CommandText is passed to SQL Server for execution.

4. If the Command is set to FirstReturnedRecord (a DataReader execution option), the first returned rowset is placed in the DataRow.

5. If OUTPUT parameters are returned by the query, they are placed in the DataRow (in the Parameters collection).

6. The RowUpdated event is raised. Here, you have another opportunity to execute other codeeven other update options. The event arguments include the newly updated row and the number of rows affected by the CommandText action command.

7. AcceptChanges is called. This resets the RowState of each row in the DataTable to "Unchanged" to indicate that all changes have been made.

If you don't want the action query to execute, you can set the action argument in the event handlers and call AcceptChanges yourselfone of the only times it makes sense to call AcceptChanges.

Stepping Through the UpdateEvents Example

I wrote an example UpdateEvents to illustrate an application that performs many of these functionsyou can find it under this chapter on the DVD. This example fetches rows into a DataTable using the DataAdapter Fill method and binds the rowset to a DataGridView. This control handles several jobs besides displaying the data. It also provides a way to change individual columns, add new rows, and delete specified rows. As you'll see later, when there are errors, each row containing an error is automatically flagged.

Let's step through the code. The first routine I want to show you initializes the DataAdapter and its fetch and action commands. Figure 12.15 illustrates how the SelectCommand is initialized to call a parameterized stored procedure, whose @CustomerNameWanted Parameter value is set from a TextBox control on the form. This DataAdapter initialization is done oncebefore the application shows its first form.

Figure 12.15. Initializing the DataAdapter SelectCommand.

As shown in Figure 12.16, the UpdateCommand calls a stored procedure that executes the UPDATE statement against the specified row (as indicated by the @CustID Parameter value). Pay close attention to how the Parameter objects are declared. Note that the code binds each parameter to the appropriate DataTable column's Original Version value. This way, we don't have to worry about setting the Parameter value just before the Update method is firedit's set automatically.

Figure 12.16. Setting up the DataAdapter SelectCommand.

The "CustomerUpdate" stored procedure uses TimeStamp concurrency, so it's important to pass the previously fetched (@OriginialTimeStamp) value to the stored procedure. The stored procedure returns the new TimeStamp in @NewTimeStamp in an OUTPUT parameter. Note that I bind the @NewTimeStamp Parameter back to the TimeStamp source column in the local DataTable, which writes the new value back to the "current" version of the TimeStamp column when the Update method succeeds. Setting the UpdateCommand object's UpdatedRowSource property enables this automatic postback data binding. The InsertCommand and DeleteCommand are set up in a similar fashion later in the same subroutine. When the user clicks the "Fetch" button, the following code is invoked to execute the query, populate the DataTable, and bind it to the DataGridView control, as shown in Figure 12.17.

Figure 12.17. Populating the DataTable with the parameterized Fill method call.

IMHO

Note that I hide the TimeStamp column to help prevent the DataGridView from choking on the binary column. I think it's really dumb that the DataGridView does not have a default behavior for binary values like a TimeStamp. I also added a DataError event handler to deal with these error events.

When the user makes changes to the DataGridView containing the fetched rowset, the DataTable EditMode is set and these changes are posted to the underlying DataTable in behind-the-scenes columns designed to hold interim values or "versions" of the data column values. ADO.NET supports four versions of the data values for each column. You can examine these versions if (and only if) the DataRow.HasVersion property is True. The available versions include:

• Current Version: Contains the value returned when the Row(n).Item is referencedin other words, the current value of the specified DataRow column. The Current version is exposed via Row(n.CurrentVersion)but is available only if DataRowVersion.Current is True. Figure 12.18 illustrates fetching the Current version of a DataRow. The Current version becomes available when the BeginEdit method is called.

  Figure 12.18. Exposing the Current (and Proposed) versions kept in the Rows collection.

  

• Default Version: This is the default version of the DataRow valuedepending on the DataRowState. For all states except Detached, the Default version is Current.

• Original Version: This version contains the pre-modified column value. Its value is set to the Current value when the DataRow or DataTable AcceptChanges method is called.

• Proposed Version: When a change is made to a column in Edit mode, the value is exposed in the Proposed version. This version is made available when BeginEdit is executed and deleted when CancelEdit or RejectChanges is executed. When RejectChanges is executed, the Proposed version value is replaced with the Current version value.

These "versions" of the DataRow value can be invaluable when you're executing business rules or other editing criteria checks. For example, your code could check to see if the edited value (the Proposed version) is more than X percent higher or lower than the Current or Original version.

As you can see, the DataRow object's BeginEdit, EndEdit, and CancelEdit methods play an important role in how data (especially bound data) is handled within the DataTable. If your code does not properly end an editing session, the data in your bound controls won't (reliably) end up in the bound DataTable.

Executing the Update Method

Okay, we're ready to fire the Update method and get the process of changing the database based on the current contents of the DataTable. In this example, this routine is fairly simplethat's because I'm not using "batch" mode; each action command is executed individuallyusing one round-trip to the server for each operation. Frankly, this approach is far easier to code and handle errors than the batch mode I'll discuss later in this section.

Figure 12.19 illustrates the routine to fire the Update method against the DataAdapter it starts by flushing the bound controls to the bound DataTable (by calling EndEdit). Note that the default DataAdapter.Update syntax updates all of the DataTables associated with the DataAdapterit's a better idea to specify which DataTable in the DataSet to update, as I've done in this example.

Figure 12.19. The Update button event handler.

Once the Update event fires, it performs several of the steps I outlined earlier in this chapter (see "Using Events to Execute Your Own Update Logic").

Trapping the RowUpdating Event

As each row is prepared to be sent to the server for updating, the RowUpdating event is fired. (The event handler is shown in Figure 12.20.) It fires once for each row being processedbefore the action command has been sent to the server for execution. The event argument e contains detailed information about what's about to be executed and the DataRow in its current (proposed) state. I've programmed this RowUpdating event handler to tackle two jobs:

1. Process any row-based business rules. In this case, I check to make sure the Discount column is not set to a value outside the range of 0 to N (where N is set by a constant or fetched from the database during application initialization).

2. Set action command Parameters in situations where the Parameter values are not supplied automatically by Parameter binding.

Figure 12.20. Initializing the DataAdapter RowUpdating event handler.

I set up the RowUpdating event to make sure that my client-side business rules can permit the row to be updated. In this case, the client-side (application-based) business rules say that a customer's discount cannot be greater than a specified value (75%). Yes, these business rules should be fetched and updated when the application begins and reset when needed by whatever mechanism makes sense.

The RowUpdating event can also be used to set action Command Parameter values. This is not necessary for the UpdateCommand, as I've already chosen to automatically bind these Parameters to the DataTable using the SourceTable Parameter property. Figure 12.21 illustrates how I set the Parameter values for the Insert and Delete action commands and test the business rule(s). Note that the RowUpdating event handler passes the StatementType, which is used to provide special-case code for each action command.

Figure 12.21. Managing business rules and populating Parameter values prior to action command execution.

If your code's logic or business rules determine that the row should not be passed to the server as an UPDATE or INSERT, or that the row should not be deleted, you have several options. First, you can set the e.Status argument to UpdateStatus.ErrorsOccurred, UpdateStatus.SkipCurrentRow, or UpdateStatus.SkipAllRows. If e.Status is left alone or set to UpdateStatus.Continue, the action command is passed to the server for execution when the event handler ends. Remember, the RowUpdating event fires once for each row that needs inserting, changing, or deleting based on the RowStatethat is, unless you've enabled "batch" mode, which I'll discuss a little later in this section.

Trapping the RowUpdated Event

Once the action command is executed, the DataAdapter RowUpdated event fires. Again, you can take this opportunity to execute business rules, but it's really too late for thatthe rows might have already been added to the database. However, the data state might have been set by another application, so perhaps you'll find a situation where you should throw an error on data validation failure. This is the place to check for errors. As this event fires after each action command is executed, it's fairly easy to handle the errors. Figure 12.22 illustrates how this event handler can be coded. Again, your logic and action commands determine what constitutes an error. Perhaps, the number of RecordsAffected should be six or some other value (other than one). This approach gives you the flexibility to determine what's correct and what's not.

Figure 12.22. Handling the RowUpdated event.

Managing Batch Mode Updates

When processing changes to a DataTable, unless you enable "Batch Update" mode, each change to the DataTable is treated like a separate operationwith one round-trip to the server for each change. This can be somewhat expensive when there are lots of changes to be made. That said, I wonder how much overhead is too much. Consider that once you switch to batch mode, your exception handlers must be far more complex, and as a result, your application can be harder to write and maintain. If you're finding that these round-trips are affecting performance, perhaps it's because you're making bulk changeschanges that would better be handled on the server or with the SqlBulkCopy method.

Enabling batch mode is deceptively simpleall you do is change the DataAdapter UpdateBatchSize property to a value greater than 1. The UpdateBatchSize determines how many rows are sent to SQL Server in a single round-trip. If it's set to 0, ADO.NET sets the size to the maximum size supported by SQL Server. In my experience, a batch size of about 50 is optimala larger batch size overloads the caching algorithms, a smaller size defeats the purpose of batching. Of course, your mileage may vary, so experiment with various batch sizes to see which works best for you.

I say "deceptively" simple because once you set batch mode (which is enabled for the first time in ADO.NET 2.0), several criteria change. Let's step through these changes:

• In batch mode, the UpdatedRowSource property (which determines how [or if] ADO.NET fetches the post-update values from your UpdateCommand and InsertCommand) must be set to None or OutputParameters. This means, in batch mode, ADO.NET can't accept rowsets from SELECT statements executed after the action command.

• In batch mode, the RowUpdating event fires once for each row being processed, just as before, but the RowUpdated event fires only after all of the rows have been processed. The event handler returns an array of DataRow objects that contain the error status and RowState for each row processedthis is fetched with the e,CopyToRows method, as shown in the example (illustrated in Figure 12.23).

  Figure 12.23. The btnUpdate_Click event handler invokes the Update method.

  

• In batch mode, you can choose to stop processing the batch on first error or continue despite errors. If you choose to stop on the first error, no changes are posted to the databaseeven when 1 change out of 100 fails. If you choose to stop only after all change operations have been attempted, you can leave your database in an indeterminate statewith some changes made and others not.

• In batch mode, the entire batch can be rolled back if the severity of the exception thrown by SQL Server in the procedure (or in a trigger) exceeds Severity 11.

Examining the ExecuteUpdateEvents BatchMode Example

I modified the previous example to incorporate batch mode processing, and as I said, things got far more complex. Let's visit some of the parts of the application that were affected. Let's start with the btnUpdate_Click event handler (shown in Figure 12.23) that is fired when the user requests to post changes to the database.

While this routine is similar to the earlier example, note that I set the DataAdapter UpdateBatchSize and the DataAdapter ContinueUpdateOnError properties before executing the Update method. This code is set up to handle exceptions from at least three different sourcesfrom SQL Server (SqlException) and from ADO.NET (DBConcurrencyException and DataException).

Let's continue with the RowUpdated event handler, shown in Figure 12.24.

Figure 12.24. The RowUpdated event handler for the batch mode example.

Notice that the SqlRowUpdatedEventArgs structure exposes a CopyToRows method that returns an array of DataRow objects. These objects contain the RowState, Table, HasErrors, and RowError properties of the DataRows processed in the last batch. The example binds this array of DataRow objects to a DataGridView to show what it contains. In this case (as shown in Figure 12.25), it does not contain any indication that any row failed to update.

Figure 12.25. The CopyToRows method returns the RowState and other details of the action command operations.

The code in Figure 12.24 also dumps the list of errors for each column and row in this DataRow array populated with CopyToRows. The rest of the RowUpdated event handler is basically the same as the nonbatch mode example shown earlier.