ADO.NET Best Practices, Part II : Page 4

Retrieving Server-Generated Values
More often than not, when you make updates across tables, you need a previously generated value to successfully run the next statement within a stored procedure. Such a typical situation might involve triggers or identity columns. If you're using direct commands or stored procedures, there's not much that ADO.NET does for you. You must address the issue yourself. One common workaround entails reading critical values back through a subsequent query. You can return the values you need through output parameters or additional result sets. This solution works but you have to code it yourself. If you use the ADO.NET batch update instead, you can rely on a couple of built-in mechanisms that save you from extra coding. The first mechanism is based on the UpdatedRowSource property of the command object. The other mechanism leverages the RowUpdated event on the data adapter object.

The UpdatedRowSource property looks for output parameters, the first row of the next query, both, or neither. By default, it looks for both. The following SQL batch inserts a new row into a database where custID is an identity column. The second statement retrieves the last identity value generated in the scope and returns it as the updated value of the custID column.

   INSERT INTO customers (custID, custName) VALUES (@CustID, @CustName)
   SELECT SCOPE_IDENTITY() AS custID

If you assign the above statements to the InsertCommand property of a data adapter, the UpdatedRowSource property will catch the newly generated identity value and pass it to the DataRow object being updated. If you set the UpdatedRowSource property to Both (the default) or FirstReturnedRecord, each inserted row processed through the batch update will have the identity column updated. And, more importantly, you don't have to write any additional code. You only need to select a checkbox in the Visual Studio .NET wizard that configures a data adapter.

A data adapter that is about to update a row during a batch update process fires the RowUpdating and RowUpdated events, respectively, before and after the update. You handle the RowUpdated event as follows:

   adapter.RowUpdated += new 
      SqlRowUpdatedEventHandler(OnRowUpdated);

The event handler gets a data structure of type SqlRowUpdatedEventArgs in which the Row property returns the row as updated by the underlying database. You'll need to modify this code database. You must design effective queries and update strategies to make a scalable application. To write effective SQL code, you should know the SQL engine you're using, and realize that they may not be equal. If you're building a .NET application, you also need a good understanding of the ADO.NET model and its capabilities. ADO.NET is powerful but it doesn't reduce database programming to mere point-and-click programming. ADO.NET offers a set of powerful tools but without practice and experience you may not do a lot with them. The command builder object or even the batch update process, for example, greatly simplifies the update process but you must know what they do and how they work.

Previous Page