Solve the data concurrency exception when using the DataSet object

Released on: 1/14/2005 | updated on: 1/14/2005

David burgett

This document assumes that you are familiar with ADO. NET and Visual Basic. net.

Summary

Many technologies provided by ADO. Net can improve the performance of medium-sized dataset applications and make it easier to build such applications. Dataset is a flag of the ADO. Net object model and can be used as a small, disconnected copy of the data source. While using dataset to improve performance by reducing the huge overhead of the back-to-back database server, it also introduces the possibility of Concurrent Data exceptions caused by multiple users trying to change the same data at the same time. This article examines the common causes of data concurrency exceptions and proposes technologies for solving these exceptions.

Upgrading the data access layer to ADO. Net has many advantages. Most ADO. Net involves using internal dataset objects. Basically, the DataSet object is a copy of the database that has been disconnected in the memory. The DataSet object contains one or more data tables. Each data table usually corresponds to a table in the basic database. Dataset provides many advantages, but it also raises some challenges. In particular, you may encounter problems related to data concurrency exceptions. I have created a simple Windows Forms customer service application that demonstrates the potential defects of this particular issue. In this article, I will show you how to solve the data concurrency problem.

The customer service application example shown here uses Visual Basic.. NET and SQL Server 2000.. NET Framework has nothing to do with the language, so any.. NET Framework compatible language. Similarly, because the DataSet object abstracts the data source, the actual implementation of the data source is not important; whether the basic data source is an SQL Server, a local XML file, or derived from data retrieved through the service, data concurrency exceptions also occur.

Advantages and disadvantages of Dataset

Datasets provide many advantages. For example, you can obtain the ability to enforce integrity rules in the memory rather than in the database. A DataSet object can define and force the relationship between tables and column constraints to ensure that business rules can be applied without having to and from the database. Through database abstraction, you can write a separate set of code to access the DataSet object, regardless of the data source that fills the dataset. Can the basic data source be SQL server? Oracle, or even XML files. Regardless of the source of the basic data, your code interacts with dataset in the same way. In this way, you can change the basic data source without changing the code.

However, the most important advantage of using dataset is to improve performance. Because dataset is disconnected from the basic database, the Code does not need to call the database frequently, which significantly improves performance. You can add many new rows to multiple data tables in a dataset and check the validity and integrity of each row when creating a dataset. The dataadapter object connects the dataset to the basic database, and then you can use a command to update the basic data. All new rows in each table are added using this command, which ensures the validity of all rows added to the database.

Like most performance optimizations, such optimizations also have a price. Because the DataSet object is disconnected from the basic database, the data may be out of date. Because dataset cannot maintain real-time data, but fills in the snapshot of real-time data during dataset, problems related to data concurrency may occur. When multiple users access the same data, if any user can update the data without the knowledge of other users, data concurrency may occur. Then, a user may accidentally update the data without knowing that the data he is changing is not the same as the data he is seeing in the application. Fortunately, when a data concurrency issue occurs, the DataSet object has built-in support for capturing data concurrency issues so that your application can respond accordingly.

Sample Application

A fictitious company uses the customer service application I created to create orders for their customers and update their personal information. Many customer sales representatives (CSR) use the application from their desktops. These CSR receive orders by phone and collect personal and payment information from the customer. Customer records are stored in the database to speed up the process of returning customer orders. Then, CSR creates an order and adds a separate product for each order, specifying the quantity and current price. Once all the information is collected, the CSR will press the "place order" button to insert the customer and order records into the actual database.

CSR also uses this application to complete the request sent to the company via email or normal mail. These requests are evenly distributed to each CSR and forwarded to the CSR every morning. CSR completes these requests by phone. The system is designed to speed up request fulfillment, but the cost is to allow CSR to share with all customers. Each request sent by phone or email may be processed by different CSR, which increases the possibility of data concurrency problems.

To improve performance, the application retains a DataSet object in the memory that contains the customer and the order. Because many employees use the application at the same time, there will be many disconnected snapshots of real-time data, each employee's workstation has such a snapshot. All customer maintenance, order entry, and order maintenance use this DataSet object named dsalldata.Figure 1Shows the code used to build dsalldata, and this Code is part of the global module, so it can be used for all forms in the application.

The code for building dsalldata creates an empty DataSet object, three dataadapters, and three commandbuilder. Each dataadapter performs a simple select * operation from an appropriate table, while commandbuilder fills in other necessary information so that dataset has the complete insert, update, and delete functions. The main routine uses the dataadapter object to fill dsalldata with data from three tables, and then uses the "Customer maintenance" form to start the application.

Figure 2The customer maintenance screen is displayed, which contains a DataGrid Control bound to the MERs data table in dsalldata. This simple grid allows the CSR to edit any basic attributes of the customer. Because the grid is bound to the MERs able, any changes made to the values in the grid are automatically stored in the datatable. Dsalldata stores these values until the CSR explicitly notifies the form to update the basic data source by clicking the Save Changes button.

Figure 2 allow users to edit the maintenance Mesh

For order entries, useFigure 3To create some new rows and add them to dsalldata. First, create an order record, and then create a record for each item of the order in the orderdetail data table. After adding all required rows to dsalldata, you only need to call an appropriate dataadapter update method to update the basic data source with these new rows.

Because many CSR programs use this application and update customer information at the same time, the information displayed by the CSR at any specific time may be outdated. During this plan, the application architect decided that the cached data in dsalldata should be refreshed every 30 minutes to ensure that the information normally viewed by CSR is correct. The application has been carefully designed to ensure that data refresh is performed only in idle time, without affecting performance. Therefore, the interval between dataset refreshes is slightly more than 30 minutes, depending on the CSR activity.

Figure 4 Application Model

The data model of this application is very simple (seeFigure 4). It is stored in SQL Server 2000 and consists of only three tables, one for the customer, one for the order, and one for the details of each order. An appropriate primary key and link have been defined to ensure the integrity of the reference. In addition, a trigger is defined in the orderdetail table to update the total column in the orders table. Each time an orderdetail record is inserted, updated, or deleted, the trigger is triggered to calculate the total sales of the order and update the appropriate rows in the orders table. InFigure 5The updateordertotal code of the trigger is displayed.

The first data concurrency exception

The customer service application was running normally in production for a few months, and then shut down due to an unhandled dbconcurrencyexception. In this section, I will explain the situations that lead to this new exception.

The first customer sales representative Joe started the application. This forces the initial data loading of dsalldata dataset and starts the data refresh cycle every 30 minutes. Joe's inbox has a bunch of written work to do, including modifying requests sent by customers by fax, mail, or email. He started to complete these change requests, but he was often interrupted by phone orders.

At the same time, the second customer sales representative Sally arrived at the office and started her own application instance. Sally's application instance loads initial data from SQL server, including any updates that Joe has made so far. Sally received a call from a customer requesting to change the phone number. The customer previously requested to change the address by email, but did not know the new phone number yet. Now they want to update their records. Sally uses the customer maintenance screen to update the customer's phone number. When Sally changes the phone number in the Data mesh, the new phone number is stored in dsalldata. Sally confirmed the customer's other information and found that the original address change request was not processed, so she updated the information and then clicked save changes to send the new data to the SQL Server database.

Joe diligently processes requests sent by email in the compartment next to him, and then he encounters an original change request from the same customer. When the customer maintenance screen is turned on, the application works as designed to extract information from the cached DataSet object. Joe's application is not automatically synchronized with the database. Because Sally updates the customer address, the customer maintenance Screen still displays the old address. Joe corrected the information displayed in the DataGrid with the new information provided in the email, and then clicked the Save Changes button. However, when Joe does this, it receives an error message, namely "Concurrency Violation: The updatecommand affected 0 records", and then the application crashes. When Joe restarts the application, he finds that the address has been updated and believes that his change must have been completed before the application crashes. The following are related code lines:

Private Sub butSave_Click (ByVal sender As System.Object, _                               ByVal e As System.EventArgs)        daCustomer.Update(dsAllData.Tables("Customer"))End Sub

The actual exception created is of the dbconcurrencyexception type and is the result of the specific built-in functions of the dataadapter object (seeFigure 6). Dataadapter is designed to "pull" data into disconnected objects (such as dataset) so that it can automatically and carefully check whether the data is changed before performing updates. If the basic data has been changed, dataadapter will cause dbconcurrencyexception without updating the request.

Figure 6 dbconcurrency exception

The implementation of this integrity check is quite simple, and you can use the datatable object to save multiple groups of data. When the data is first loaded into the data table, the datarowversion attribute of all data rows in the data table is set to original. When you modify a column value in a data row, a copy of datarow is created and marked as current. The data row marked as original still exists in its unchanged form. All subsequent changes to the data only affect the current data row. When you execute the dataadapter update method on a data table (or multiple data tables in the dataset), It cyclically accesses all current rows to determine the update statement that should be applied to the basic data source. In addition to the datarowversion attribute, a data row also has the rowstate attribute, which identifies the status of data in the row. Possible values include unchanged, added, modified, deleted, and detached.

Now that you have determined which rows in the basic data need to be updated, dscustomer dataadapter builds the SQL statements required to update the SQL Server database. To reviewFigure 1In, I use the commandbuilder object and dataset to construct the necessary insert, update, and delete statements. The update statement created by the commandbuilder object uses the value stored in the data row copy with the datarowversion value original to identify and update the corresponding row of the database. That is, commandbuilder creates an SQL statement to find rows that exactly match all the original values stored in the dataset, rather than simply using the primary key value to identify the correct rows. The following code is an example of an update statement built to update a customer's phone number:

UPDATE Customer SET Phone = @p1WHERE ((ID = @p2) AND ((FirstName IS NULL AND @p3 IS NULL)       OR (FirstName = @p4))AND ((LastName IS NULL AND @p5 IS NULL) OR (LastName = @p6)) AND ((Address IS NULL AND @p7 IS NULL) OR (Address = @p8)) AND ((Address2 IS NULL AND @p9 IS NULL) OR (Address2 = @p10)) AND ((City IS NULL AND @p11 IS NULL) OR (City = @p12)) AND ((State IS NULL AND @p13 IS NULL) OR (State = @p14)) AND ((Zip IS NULL AND @p15 IS NULL) OR (Zip = @p16)) AND ((Phone IS NULL AND @p17 IS NULL) OR (Phone = @p18)))

The update statement uses parameters rather than actual values, but you can see how the values of each column in the row are checked.

Now, the exact row and the original value in the basic database have been identified, and dataadapter can safely update the row. However, if any column value changes in the row of the database after the datatable is filled, the update statement fails because the database does not have rows that match the conditions in the WHERE clause. By simply checking the actual number of updated rows in the database, dataadapter can easily determine whether the update is successful. If no row is updated, the basic data must have been changed or deleted, and a data concurrency exception occurs. This explains a vague error message that Joe received when trying to update the customer's phone number. The correct error detected by dataadapter is not the basic data that has been changed, but is not recorded and updated, this indicates that the basic data must have been changed.

Solution

Here are several methods to handle dbconcurrencyexception. The first is to make sure it never appears. Can be eliminatedFigure 1The sqlcommandbuilder object in is replaced by the sqlcommand object customized for the updatecommand attribute of the dataadapter object. I will use a where clause that filters only the primary key rather than all available columns to create an SQL statement for the commandtext attribute. This eliminates all concurrency issues (assuming that the primary key is unchangeable ).

However, this technology will introduce several issues. First, this requires a considerable amount of other code, because I will have to create a sqlcommand object for the insertcommand and deletecommand attributes in each dataadapter. In addition, if the basic database architecture is changed, these hardcoded SQL statements introduce new errors. With the sqlcommandbuilder object, the application can determine the database architecture at runtime, accept any changes that may have been introduced, and create SQL statements accordingly. This cannot solve the concurrency problem, but in short it can avoid this problem, so that users can rewrite others' changes without knowledge.

A better solution is to capture exceptions, suchFigure 7. Try... Catch Block capture dbconcurrencyexception and display a message box for users to identify exceptions and provide them with an option, suchFigure 8. At this time, I have determined that the concurrency error has appeared and there are two options: you can retrieve the new basic data and display it to the user, forcing them to make changes again. Alternatively, you can simply rewrite the new basic data with the user-specified changes. These areFigure 8The options shown in the message box.

Figure 8 handling data concurrency exceptions

If you decide to view the new basic change and discard the current change, simply refresh the data stored in the customer data table. Because the data grid is bound to the data table, the grid automatically displays new data. To refresh data, you have two options. First, use the fill method of the dacustomer dataadapter to enter all the content in the data table again. If this technology works, the problem is completely solved, because you are only interested in refreshing data in a single data row. I have created a routine named updaterow, which extracts data only for a single data row. (SeeFigure 9). When using the updaterow routine, you must note that the data rows that I have defined in the parameter set have a single integer key column. If the table has a different data type key or a combination key, you need to reload updaterow to meet specific key requirements. After refreshing the data rows and/or data tables with the current data, the DataGrid displays a small error icon next to the row that causes a concurrency exception (seeFigure 10).

Figure 10 error icon displayed on the Data Grid

Another option for users is to discard the changes made to the basic database and force their changes to take effect. There are several implementation methods. The first method is to execute SQL statements directly for the SQL Server database to synchronize data with the data in the data table. Although this method runs well, it may be faulty because it will cause you to rewrite the SQL statement for every database change. The SQL compiled for this technology may also require hard coding of the specific database version in use, thereby losing the abstract functions provided by dataadapter and dataset objects.

A better choice is to use the previously compiled updaterow routine and allow the dataadapter to process updates.Figure 9The code in first creates a copy of the data row with new changes, and then calls the updaterow routine to obtain new data from the database. When you try to execute the update again, it is necessary to call the updaterow process, so that you will not receive another concurrency exception. The Code then cyclically accesses all columns in the Data row to replace the newly retrieved value with the user-supplied value. Finally, dataadapter is used to update the basic database with user changes.

These two code solutions have some potential problems. First, by default, the update method of dataadapter will fail when a concurrency exception occurs for the first time and will not continue to process other data rows. This may cause some updates to the data or several concurrent exceptions. You need to process them one by one. Another potential exception may occur inFigure 7The code shown in may also cause user changes to be added to the branch of the basic database. Between calling the updaterow routine and executing the update method on the dacustomer, another user is unlikely to change the basic data. This will generate an unprocessed dbconcurrencyexception. One possible alternative is in try... put an update method call in the catch block, but your second catch code will be similar to the first one, and it has the potential to generate your own concurrency exception, this requires more try... catch Block. A more elegant solution is to break this code into an independent routine, which can be called recursively in case of multiple concurrent exceptions.

Use SQL Server triggers

The developer of my fictitious company's customer service application has now corrected the code to handle the dbconcurrencyexception that Joe received when updating a customer, and everything went fine again (that is, until that afternoon Sally tried to enter an order ).

Sally received a call from a customer and he wanted to place an order. The customer already exists in the database, so Sally extracts the basic information of the order, including the delivery address. Then she opened the orderdetails screen and added two items to the order. Each orderdetail record includes orderid, productid, quantity, and price. If the order is correct, Sally click the place order button to insert the order to the database. In ExampleFigure 3In the code shown in, I have added the hard-coded VALUE OF THE orderdetail record to simplify the code.

An underground order has been successfully placed and inserted into the database, but Sally receives a notification that one of these items is out of stock, so it cannot be delivered within two weeks. The customer knew that the package would be out of town when it could be delivered, so he asked if he could change the delivery address of the order. Previously, Sally had changed the order information through the order maintenance screen, so she replied that she was willing to help. She opened the appropriate screen and changed the shipping address for the customer. However, when she clicked the Save Changes button, she saw an error dialog box similar to Joe, and then the application crashed.

The key to understanding why dbconcurrencyexception is caused is the basic database. ReviewFigure 5, I placed a trigger in the orderdetail table that triggers all insert, update, and delete statements. Calculate the total number of quantity multiplied by price by each orderdetail row bound to this order, and trigger updateordertotal to update the total cost of the order. When Sally creates an order with two projects, the application first inserts a new line in orders, and then inserts two new lines in orderdetail. After each orderdetail record is created, the trigger is triggered to update the total column of the order record. However, this value is only generated in the database and will not be automatically passed to the application. In fact,Figure 3The code in does not specify a total becauseFigure 1The default value is specified .. Then, the default value 0 and the new orders record are passed to SQL Server. The new orders record is correct when the order is created. The database then updates the total column, but the data table in the application still uses 0 as the total number of orders. When Sally tries to update the orders record, dataadapter identifies that the total column has been changed and then triggers dbconcurrencyexception.

The problem is that Sally updated the order record in the past, but it happened that the last automatic data refresh had been performed, so she did not update the created order. Within 30 minutes of each order creation, the dsalldata DataSet object is refreshed, which will update the orders data table with the correct total number. Any order update made after automatic data refreshing (or application restart) will be performed as planned.

The solution to this problem is similar to the previous one: refresh the data that the user has seen. However, I can be more proactive here. Instead of waiting for a concurrency exception, I will automatically refresh the data lines of each new orders after creating the orderdetail record. ModifiableFigure 3To include the call to the updaterow routine for the specified row in orders after calling the update method of daorderdetail. Generally, SQL Server completes the trigger almost at the same time, but you cannot rely on it. Therefore, a cautious developer may add a latency so that the trigger has enough time to complete.

Summary

The disconnected nature of the new ADO. Net DataSet object provides some important performance benefits, while at the same time paying the cost of introducing new data concurrency error types in applications. By understanding how and why these errors occur, You can elegantly capture and process them, but this also adds additional level of support to the application. This gives users more choices in maintaining the integrity of the basic database. When application development moves to the universal world of Internet and Intranet applications with hundreds or thousands of users simultaneously, processing concurrency will become an increasing challenge. ADO. NET provides you with all the tools necessary to overcome this challenge.

For more information, see:

Data points: datarelations In ADO. net

Advanced basics: Viewing the values of a dataset in a debug window

Dataset updates in Visual Studio. NET

For background information, see:

. Net e-Business ArchitectureWritten by David burgett, Matthew Baute, John Pickett, and Eric Brown (SAMs, 2001)

Data points: establishing relationships between rowsets with ADO. net

David burgettHe is a senior technical architect at G. A. Sullivan in Kansas City and a co-owner/developer of reprintorders.com. Recently, he has made three books about Visual Studio.Net eBusiness Architecture(SAMs, 2001). David often gives speeches at technical meetings. You can useDavid@burgett.comContact him.

Go to the original English page