PERFORMING UPDATE OPERATIONS 983

Performing Update Operations

Now it’s time to look at a few really advanced database operations. Getting data out of a database, storing them to a DataSet, and even processing them on the client computer is fairly straightforward. Updating the underlying tables is also straightforward, as long as all rows and all fields have been validated. In this section, we’ll take a look at what can go wrong in moving the data from the DataSet back into the database.

As you have seen, there are two major approaches when working with ADO.NET: use the DataSet’s update method, or use the Command object to execute SQL statements and stored procedures directly against the database. Neither approach is necessarily better than the other; sometimes you’ll find DataSets more convenient to work with, sometimes not. DataSets were designed for disconnected scenarios. You can populate a DataSet with several tables, establish relations between them, and send it to Germany where someone might use it for their own purposes. Or you can take it with you to a Greek island, edit it, and bring it back two weeks later. The question is what will happen when you attempt to update the database with your DataSet’s data. If the tables are edited frequently by other users as well, then all kinds of conflicts will arise when you call the Update method. If the tables aren’t modified frequently, then there’s a good chance that most of the rows in your DataSet will successfully update the underlying tables.

You can safely use DataSets to send data to other users. You can also safely receive DataSets from other users, probably from different databases. You’ll be able to write applications to look at them, even edit them. You can also use them to update your database. The data in a DataSet sent by someone else most likely contains new data, and you can insert the appropriate rows in your database. If the same data exist in your database, you’ll probably use the new DataSet to update your database. A publisher might send you a complete list of books for your online store. It’s safe to assume that the publisher’s data contain fewer mistakes than yours and you can use the new data to update your database.

The most complicated scenario is when you want to write applications to maintain a database. Invoicing and similar applications are easy to write, because an invoice can’t be edited. Invoices are entered once, they can be viewed many times, used in calculations, but no one can change an invoice. We usually cancel an invoice in its entirety. If you want to write a front-end application for maintaining a database where things change every day, the disconnected approach of ADO.NET may not be your best bet. Here’s why. The DataSet can hold a small (or not so small) segment of your database. To make the best of the disconnected nature of the DataSet, you must keep it on the client for as long as possible. The longer you keep the disconnected DataSet on the client computer, the more you increase the chances of other users to modify the same data in the database. The problem of two or more users attempting to update the same data is as old as computers (almost) and is known as concurrency. There are two ways to deal with concurrency, optimistic concurrency and pessimistic concurrency. ADO.NET is based on optimistic concurrency.

Optimistic concurrency means that no other users will attempt to access the same data while you’re editing them. As you recall, the stored procedures generated by the DataAdapter wizard for updating the database won’t update a row if even one of its fields has changed since we last read it. If even a single field has changed value in the underlying table, then the row we have in the DataSet has been edited by someone else. Can we overwrite someone else’s edits?

Let’s think about it for a moment. If you’re editing the Customers table, you’re most likely changing addresses, phone numbers, and so on. If someone else happens to edit the same row at the same

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

984 Chapter 22 PROGRAMMING THE ADO.NET OBJECTS

time, they’re probably doing the same. Would it make any difference if you committed your edits before, or after, another user? There are many situations where the “last-write wins.” The user that updates the database last overwrites changes made by others. When you create the SQL statements or stored procedures for updating the database, you’re given the option to use optimistic concurrency or not. If you decide that the “last-write wins” scenario works for your application, don’t use optimistic concurrency.

In an airline reservation system, the “last-write wins” scenario is out of the question, obviously. We don’t want agents to assign the same seat over and over again. The same is true for an application that updates bank accounts and so on. That’s why the wizard that generates the SQL statements for updating the database uses optimistic concurrency and doesn’t update a row if it has been changed since it was read into the DataSet.

A DataRow’s Versions

How does the DataSet know that a row has been edited since it was last read? This is an interesting aspect of ADO.NET. Each row in a DataTable has several versions. The values read from the database are the Original values (this is how the DataSet knows whether a row has been modified since it read it from its table). The value of a field in the DataSet is the Current value. While the user is editing a field, the new value is the Proposed value. The Proposed value will become the Current value when the changes are written to the DataSet. Finally, the Default value is the field’s default value.

To specify which version of a field’s value you want to read, specify the second parameter of the DataRow.Item property. The following statement retrieves the Original value of the first column of the first row in the Products table of the DSProducts1 DataSet:

DSProducts1.Products.Rows(0).Item(“ProductName”, DataRowVersion.Original)

To experiment with the various versions of a row, open the EditProducts project and add a Show Versions button on its form. Then enter the statements of Listing 22.10 in the button’s Click event handler.

Listing 22.10: Reading the Versions of a DataRow

Private Sub Button1_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button1.Click Dim i As Integer

Dim row As DataRow For i = 0 To 2

row = objDSProducts.Products.Rows(i)

If row.HasVersion(DataRowVersion.Current) Then Console.WriteLine(“CURRENT “ & row.Item(“ProductName”, _

DataRowVersion.Current))

End If

If row.HasVersion(DataRowVersion.Default) Then Console.WriteLine(“DEFAULT “ & row.Item(“ProductName”, _

DataRowVersion.Default))

End If

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

PERFORMING UPDATE OPERATIONS 985

If row.HasVersion(DataRowVersion.Original) Then

Console.WriteLine(“ORIGINAL “ & row.Item(“ProductName”, _

DataRowVersion.Original))

End If

If row.HasVersion(DataRowVersion.Proposed) Then

Console.WriteLine(“PROPOSED “ & row.Item(“ProductName”, _

DataRowVersion.Proposed))

End If

Next

End Sub

The event handler of Listing 22.10 displays all the versions of the first two rows in the objDSProducts DataSet (a DataSet that holds the Products table). It displays only the ProductName field’s values in all versions, so this is the field you must edit if you want to experiment with the versions of the DataRow object.

As you can see, a row may not have all possible versions, so we use the HasVersion method to find out whether a specific version exists before we attempt to retrieve it. This code prints all available versions of the first three rows of the Products table. Run the application and edit one or more of the first three rows. Then click the Show Versions button to see the versions of the first three rows at the time.

The second product’s name is “Chang.” Change it to “Chang1” and, without moving the focus to another field, click the Show Versions button. This is what you will see in the Output window:

CURRENT Chang

DEFAULT Chang1

ORIGINAL Chang

PROPOSED Chang1

The Current version is the value of the ProductName field in the DataSet, and it’s “Chang.” The Proposed value is “Chang1.” Return to the form and click another product, then back to Chang. The action of switching to another row caused the changes to be written to the DataSet. If you click the Show Versions button again, you will see the following:

CURRENT Chang1

DEFAULT Chang1

ORIGINAL Chang

For one, there’s no Proposed value. Once the changes are saved, there’s no longer a Proposed value. The current value became “Chang1,” but the Original value did not change. You must commit the changes to the database and reload the DataSet to change a row’s Original value.

As you can guess, the various versions of the values are used in validating the data and in determining whether a row has changed since it was last read from the database. The SQL statements generated by the wizard that generates the DataAdapter object for each table use the various versions of the rows to implement optimistic concurrency. The following UPDATE statement commits the

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

986 Chapter 22 PROGRAMMING THE ADO.NET OBJECTS

changes made to a row to the underlying table, but only if the row’s fields have the same values read into the DataSet:

UPDATE dbo.Products SET ProductName = ?, SupplierID = ?, CategoryID = ?, QuantityPerUnit = ?, UnitPrice = ?, UnitsInStock = ?, UnitsOnOrder = ?, ReorderLevel = ?, Discontinued = ?

WHERE (ProductID = ?) AND

(CategoryID = ? OR ? IS NULL AND CategoryID IS NULL) AND (Discontinued = ?) AND

(ProductName = ?) AND

(QuantityPerUnit = ? OR ? IS NULL AND QuantityPerUnit IS NULL) AND (ReorderLevel = ? OR ? IS NULL AND ReorderLevel IS NULL) AND (SupplierID = ? OR ? IS NULL AND SupplierID IS NULL) AND (UnitPrice = ? OR ? IS NULL AND UnitPrice IS NULL) AND (UnitsInStock = ? OR ? IS NULL AND UnitsInStock IS NULL) AND (UnitsOnOrder = ? OR ? IS NULL AND UnitsOnOrder IS NULL)

The question marks correspond to parameters, and they’re substituted with actual values prior to executing this statement. If you turn off optimistic concurrency, the wizard will generate the following simple statement:

UPDATE dbo.Products SET ProductName = ?, SupplierID = ?, CategoryID = ?, QuantityPerUnit = ?, UnitPrice = ?, UnitsInStock = ?, UnitsOnOrder = ?, ReorderLevel = ?, Discontinued = ?

WHERE (ProductID = ?)

A DataRow’s States

In addition to versions, rows have states, too; a row can be in one of the following states:

Added The row has been added to the DataTable, but it hasn’t been accepted yet (rows are accepted after they’re written to the database as well).

Deleted The row has been deleted. However, it remains in the DataSet marked as Deleted, so that the Update method can delete the matching row of the underlying table.

Detached The row has been created but it has not been added to a DataTable yet. A row is in this state while you set its fields and before you actually add it to a table.

Modified The row has been modified, but it hasn’t been accepted yet.

Unchanged The row hasn’t been changed yet.

The state of a row is used in updating the underlying tables. When you call the DataAdapter’s Update method, all rows are moved to the database, where they’re committed with the appropriate SQL statement or stored procedure. To conserve bandwidth, you can send only the modified, added, and deleted rows to the database. This is the technique used by the DataForm wizard, and it’s worth taking a look at the code it produces. All the action of updating the database takes place in the UpdateDataSource() function, which returns the number of rows that were updated successfully. Listing 22.11 shows the code of the UpdateDataSource() function.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

PERFORMING UPDATE OPERATIONS 987

Listing 22.11: The UpdateDataSource() Function of the DataForm Wizard

Public Function UpdateDataSource(ByVal dataSet As EditProducts.DSProducts) _ As System.Int32

Me.OleDbConnection1.Open()

Dim UpdatedRows As System.Data.DataSet

Dim InsertedRows As System.Data.DataSet

Dim DeletedRows As System.Data.DataSet Dim AffectedRows As Integer = 0

UpdatedRows = DataSet.GetChanges(System.Data.DataRowState.Modified) InsertedRows = DataSet.GetChanges(System.Data.DataRowState.Added) DeletedRows = DataSet.GetChanges(System.Data.DataRowState.Deleted) Try

If (Not (UpdatedRows) Is Nothing) Then

AffectedRows = OleDbDataAdapter1.Update(UpdatedRows)

AffectedRows = (AffectedRows + OleDbDataAdapter2.Update(UpdatedRows)) AffectedRows = (AffectedRows + OleDbDataAdapter3.Update(UpdatedRows))

End If

If (Not (InsertedRows) Is Nothing) Then

AffectedRows = (AffectedRows + OleDbDataAdapter1.Update(InsertedRows)) AffectedRows = (AffectedRows + OleDbDataAdapter2.Update(InsertedRows)) AffectedRows = (AffectedRows + OleDbDataAdapter3.Update(InsertedRows))

End If

If (Not (DeletedRows) Is Nothing) Then

AffectedRows = (AffectedRows + OleDbDataAdapter1.Update(DeletedRows)) AffectedRows = (AffectedRows + OleDbDataAdapter2.Update(DeletedRows)) AffectedRows = (AffectedRows + OleDbDataAdapter3.Update(DeletedRows))

End If

Catch updateException As System.Exception Throw updateException

Finally Me.OleDbConnection1.Close()

End Try

End Function

This function generates three new DataSets and populates them with the added, deleted, and modified rows of the original DataSets. In a large DataSet, the three partial DataSets will be considerably smaller than the original DataSet. Then, it calls the Update method of the corresponding DataAdapter object, passing the appropriate DataSet.

The order of the DataSets passed to the Update method is important. First it passes the rows that were edited, then the new rows, and finally the deleted rows.

If you examine the code generated by the wizard, you’ll see that it doesn’t reload the DataSet. Instead, it merges the rows that successfully updated the underlying tables with the existing rows in the DataSet (it basically consolidates the changes) and then calls the DataSet object’s AcceptChanges method. AcceptChanges sets the Original value of all fields to the Current value. In effect, the new DataSet is the same as the one you would get by reloading it from the database. If your DataSet isn’t

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com