OleDbCommandBuilder Object Reference

Refreshing a Row After Submitting an Update

In Chapter 10, you learned how to construct and use INSERT, UPDATE, and DELETE queries to submit changes to your database. These queries are basically one-way streets. The database modifies the contents of the row based on the information you supply in the query. Although the database reports how many rows the query affected, it does not return the new contents of the modified rows.

Sometimes submitting an update to your database needs to be a two-way street. In Chapter 10, I discussed using the Microsoft SQL Server timestamp data type to ensure that you don't unintentionally overwrite another user's changes to the database. When the contents of the row change, the database generates a new value for the timestamp column for that row. Consider the following scenario.

Your application displays the contents of an order. The user adds a new a line item to the order, which corresponds to a row in your table, which is similar to the Northwind database's Order Details table. However, in your table you have a timestamp column and you're using the value of this timestamp column in your updating logic. When the user submits the new line item to the database, the database generates the value for the timestamp column of the new row. No problem there.

For the sake of discussion, let's say the application has a Microsoft Windows front end instead of using Web forms. After the user submits the new line item, the contents of the order are still visible in the application. What if the user needs to modify that same line item and submit that change to the database?

Remember that the DataAdapter object's updating logic uses the value of the timestamp column in the UpdateCommand. The database generates a value for the timestamp column for the row when you submit the new row. If that timestamp value does not appear in your DataRow object, the update attempt will fail.

You can run into the same problem when you modify a row, submit that change to the database, and then attempt to modify that row again. The database will generate a new value for the timestamp column when you submit the first change. If you don't have the new value for the timestamp column in your DataRow object, the subsequent update attempt will fail.

Retrieving the Newly Generated Value for the Timestamp Column After You Submit an Update

Let's say the initial query to retrieve data from the Order Details table looks like this:

SELECT OrderID, ProductID, Quantity, UnitPrice, TimestampColumn     FROM [Order Details] WHERE OrderID = ?

You learned in the previous chapter that you can submit updates to the table using the following parameterized query:

UPDATE [Order Details]     SET OrderID = ?, ProductID = ?, Quantity = ?, UnitPrice = ?     WHERE OrderID = ? AND ProductID = ? AND TSCol = ?

We can issue the following query to retrieve the new value that the database generates for the timestamp column:

SELECT TSCol FROM [Order Details] WHERE OrderID = ? AND ProductID = ?

Sure, you could manually execute this query after submitting an update. But what if you have a series of changes to submit?

Let's see how we can use this query in conjunction with the ADO.NET object model to retrieve these values automatically after submitting updates.

Using Batch Queries to Retrieve Data After You Submit an Update

In Chapter 5, you learned how to use a DataAdapter to fetch the results of a batch query such as this one:

SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers; SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders

You can also use batched queries to retrieve data after you submit an update. You can combine the update query and the query to retrieve the new timestamp value by using a batched query. Set the CommandText of the DataAdapter object's UpdateCommand to the following parameterized query:

UPDATE [Order Details]     SET OrderID = ?, ProductID = ?, Quantity = ?, UnitPrice = ?     WHERE OrderID = ? AND ProductID = ? AND TSCol = ?; SELECT TSCol FROM [Order Details] WHERE OrderID = ? AND ProductID = ?

note

Not all databases support row-returning batch queries. Microsoft SQL Server supports this functionality. Oracle and Microsoft Access databases do not. Check your database's documentation to determine whether it supports this functionality.

When the DataAdapter submits the pending change, it will also execute the subsequent SELECT query and store the results in the modified DataRow. The DataRow object will have the newly generated value for the timestamp column, which means you can modify the row again and successfully submit those new changes.

The Command Object's UpdatedDataSource Property

The DataAdapter uses the Command objects stored in its InsertCommand, UpdateCommand, and DeleteCommand properties to submit updates to your database. But how does the DataAdapter know to look for results from InsertCommand and UpdateCommand? The answer lies in the Command object's UpdatedRowSource property.

The UpdatedRowSource property accepts a value from the UpdateRowSource enumeration. (The available values are listed in Table 11-1.) By default, the Command will fetch new data for the modified row by checking for output parameters and the first row returned by the query.

Table 11-1 Members of the UpdateRowSource Enumeration

Constant

Value

Description

Both

3

Tells Command to fetch new data for the row using both the first returned record and output parameters. This is the default.

FirstReturnedRecord

2

Tells Command to fetch new data for the row through the first returned record

None

0

Tells Command not to fetch new data for the row upon execution.

OutputParameters

1

Tells Command to fetch new data for the row using output parameters

You can improve the performance of your update by setting the value of the UpdatedDataSource property to the appropriate value. I performed a small, informal test using this property. I used a DataAdapter to retrieve the contents of the Order Details table and insert all the rows in a new table with the same structure. Initially, I left the UpdatedDataSource property at its default value, Both.

In my test, the InsertCommand simply submitted the new row. It does not include a batch query to refetch the contents of the row after the update is submitted, so there's no need to have the InsertCommand property's UpdatedDataSource property set to anything other than None. When I set the property to None and reran the test, the test ran between 1 and 2 percent faster.

When the UpdatedDataSource property is set to Both, the default, the Command will check for output parameters and for the first row returned by the Command, even if the Command does not return data. You should set the UpdatedDataSource property of your Command objects to the appropriate value, or else you'll pay a small but unnecessary performance penalty.

Retrieving New Data Using Output Parameters

The UpdatedDataSource property also lets you specify that you will retrieve new data using output parameters.

We can build a stored procedure that uses a similar UPDATE query to modify a row in the Order Details table and returns the new timestamp value using an output parameter, as shown here:

CREATE PROCEDURE spUpdateDetail (@OrderIDNew int, @ProductIDNew int, @QuantityNew smallint,  @UnitPriceNew money, @OrderIDOrig int, @ProductIDOrig int,  @TSCol timestamp OUTPUT) AS UPDATE [Order Details]     SET OrderID = @OrderIDNew, ProductID = @ProductIDNew,          Quantity = @QuantityNew, UnitPrice = @UnitPriceNew     WHERE OrderID = @OrderIDOrig AND ProductID = @ProductIDOrig           AND TSCol = @TSCol; IF @@ROWCOUNT = 1     SELECT @TSCol = TSCol FROM [Order Details]         WHERE OrderID = @OrderIDNew AND ProductID = @OrderIDNew

All that's left to do is to set the UpdateCommand property's CommandText to the stored procedure, build the Command object's Parameters collection, and set the Command object's UpdatedRowSource property to OutputParameters or Both. It's really that easy.

This process is more efficient than returning data in a SELECT query. It's much faster to check the value of a parameter than to fetch the results of a query. Plus, databases such as Oracle support output parameters but not batch queries that return rows.

Using the DataAdapter Object's RowUpdated Event to Retrieve Data After You Submit an Update

Some databases, such as Microsoft Access, don't support batch queries and don't support output parameters on stored procedures. If you're using such a database, the two previous methods of retrieving data after performing an update aren't available. However, you do have one option, apart from moving to a database that supports more functionality.

The DataAdapter object exposes two events that it throws when it submits the changes cached in a DataRow: RowUpdating and RowUpdated. As their names imply, the former occurs just before you submit the change and the latter occurs immediately after you submit the change.

If you submit the changes in multiple rows, the RowUpdating and RowUpdated events will fire for each row. You won't get all of the RowUpdating events and then all of the RowUpdated events. If you add logging code to the events, you'll see entries like the following in your log file:

RowUpdating event fired for row #1 RowUpdated event fired for row #1 RowUpdating event fired for row #2 RowUpdated event fired for row #2 RowUpdating event fired for row #3 RowUpdated event fired for row #3

We can use the RowUpdated event to fetch the new value that the database generates for the updated row. The following code snippet includes such an example. The code demonstrates how to handle the RowUpdated event. For the sake of brevity, it references imaginary functions that create the DataTable, the DataAdapter, and the Command used to fetch the new timestamp value.

Note that in the RowUpdated event handler, the code tests to make sure the update succeeded and that the change stored in the row constituted an insert or an update. Obviously, if we've just deleted a row from the database, there's no need to query for a timestamp value. Also, because the Command to fetch the new timestamp value fetches only a single value, the code snippet uses the ExecuteScalar method to retrieve that value.

Visual Basic .NET

Dim da As OleDbDataAdapter = CreateMyDataAdapter() Dim cmdGetNewTS As OleDbCommand = CreateGetNewTSCommand() AddHandler da.RowUpdated, AddressOf HandleRowUpdated Dim tbl As DataTable = CreateMyDataTable() da.Fill(tbl)  da.Update(tbl) Private Sub HandleRowUpdated(ByVal sender As Object, _                              ByVal e As OleDbRowUpdatedEventArgs)     If e.Status = UpdateStatus.Continue AndAlso _       (e.StatementType = StatementType.Insert OrElse _        e.StatementType = StatementType.Update) Then         cmdGetNewTS.Parameters("@OrderID").Value = e.Row("OrderID")         cmdGetNewTS.Parameters("@ProductID").Value = e.Row("ProductID")         e.Row("TSCol") = CType(cmdGetNewTS.ExecuteScalar, Byte())         e.Row.AcceptChanges()     End If End Sub

Visual C# .NET

OleDbDataAdapter da = CreateMyDataAdapter(); OleDbCommand cmdGetNewTS = CreateGetNewTSCommand(); da.RowUpdated += new OleDbRowUpdatedEventHandler(HandleRowUpdated); DataTable tbl = CreateMyDataTable(); da.Fill(tbl);  da.Update(tbl); private void HandleRowUpdated(object sender, OleDbRowUpdatedEventArgs e) {     if ((e.Status == UpdateStatus.Continue) &&        ((e.StatementType == StatementType.Insert) __         (e.StatementType == StatementType.Update)))     {         cmdGetNewTS.Parameters["@OrderID"].Value = e.Row["OrderID"];         cmdGetNewTS.Parameters["@ProductID"].Value = e.Row["ProductID"];         e.Row["TSCol"] = (byte[]) cmdGetNewTS.ExecuteScalar();         e.Row.AcceptChanges();     } }

After retrieving the new timestamp value and assigning it to the appropriate column in the DataRow object, the code calls the AcceptChanges method of the DataRow object to commit this change to the DataRow. Without the call to the AcceptChanges method, the DataRow object would cache this change so that it can submit the change to the database later. That's not what we want to accomplish by retrieving the new timestamp value. The call to the AcceptChanges method tells ADO.NET to simply accept the change stored in the DataRow.

This approach is very flexible because it will work with any database, but you pay a price in performance. In the tests I've run, using events to retrieve timestamp values proved to be about 35 percent slower than the batch query approach and about 50 percent slower than using output parameters on stored procedures.

The Timestamp Sample Application

On the companion CD, you'll find a sample application written in Visual Basic .NET and in Visual C# .NET that demonstrates all three methods of retrieving data after you submit an update. (See Figure 11-1.)

The application demonstrates the initial problem: unless you fetch new timestamp values after you submit changes to your database, subsequent attempts to update those same rows will fail.

Figure 11-1

The Timestamp sample application



Microsoft ADO. NET Core Reference
Microsoft ADO.NET (Core Reference) (PRO-Developer)
ISBN: 0735614237
EAN: 2147483647
Year: 2002
Pages: 104
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net