| The Update statement is the most interesting of the statements generated by the wizard. It contains the parameters required to support optimistic concurrency. To see the statement, go to the Property window and find the UpdateCommand property. Just as with the SelectCommand, the UpdateCommand is an SqlCommand object. Click the plus box to expand it and then find the CommandText property. When you click the ellipsis button, a query designer opens just as with the Select statement. UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, Address = @Address, City = @City, Region = @Region, PostalCode = @PostalCode, Country = @Country, Phone = @Phone, Fax = @Fax WHERE (CustomerID = @Original_CustomerID) AND (Address = @Original_Address OR @Original_Address IS NULL AND Address IS NULL) AND (City = @Original_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Original_CompanyName) AND (ContactName = @Original_ContactName OR @Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle OR @Original_ContactTitle IS NULL AND ContactTitle IS NULL) AND (Country = @Original_Country OR @Original_Country IS NULL AND Country IS NULL) AND (Fax = @Original_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND (Phone = @Original_Phone OR @Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = @Original_PostalCode OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND (Region = @Original_Region OR @Original_Region IS NULL AND Region IS NULL); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID) The bold SQL keywords are to aid readability. What happened to our simple Select statement? Because we selected Optimistic Concurrency and Refresh the DataSet, the statement got a lot more complicated. If we take a look at the Parameter collection we will see that there are two versions of each parameter, one for the main parameter, and one with the word Original prepended to it (see Figure 6.13). Figure 6.13. Parameter collection of Update command.  What this query is doing is using the where clause to determine if the original data has changed since the time it was read. If it has changed, the query updates nothing (because the where clause fails) and the DataAdapter will throw an error. If the data has not changed, the Update statement succeeds in changing the data. Take special note that this relies on the DataSet keeping track of the original values of all of the rows involved. Also, keep in mind that the Update method of the DataAdapter tries to execute all of the pending changes in the DataSet, including deletes and inserts . It's so easy to think only in terms of one row at a time. Normally, if one of the Update, Insert, or Delete statements fails, the DataAdapter stops processing the operation. The operations that completed successfully are permament but any operation that failed would obviously not be complete. You can adjust this behavior by setting the ContinueUpdateOnError property to true. If you do this, then the DataAdapter will merrily go on until it is out of updates. Let's look at what happens if we uncheck the Use Optimistic Concurrency option. Rerun the DataAdapter Configuration wizard. You can do this from the designer by right-clicking on the DataAdapter and selecting Configure DataAdapter. When you get to the Select query screen, click the Advanced Options button. Uncheck the two options as shown in Figure 6.14. Figure 6.14. Advanced options with concurrency and refresh unchecked.  After clicking OK, click through the rest of the wizard screens. Now let's find the UpdateCommand property again and open the query designer. The SQL statement is quite a bit simpler than before. UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, Address = @Address, City = @City, Region = @Region, PostalCode = @PostalCode, Country = @Country, Phone = @Phone, Fax = @Fax WHERE (CustomerID = @Original_CustomerID) This probably looks a little more like you would think it should. What are the consequences of this form of the statement? There is no checking to see if the data has changed. What this means is that the last one to update wins. I don't like either method of handling the concurrency problem. Without the optimistic concurrency check box selected, users could chaotically overwrite each other's updates. With it checked, the system would act as if the row no longer existed if another user updated it after the first user read it. This is not the case. The row still exists; it has merely been changed. The best process would be to reread the row, then compare each column value in the recently read row to each original value. If no column values have changed, then update the row. If any column values did change, then you could issue a message and allow the user to take appropriate action. Of course, you would do all of this inside a stored procedure and a transaction. This way you get to use pessimistic concurrency for the duration of the update. This prevents any other user from accessing the row being operated on until your update is complete. Since it is happening inside a stored procedure or a transaction, it should complete very fast, not keeping the row locked for too long. We'll demonstrate this technique later in the chapter.  |
