Dataset data concurrency Exception Handling

Summary: Ado. Net is a data-intensive application.ProgramIt provides a variety of technologies to simplify the process of establishing such programs. A dataset (Dataset) serves as a flag of the ADO. Net object model and as a copy of a micro, unconnected data source. Although using a dataset improves performance by reducing the high-cost access to the database server, it also brings the possibility that multiple users attempt to access the same data at the same time, this causes a data concurrency exception ). This article investigates the common causes behind data concurrency exceptions and introduces the technologies to solve these problems.
Upgrading the data access layer to ADO. Net has many advantages, one of which is to use internal dataset objects. A DataSet object is basically an unconnected, In-memory database copy. A DataSet object contains one or more data tables. Each data table generally corresponds to one table in the database. Datasets provide many benefits, but they also bring about some problems, especially problems related to data concurrency exceptions. I have built a simple Windows Forms customer service application that explains the potential defects of the problem. This article will introduce the application and demonstrate how to solve the data concurrency problem caused by it.

The customer service application example created in this article uses Visual Basic. and SQL Server 2000. the. NET Framework component is language-independent.. NET Framework components can be used in compatible languages. Similarly, because the DataSet object abstracts the data source, the actual execution of the data source is not important. No matter the underlying data source is SQL Server, a local XML file, or data retrieved from a service, data concurrency exceptions also occur.
Advantages and disadvantages of datasets

A dataset provides many benefits. For example, compared to a database, it enhances the integrity rules in the memory. A DataSet object can define and enhance the relationship between tables and column constraints to ensure that the business rules used have no defects in the database. Through database abstraction, you can create a singleCodeYou do not need to fill in the source data of the dataset. The underlying data sources may be SQL Server, Oracle, or even XML files. No matter what the underlying data source is, the Code uses the same method to interact with the dataset. This allows you to change the underlying data source without changing the code.

However, the biggest benefit of using a dataset is improved performance. Because the dataset is disconnected from the lower-level database, the code will be less called for the database, significantly improving the performance. You can add new rows to multiple data tables of a dataset to verify the validity of each row and the integrity of the reference. The data adapter connects a dataset to a lower-level database and can use a command to update the lower-level database. All new rows in each table are added using commands to ensure that all rows added to the database are valid.

Optimization of performance is costly. Because the DataSet object is disconnected from the lower-level database, there is often a chance that the data is not out of date. Because the dataset does not store activity data and only saves a snapshot of the activity data of the currently filled dataset, problems related to data concurrency will arise. Data concurrency issues occur when multiple users access the same data and any user can update the data without the information of other users. In this case, a user occasionally updates the data, but does not know that the data has changed, not what he sees in the program. Fortunately, the DataSet object has built-in support for capturing data concurrency issues, so the application can correctly respond.

Sample program

A virtual company uses this customer service application to create customer orders and update customers' personal information. Many customer sales representatives (CSR) use the application on the desktop. CSR obtains orders by phone and collects personal information and payment information from the customer. Customer records are stored in the database to increase the speed at which regular customers process orders. CSR then creates an order and adds the product item to the order. After the specified quantity and current price are collected, CSR click the place order button to insert the customer and order records to the database.

CSR also uses an application to execute a request sent to the company through an email or a slow email. These requests are evenly separated between CSR and sent to them every morning. CSR calls these requests. The system design needs to increase the request implementation speed, and all customers share the CSR. Each customer's request, whether sent by phone or email, is processed by different CSR, which increases the chance of data concurrency problems.
To improve performance, the application maintains a DataSet object filled with customer and order information in the memory. Because many employees use this application at the same time, there will be many non-connected snapshots of activity data, all of which are on the employee's workstation. All customer maintenance, order input, and order maintenance use a DataSet object named dsalldata. Figure 1 shows the code for building dsalldata. It is part of the global module, so all forms in the application can use it.

Const connstring = "Server = localhost; database = northwind; uid = sa; Pwd ="
Public conncustsvc as sqlclient. sqlconnection
Public dacustomer as sqlclient. sqldataadapter
Public cbcustomer as sqlclient. sqlcommandbuilder
Public daorders as sqlclient. sqldataadapter
Public cborders as sqlclient. sqlcommandbuilder
Public daorderdetail as sqlclient. sqldataadapter
Public cborderdetail as sqlclient. sqlcommandbuilder
Public dsalldata as Dataset
Public sub main ()
Conncustsvc = new sqlclient. sqlconnection (connstring)
Dacustomer = new sqlclient. sqldataadapter ("select * from customer", conncustsvc)
Cbcustomer = new sqlclient. sqlcommandbuilder (dacustomer)
Daorders = new sqlclient. sqldataadapter ("select * from orders", conncustsvc)
Cborders = new sqlclient. sqlcommandbuilder (daorders)
Daorderdetail = new sqlclient. sqldataadapter ("select * From orderdetail", conncustsvc)
Cborderdetail = new sqlclient. sqlcommandbuilder (daorderdetail)
Dsalldata = new dataset ()
Dacustomer. missingschemaaction = missingschemaaction. addwithkey
Dacustomer. Fill (dsalldata, "customer ")
Daorders. missingschemaaction = missingschemaaction. addwithkey
Daorders. Fill (dsalldata, "orders ")
Dsalldata. Tables ("orders"). Columns ("Total"). defaultvalue = 0
Daorderdetail. missingschemaaction = missingschemaaction. addwithkey
Daorderdetail. Fill (dsalldata, "orderdetail ")
Application. Run (New frmcustomermaintenance ())
End sub
Figure 1. Fill in the DataSet object

The dsalldata Code creates an empty DataSet object, three data adapters, and three command constructors ). Each data adapter performs a simple "select *" Operation on the appropriate table, and the command constructor fills the dataset with the required information to insert it) update and delete capabilities. The main program uses the data adapter object and the data in all three tables to fill dsalldata, and then uses the customer maintenance form to start the application.
Figure 2 shows the customer maintenance screen, which has a DataGrid object bound to the dsmers data table of dsalldata. This simple table allows CSR to edit any basic attributes of a customer. Because the table is bound to the MERs data table, any changes in the table are automatically stored in the data table. Dsalldata saves these values until the CSR click Save changes to explicitly notify the form to update the lower-level data source.

Figure 2. Allow editing of the Customer maintenance table

To enter the order, use the code in Figure 3 to create several new rows and add them to dsalldata. First, create an order record, and then create several records for each item of the Order in orderdetail of the data table. After all required rows are added to dsalldata, an appropriate data adapter's update method call uses a new row to update the underlying data source.

Private sub createorder ()
Dim Dr as datarow
Dr = dsalldata. Tables ("orders"). newrow
With Dr
. Item ("dateordered") = now
. Item ("customerid") = 1
. Item ("shiptoaddress") = "123 Main"
. Item ("shiptocity") = "Kansas City"
. Item ("shiptostate") = "Mo"
. Item ("shiptozip") = "12345"
End
Dsalldata. Tables ("orders"). Rows. Add (DR)
Addorderdetail (dr. Item ("ID"), 1, 1, 9.99)
Addorderdetail (dr. Item ("ID"), 2, 2, 4.99)
Daorders. Update (dsalldata. Tables ("orders "))
Daorderdetail. Update (dsalldata. Tables ("orderdetail "))
End sub
Private sub addorderdetail (byval orderid as integer ,_
Byval productid as integer, byval quantity as integer ,_
Byval price as Single)
Dim Dr as datarow
Dr = dsalldata. Tables ("orderdetail"). newrow
With Dr
. Item ("orderid") = orderid
. Item ("productid") = productid
. Item ("quantity") = Quantity
. Item ("price") = Price
End
Dsalldata. Tables ("orderdetail"). Rows. Add (DR)
End sub
Figure 3. New Order with detailed records

Because CSR uses an application and updates customer information at the same time, it seems that a CSR will see expired information at any time. To prevent this problem, the Application Designer decided that the data cache of dsalldata should be refreshed every 30 minutes to ensure that the CSR usually sees the correct information. Application
Program Design
Be careful to ensure that data is refreshed only during idle time so that it does not affect performance. The data set is refreshed a little longer than 30 minutes, depending on the CSR behavior.

Figure 4. Application Data Model

The data model of this application is very simple (figure 4 ). It is stored in SQL Server 2000 and contains only three tables, including the customer table, order table, and detail table of each order. It also defines appropriate primary keys and relationships to ensure the integrity of the reference. In addition, a trigger is defined on orderdetail to update the total column of the orders table. Insert, update, or delete an orderdetail record each time, call the trigger to calculate the last sales value of the order, and update the appropriate rows in the orders table. Figure 5 shows the code of the trg_updateordertotal trigger:

Create trigger trg_updateordertotal on [DBO]. [orderdetail]
For insert, update, delete
As
Declare @ orderid int
Select @ orderid = orderid from inserted
If @ orderid is null
Select @ orderid = orderid from deleted
Update orders
Set Total =
(
Select sum (price * quantity)
From orderdetail
Where orderid = @ orderid
)
Where id = @ orderid
Figure 5. Update the total column

The first data concurrency exception

This customer service application has been in use for several months without any problems, but suddenly generates an unhandled exception dbconcurrencyexception. This section explains the environment that causes this exception.

The first customer sales service engineer who uses the application, Joe, opens the application. This will initialize loading data into the dataset dsalldata and refresh the data every 30 minutes. Joe's inbox contains a pile of files, including change requests sent by customer fax, mail, or email. He started to process change requests, but he was often interrupted by phone orders.

In the meantime, Sally, the second customer sales service personnel, arrived at the office and opened her application instance. Sally's application instance also initializes data loading from SQL server, including updates made by Joe. Sally also received a request to change the phone number. The customer previously sent an email to change the address, but did not know his new phone number at that time. Now the record is updated. Sally uses the customer maintenance screen to update the customer's phone number. When Sally changes the phone number in the DataGrid, the new number is stored in dsalldata. When Sally confirms other customer information, she realizes that the original address change has not been processed, so she updates the information and click the Save Changes button to send the new data to the SQL Server database.

Joe is processing the original request of the same customer. When the customer maintenance screen is opened, the application reads information from the cached DataSet object. When Sally updates the customer address, Joe's application is not automatically synchronized with the database, so the customer maintenance Screen still displays the old address. Joe uses the new information provided by the email to correct the information displayed in the DataGrid and click Save changes. After this operation, an error message "concurrency fault occurs: The update command affects 0 records (Concurrency Violation: The updatecommand affected 0 records)", and the application crashes. When Joe opened the application again, he found that the address had been updated and thought that his change had been completed before the application crashed. The following is the problematic code line:

Private sub butsave_click (byval sender as system. Object ,_
Byval e as system. eventargs)
Dacustomer. Update (dsalldata. Tables ("customer "))
End sub
The actual exception is generated by the dbconcurrencyexception type, which is the result of a specific function established inside the data adapter object (see figure 6 ). The data adapter is designed to populate data with unconnected objects (such as Datasets) so that it can automatically check data for changes before performing updates. If the underlying data is changed, the data adapter will cause a dbconcurrencyexception instead of an update.

Figure 6. dbconcurrency exception

The integrity check is quite direct. It improves the performance of the data table object and enables it to maintain multiple data sets. 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 a column in the Data row is modified, the row is copied and marked as current. The row marked as original remains unchanged. All subsequent changes to the data only affect the current row. When an update method of the data adapter is executed for a data table (or multiple data tables in a data set), it repeats all the current rows to determine the update statement to be sent to the lower-level data source. As a supplement to the datarowversion attribute, a data row has a rowstate attribute used to identify the data status in the row. The possible values are unchanged, added, modified, deleted, and detached.

After determining which rows in the lower-layer data need to be updated, the data adapter dscustomer establishes the SQL statement required to update the SQL Server database. In Figure 1, I use a dataset and a command constructor object to create the required insert, update, and delete statements. The update statement created by the command constructor object uses the data row copy with the datarowversion value original to identify and update the appropriate rows in the database. This means that, instead of simply identifying the correct rows using the primary key value, the command constructor creates an SQL statement to find rows that match the original values stored in the dataset accurately. The following code is an example of an update statement used to update a customer's phone number:

Update customer set phone = @ p1
Where (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 )))
This update statement uses parameters instead of actual values, but you can see how each column in the row is checked.

After identifying accurate rows and the original values in the lower-level database, the data adapter can securely update rows. However, if a column in a row in the database changes after the data table is filled, the update statement fails because the database does not have a row that matches the standard in the where condition. The data adapter determines whether the update is successful. You only need to check the actual number of rows updated in the database. If no row is updated, the underlying data must be changed or deleted, resulting in a data concurrency exception. This explains the vague error message that Joe received when trying to update the customer's phone number: the actual error detected by the data adapter is not because the underlying data has changed, but not because the record has not been updated, it indicates that the underlying data must be changed.

Solution

Dbconcurrencyexception can be solved in two ways. The first is to ensure that it will never reproduce: I can delete the sqlcommandbuilder objects used by the code in Figure 1 and replace them with the sqlcommand objects of the updatecommand attribute of the data adapter object. I will create an SQL statement with the where condition in the commandtext attribute, and only ignore the primary key instead of all columns. This will eliminate all concurrency issues (assuming the primary key will not change ).

However, this technology brings about several problems. First, it is obvious that more code is needed, because I have to create a sqlcommand object for the insertcommand and deletecommand attributes of each data adapter. In addition, if the underlying database schema changes, these hard codes will bring new errors. If the sqlcommandbuilder object is used, the application determines the database Overview during running, accepts any changes, and establishes an SQL statement accordingly. This does not solve the concurrency problem, but completely avoids the problem, so that users can overwrite others' changes without knowing it.

Try
Dacustomer. Update (dsalldata. Tables ("customer "))
Catch dbcex as data. dbconcurrencyexception
Dim dresult as dialogresult
Dresult = MessageBox. Show (messagestring ,_
"Data concurrency exception occurred ",_
Messageboxbuttons. yesnocancel, messageboxicon. error ,_
Messageboxdefaultbutton. button1 ,_
Messageboxoptions. defaulttopics toponly)
If dresult = dialogresult. yes then
'Two options: fill the entire table or refresh the row
'Dacustomer. Fill (dsalldata. Tables ("customer "))
Updaterow ("customer", dbcex. Row. Item ("ID "))
Elseif dresult = dialogresult. No then
'Save the copy of the new row
Dim drcopy as datarow, drcurrent as datarow
Drcopy = dsalldata. Tables ("customer"). newrow ()
Dim DC as datacolumn
Drcurrent = dsalldata. Tables ("customer"). Rows. Find (dbcex. Row. Item ("ID "))
For each DC in drcurrent. Table. Columns
If DC. readonly = false then
Drcopy. Item (DC. columnname) = drcurrent. Item (DC. columnname)
Next
'Get the current value from the database
Updaterow ("customer", dbcex. Row. Item ("ID "))
'Now restore the user input value and save it again
For each DC in drcurrent. Table. Columns
If DC. readonly = false then
Drcurrent. Item (DC. columnname) = drcopy. Item (DC. columnname)
Next
Dacustomer. Update (dsalldata. Tables ("customer "))
End if
End try
Figure 7. Capture concurrency exceptions
Figure 7 shows a better solution to catch this exception. The try... Catch Block captures dbconcurrencyexception and displays a message window that identifies the error to the user (shown in Figure 8 ). In this way, I can identify a concurrency error and have two options: I can retrieve lower-level data and display it to users, and force them to make changes again, or I can simply overwrite the lower-layer data with the change specified by this user. These options are displayed in the message box (figure 8 ):

Figure 8. handling data concurrency exceptions

private sub updaterow (byval tablename as string, byval ID as integer)
'Get reference to a specific row
dim Dr as datarow = dsalldata. tables (tablename ). rows. find (ID)
'CREATE Command update to obtain new lower-level data
dim cmd as new sqlclient. sqlcommand ("select * from" &; amp; tablename "where id =" &; amp; id. tostring (), conncustsvc)
'Open the connection and create a data reader
conncustsvc. open ()
dim RDR as sqlclient. sqldatareader = cmd. executereader ()
RDR. read ()
'Copy new data from the database to the data row
dim DC as datacolumn
for each DC in dr. table. columns
If DC. readonly = false then _
dr. item (DC. columnname) = RDR. item (DC. columnname)
next
'Accept changes in the data row
dr. acceptchanges ()
conncustsvc. close ()
end sub
figure 9. updaterow program updates buffered data rows

If you decide to view the new lower-level changes and discard their changes, you just need to refresh the data stored in the customer data table. Because the DataGrid is bound to a data table, the table automatically displays new data. To refresh data, you have two options: the first is to use the data adapter dacustomer's fill method to easily fill the entire data table. Although this technology can be implemented, it is too costly because you only need to refresh a row. I created a program called updaterow, which only reads data from problematic rows (figure 9 ). When using the updaterow program, note that I have defined in the parameter set that the row to be found is a single column with an integer keyword. If the table has different data types or composite keys, you must reload updaterow to meet the requirements of a specific key. After the data row and/or data table are refreshed with the current data, the DataGrid displays a small error icon on the row that causes a concurrency exception (Figure 10 ).

Figure 10. error icon displayed in the DataGrid

Another option is to ignore the changes to the lower-level database and force the changes to take effect. There are multiple ways to implement this function. The first is to execute an SQL statement directly on the SQL Server database to synchronize data in the data table. Although this method can be implemented, it requires you to rewrite all SQL statements when the database is changed. The SQL statements written using this technology are hard-coded (hard-coded) of specific database versions, and the abstraction provided by data adapters and dataset objects is lost.

A better choice is to use the updaterow program I wrote earlier and allow the data adapter to process updates. The code in Figure 9 first creates a copy of a Data row containing new changes, and then calls the updaterow program to obtain new data from the database. The updaterow call process is necessary so that you do not receive concurrency exceptions when trying to execute updates again. The Code then iterates all columns in the Data row and updates the recently retrieved value with the value provided by the user. Finally, use the data adapter to update the underlying database.

These code solutions have some potential problems. First, by default, the update method of the data adapter fails when the first concurrency exception occurs, and the subsequent data rows are not processed. This may cause some data updates or several concurrency exceptions, each of which is handled by the user alone. The code in Figure 7 shows another potential exception when forcing the user to change to a lower-level database. There is a small chance that another user has changed the underlying database between calling the updaterow program and executing the update method of dacustomer. This will generate an unprocessed dbconcurrencyexception. One possible solution is to put the update method into try... catch Block, but the second catch code may be similar to the first one, and may generate its own concurrency exceptions, more try... catch blocks until infinity. A better solution is to put this code in an independent program that can be called when multiple concurrency exceptions occur.

Use SQL Server triggers

The customer service application developer for the company has handled the dbconcurrencyexception Code received when Joe updated the customer information, the application worked well again-until Sally tried to enter an order in the afternoon.
Sally receives an order call from a customer. The customer information is already in the database, so the basic information of the order that Sally uses includes the mailing address. Then she opened the orderdetails screen and added two items to the order. Each orderdetail record includes orderid, productid, quantity, and price. After filling out the order, Sally clicks the place order button to insert the order to the database. I simplified the code by adding a hard-coded value to the orderdetail record (3 ).

The order is successfully inserted into the database, but Sally receives a prompt that an item is out of stock and the order cannot be delivered within two weeks. The customer realized that he was not in the city when the parcel was delivered and asked if he could change the shipping address. Sally has changed the order information before using the Order Maintenance screen, so she is happy to help. She opened the appropriate screen and changed the shipping address for the customer. But when she clicked the Save Changes button, she saw an error dialog box similar to Joe, and the application crashed.

The key to understanding why dbconcurrencyexception occurs is the underlying database. Recall that in figure 5, I set a trigger on the orderdetail table. All inserts, updates, and deletes will call it. Trg_updateordertotal: calculate the number of each orderdetail row in order by multiplying the price to update the total price of the order. When Sally creates an order with two items, the application first inserts a new line into orders, and then inserts two new lines to orderdetail. After each orderdetail record is created, call the trigger to update the total column of the order record. However, this value is generated only in the database and is not passed to the application. In fact, the code in Figure 3 does not specify total because a default value has been specified in the Code in Figure 1. Then the default value 0 is passed to SQL Server as the new orders record, which 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 order price. When Sally tries to update the order record, the data adapter considers that the value of the total column has changed and generates a dbconcurrencyexception.

The trap here is that when Sally updated the order record in the past, it happened that since the last automatic data refresh, she did not update the new order just created. Within 30 minutes after each order is created, the dsalldata DataSet object is refreshed, which uses the correct total value to update the orders data table. Any updates made after automatic data updates (or application restart) will work as expected.

The solution to this problem is similar to the previous one: refresh the data you see. However, I am more proactive than waiting for a concurrency exception to occur. After creating an orderdetail record, I can automatically refresh each orders data row. I can change the code in Figure 3 to include an updaterow program call. after calling the daorderdetail update method, specify the lines in the orders. SQL Server will normally complete the trigger, but you cannot rely on it, so developers may add a time delay so that the trigger has enough time to complete.
Conclusion

the non-connection feature of ADO. Net dataset objects provides high performance, and also brings errors of data concurrency types to applications. Understanding how and why these errors occur will allow you to capture and handle these errors well and add another level of support to the application. This gives you more options to maintain the integrity of lower-layer data. With the further migration of applications to the Internet and Intranet, the number of users doubles, and the handling of concurrency problems is facing greater challenges. ADO. NET provides all necessary tools to solve this problem.