Database Access with Visual Basic .NET (3rd Edition)
| If you've written (or even used) a multiuser database application, you've probably run into concurrency conflicts. They arise when multiple users try to modify the same database data rows at the same time. That is, User A reads some data, User B reads the same data, and User A modifies that data. Now User B comes along and also wants to modify the same data. How do you deal with this problem? There are two basic approaches to handling concurrency control. The first approach pessimistic concurrency control, or locking essentially prevents the problem of User B overwriting the changes of User A by never letting the situation get to that point. In this approach, when a user reads data with the intention of modifying it, a lock is placed on that data. This lock makes that data unavailable to other users until the first user has completed his task and releases the lock. Such an approach is useful when there is a lot of contention for the same data or when a user must always be able to see the most up-to-date values of the data. A typical scenario might be a real-time inventory management or order management system, where the user doesn't want to accept any order unless she is positive that the item(s) are in stock. The major disadvantages of pessimistic concurrency control are the extra overhead of constantly managing the data locks, the need for a continuous connection to the database, and the lack of scalability. This approach has scalability problems, especially when used in a distributed environment (such as an Internet), wherein users may end up locking records for many seconds or even minutes. The second approach is optimistic concurrency control, or locking. In this approach, no data rows are locked, except for the very short span of time when the data is actually being updated. This approach avoids the issue of lock management and the problem of scalability, and it works just fine when a user is performing editing operations while disconnected from the database. However, what happens when User B returns to update data that has already been modified by User A? One option is to say that the last update is the only one that counts. However, there are not too many applications for this option to be feasible policy. What you need to do with optimistic concurrency is to detect whether the data has been modified since it was originally retrieved, called a concurrency violation. There are two basic approaches to implementing detection. The first is to maintain a timestamp or unique version number for each row, which is updated whenever the row is modified. The original value of the timestamp or version number is included as part of the WHERE clause of the update statement. The second approach is to save the original values of the fields. These values become additional conditions in the WHERE clause of the update statement. In either case, if the original row has been changed, the condition in the WHERE clause won't be met, the row won't be found, and no row will be updated. Note If you used optimistic locking with ADO 2.X and wondered why the error message associated with a concurrency violation refers to not being able to find the specified row, rather than stating that there was a concurrency violation, now you know why.
ADO.NET supports only optimistic concurrency control there is currently no built-in support for pessimistic locking. Visual Studio offers several options for implementing optimistic concurrency. This support is in line with the general pattern of extensive support for distributed, disconnected, and asynchronous application architectures. The SQL statements for the Update and Delete commands generated by both the CommandBuilder and the DataAdapter Configuration Wizard both include a WHERE clause that detects concurrency conflicts. Let's take at look at the relevant code that we generated in Chapter 6 with the DataAdapter Configuration Wizard. You can obtain the generated code by expanding the Windows Form Designer generated code region when viewing the form frmUpdates in the code window. First, look at the SQL Update statement (reformatted for easier reading) in Listing 7.1. Listing 7.1 The SQL Update Statement generated by the DataAdapter Configuration Wizard
UPDATE tblEmployee SET FirstName = @FirstName, LastName = @LastName, DepartmentID = @DepartmentID, Salary = @Salary WHERE (ID = @Original_ID) AND (DepartmentID = @Original_DepartmentID OR @Original_DepartmentID IS NULL AND DepartmentID IS NULL) AND (FirstName = @Original_FirstName) AND (LastName = @Original_LastName) AND (Salary = @Original_Salary OR @Original_Salary IS NULL AND Salary IS NULL) ; SELECT FirstName, LastName, DepartmentID, Salary, ID FROM tblEmployee WHERE (ID = @ID) It starts as a standard Update statement, setting the values of the four updatable columns to the new values passed as parameters to the UpdateCommand object. The WHERE clause contains the primary key field (ID), as well as the original values of each of the other columns, and tests to see if these original values match the current values for the row in the database. This generated statement goes even further and checks for NULL values in both the database and current values for columns that are nullable. A Select statement (the one we specified when configuring the DataAdapter) follows the semicolon. The semicolon is the separator between commands in a batch statement, and the Select statement is added by default to return the refreshed row to the application. Let's now look at the code for setting the parameters for the UpdateCommand object, as shown in Listing 7.2. Listing 7.2 Code to set command parameters generated by the DataAdapter Configuration Wizard
Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter ("@FirstName", Ten command parameters are defined for this command object. The first four are the current (possibly modified) values of the columns that are to be updated to the row in the database. Remember, we discussed earlier in Chapter 5 that each row maintains as many as four different versions of the values for that row. By default, if you don't specify otherwise, you receive the current value for the column that you read. The next five parameters are the original values of all of the columns used as the values in the WHERE clause. Note that, to retrieve the original value of a column (rather than the default current value), you need to specify the row version as System.Data.DataRowVersion.Original in the constructor for the SqlParameter added to the command object. Note You don't have to include the original values of all of the columns in the WHERE clause. You can customize any of the update command objects, so you may decide that, when updating a row, you need only be alerted if another user modified one or two specific columns. But you can go ahead and update the database if one of the other columns was modified.
The last parameter is the current value of the ID column, used as the parameter for the Select statement used to bring back the updated values of the row. After each insert, update, or delete operation, the DataAdapter examines the number of rows affected by the operation. If the number of rows affected is zero, it throws the DBConcurrency-Exception exception because it assumes that this outcome is usually the result of a concurrency violation. We could add an exception handler for this to our Try-Catch block in the routine btnUpdate_Click, as shown in Listing 7.3. Listing 7.3 Try-Catch block with exception handler for DBConcurrencyException
Private Sub btnUpdate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnUpdate.Click Try daEmployees.Update(dsEmployeeInfo, "Employees") 'SqlDataAdapter1.Update(dsEmployeeInfo, "Employees") Catch ec As DBConcurrencyException 'Do something !! Catch es As SqlException MessageBox.Show(es.Message) End Try End Sub |