There are many benefits to upgrading the data access layer to ado.net, one of which is the use of internal dataset objects. The DataSet object is essentially a copy of a disconnected, in-memory database. A DataSet object contains one or more data tables (DataTable), and each data table typically corresponds to a table in the database. Datasets offer a number of benefits, but they also pose problems, especially if they are associated with data concurrency anomalies. I built a simple Windows Forms customer Service application that explains the potential pitfalls of the problem. In this article I'll introduce the application and demonstrate how to resolve the data concurrency problems it causes.
The Customer Service Application example established in this article is to use Visual Basic. NET and SQL Server 2000, but because of Microsoft. NET Framework components are language-independent (language-agnostic), so any with. NET Framework components can be used in languages that are compatible. Similarly, because the DataSet object abstracts the data source, the actual execution of the data source is not important; Data concurrency exceptions also occur regardless of whether the underlying data source is SQL Server, a local XML file, or data retrieved from a service.
the pros and cons of data sets
Datasets provide a number of benefits, such as hardening the integrity rules in memory, as compared to the database hierarchy. DataSet objects can define and enforce relationships between tables and columns, ensuring that the business rules used are not defective to the database. With database abstraction, you can create a single code collection to access the DataSet object without having to consider the source data that fills the dataset. The underlying data source might be SQL Server, Oracle, or even an XML file. The code uses the same method to interact with the dataset, regardless of the underlying data source. This allows you to change the underlying data source without changing the code.
But the biggest benefit of using datasets is improved performance. Because the dataset is disconnected from the underlying database, the code will make fewer calls to the database, dramatically improving performance. You can add new rows to multiple data tables in a dataset, validate each row for validity, and referential integrity. The data adapter (DataAdapter) connects the dataset to the underlying database and can update the underlying database with a single command. All new rows in each table are joined using the command to ensure that all rows added to the database are valid.
The optimization of performance is at a cost. Because the DataSet object is disconnected from the underlying database, there is often a chance that the data will not be extended (out of date). Because the dataset does not hold the active data, only a snapshot of the active data that was populated at that time, and problems associated with data concurrency arise. Data concurrency issues occur when multiple users access the same data and any user has no other user's information to update the data. This occurs when a user accidentally updates the data without knowing that the data has changed, not what he sees in the program. Fortunately, the DataSet object has built-in (built-in) support to capture data concurrency issues, so the application responds correctly.
Sample Programs
A virtual company uses the customer service application to create customer orders and update the customer's personal information. There are many customer sales representatives (CSRs) who use the application on the desktop. CSR uses the phone to get orders and collect personal information and payment information from customers. Customer records are kept in the database to increase the speed at which repeat customers process orders, the CSR then creates an order and adds the product item to the specified quantity and current price, after all the information is collected, the CSR clicks the Place Order button to insert the customer and the orders record into the database.
CSRs also use applications to execute requests sent to the company through electronic or slow mail. These requests are evenly split between CSR, sent to them every morning, and CSR executes those requests over the phone. System design to improve the speed of request implementation, all customers are shared among CSR. Each request of a customer, whether by telephone or mail, is handled by different CSRs, increasing the chance of data concurrency problems.
To improve performance, the application maintains a DataSet object that is populated with customer and order information in memory. Because many employees use the application at the same time, there are a number of disconnected snapshots of the active data that are on the employee's workstation. All customer maintenance, order entry, and order maintenance use a DataSet object named Dsalldata. Figure 1 is the code that establishes the Dsalldata, which 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 |
The Dsalldata code builds an empty DataSet object, three data adapters (DataAdapter), and three command constructors (CommandBuilder). Each data adapter performs a simple "SELECT *" operation on the appropriate table, and the command constructor fills the dataset with the remaining information needed to make it capable of inserting (insert), Updating (update), and deleting (delete). The main program populates dsalldata using data adapter objects and data from all three tables, and then starts the application using the Customer maintenance form.
Figure 2 shows the Customer Maintenance screen, which has a DataGrid object that is bound to the Dsalldata customers datasheet. This simple table allows the CSR to edit any basic attributes of the customer. Because the table is bound to a customers datasheet, any changes in the table are automatically stored in the datasheet. Dsalldata will save these values until the CSR clicks the Save Changes button to explicitly tell the form to update the underlying data source.
To enter the order, use the code in Figure 3 to create several new lines and add them to the dsalldata. Create an order record first, and then create several records for each item in the OrderDetail in datasheet. When all the required rows are added to the Dsalldata, an update method call to an appropriate data adapter updates the underlying data source with the new row.
Private Sub Createorder () Private Sub Addorderdetail (ByVal OrderID as Integer, _ Dim Dr as DataRow |
Because CSRS use both applications and update customer information, it seems like any time a CSR sees an out-of-date message. To prevent this, the application's designer decided that the Dsalldata data cache should be refreshed every 30 minutes to ensure that the CSR usually sees the correct information. The application is designed carefully to ensure that data refreshes are done only during idle hours, so that it does not affect performance. Data set refreshes are therefore longer than 30 minutes, depending on the behavior of the CSR.
The data model for the application is simple (Figure 4). It uses SQL Server 2000 storage, contains only three tables, a Customer table, an order table, a detail table for each order, and defines the appropriate primary keys and relationships to ensure referential integrity. In addition, a trigger is defined on the OrderDetail to update the total column of the Orders table. Each time you insert, update, or delete a OrderDetail record, invoke the trigger to calculate the final sales value for the order and update the appropriate rows for the Orders table. Figure 5 is the code for 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 |
First Data concurrency exception
The customer service application has been in use for months without any problems, but has suddenly produced an unhandled exception dbconcurrencyexception. In this paragraph I will explain the environment that caused the exception.
The first customer sales service Joe, who uses the application, opens the application. This initializes the data to be loaded into the dataset Dsalldata and refreshes the data per 30-minute cycle. Joe's Inbox contains a bunch of files, including customer fax, mail, or a change request sent by e-mail. He began to process change requests, but was often interrupted by phone orders.
Meanwhile, the second customer sales service Sally arrives at the office and opens her application instance. Sally's Application instance also initializes the download of data from SQL Server, including Joe's updates. Sally also received a request from a customer to change her phone number. The customer had previously emailed a change to the address, but at that time he did not know his new phone number and would now like to update the record. Sally updates the customer's phone number using the Customer Maintenance screen. When Sally changes the phone number in the DataGrid, the new number is stored in the dsalldata. When Sally confirms other customer information, she realizes that the original address has not been changed, so she updates the information and clicks the Save Changes button to send the new data to the SQL Server database.
Joe is working on the original request from the same customer. When he opens the Customer Maintenance screen, the application reads the information from the cached DataSet object. Because Sally updates the customer's address, Joe's application does not automatically synchronize with the database, so his customer maintenance screen still displays the old address. Joe uses the new information provided by e-mail to correct the information displayed in the DataGrid and clicks the Save Changes button. An error message "Concurrency failure: the update command affected 0 records (concurrency violation:the UpdateCommand affected 0 Records)" occurred after this operation, and the application crashed. When Joe opened the application again, he found that the address had been updated and that his changes had been completed before the application crashed. The following is the line of code for the problem:
| 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 the specific functionality established within the data adapter object (see Figure 6). The data adapter is designed to populate a disconnected object, such as a dataset, so that it automatically checks the data for changes before performing an update. If the underlying data is changed, the data adapter throws a DBConcurrencyException exception instead of performing the update.
Performing an integrity check is fairly straightforward, which improves the performance of the data Table object and enables it to maintain multiple data sets. When data is first loaded into the datasheet, the DataRowVersion property of all data rows (DataRow) in the datasheet is set to the original (Original). When a column in a data row is modified, the row is copied and marked as current, and the row marked as original remains unchanged. All subsequent changes to the data affect only the current row. When you perform an update method for a data adapter for a data table (or multiple data tables in a dataset), it repeats all the current rows to determine which update statements to send to the underlying data source. As a supplement to the DataRowVersion property, the data row has a RowState property that identifies the state of the data in the row. Its possible values are unchanged, Added, Modified, deleted, and detached.
After deciding which rows in the underlying data need to be updated, the data adapter Dscustomer establishes the SQL statement needed to update the SQL Server database. In Figure 1 I use datasets and command Builder objects to create the required insert, UPDATE, and DELETE statements. The UPDATE statement established by the Command builder object uses a copy of the data row with the DataRowVersion value of original to identify and update the appropriate rows in the database. This means that, as a substitute for simply identifying the correct row using a primary key value, the command builder establishes an SQL statement to find the rows that exactly match the original values stored in the dataset. The following code is an example of an UPDATE statement that was established to update the customer 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 with @p5 is null) or (LastName = @p6)) and ((address are null and @p7 is null) or (address = @p8)) and ((Address2 is null with @p9 is null) or (ADDRESS2 = @p10)) and (city is null and @p11 are null) OR (city = @p12)) and (State is null and @p13 are null) OR (state = @p14)) and (Zip is null with @p15 is null) or (zip = @p16)) and (phone is null with @p17 is null) or (phone = @p18)) |
The UPDATE statement uses parameters instead of actual values, but you can see how each column in the row is checked.
After identifying the original values in the exact row and underlying databases, the data adapter can safely update the row. However, if a column in a row in the database has changed since the datasheet was populated, the UPDATE statement will fail because there are no rows in the database that match the criteria in the Where condition. The data adapter determines whether update success is simple, simply checking the actual number of rows updated in the database. If no rows are updated, the underlying data must be changed or deleted, resulting in a data concurrency anomaly. This explains what Joe was getting when he tried to update the customer's phone number: The actual error that the data adapter was checking was not that the underlying data had changed, but that no records were being updated, indicating that the underlying data must have been changed.
Solving method
There are two ways to solve the problem of dbconcurrencyexception. The first is to make sure it never recurs: I can remove the SqlCommandBuilder object used by the code in Figure 1 and replace it with the SqlCommand object of the UpdateCommand property of the data adapter object. I'll set up an SQL statement with a where condition in the CommandText property, only the primary key instead of all the columns. This excludes all concurrency issues (assuming that the primary key does not change).
But this technology has brought a few problems. First, it's obvious that you need more code, because I have to create SqlCommand objects for each data adapter's InsertCommand and DeleteCommand properties. Also, if the underlying database profile (schema) changes, these hard encodings will bring new errors. If you use the SqlCommandBuilder object, the application determines the database profile at run time, accepts any changes, and builds the SQL statement accordingly. This is not to solve the concurrency problem, but to completely avoid the problem, so that users unknowingly overwrite other people's changes.
Try ' Get the current value from the database ' Now restore the value entered by the user and save it again Dacustomer.update (Dsalldata.tables ("Customer")) |
Try ... The catch block captures the dbconcurrencyexception and shows the User a message window identifying the error, giving the user a choice (shown in Figure 8). So I identify a concurrency error that has occurred and have two choices: I can retrieve the underlying data and display it to the user, force them to make changes again, or I can simply overwrite the underlying data with the user-specified changes.
Private Sub Updaterow (ByVal tablename as String, ByVal ID as Integer) ' Create command update to get new downlevel data ' Open the connection and set up a data reader (DataReader) ' Copy new data from the database to the data row ' Accept changes in the data row |
If the user decides to view the new downlevel changes and discard their changes, you simply need to refresh the data stored in the customer datasheet. Because the DataGrid is bound to a datasheet, the table automatically displays the new data. To refresh your data, you have two choices: the first is to simply populate the entire datasheet with the data adapter Dacustomer Fill method. Although this technique can be implemented, it costs too much because you simply need to refresh the line. I set up a program called Updaterow, which reads only the data in the problematic rows (Figure 9). When using the Updaterow program, be aware that I have defined in the parameter collection that the data row being found is a single, integer-key column, and that if the table has different data types or composite keys, you must overload the Updaterow to handle the requirements of a particular key. After the data row and/or datasheet is refreshed with the current data, the DataGrid displays a small error icon on the row that caused the concurrency exception.
Another option for the user is to ignore changes to the underlying database and force his changes to take effect. There are several ways to implement this functionality. The first is to synchronize data in a datasheet by executing an SQL statement directly on the SQL Server database. Although this approach can be implemented, it requires you to override all SQL statements when the database is changed. SQL written using this technique is hard coded (hard-coded) for the specific version of the database used, and the abstraction provided by the data adapter and dataset object is lost.
A better option would be to use the Updaterow program I wrote earlier and allow the data adapter to handle the update. The code in Figure 9 first establishes a copy of the data row containing the new changes, and then calls the Updaterow program to get the new data from the database. Calling the Updaterow procedure is necessary so that you do not receive a concurrency exception when you try to perform an update again. The code then iterates through all the columns in the data row, updating the recently retrieved value with the user-supplied value. Finally, use the data adapter to update the underlying database.
There are some potential problems with these code solutions. First, by default, the Update method of the data adapter fails with the first concurrency exception and does not process subsequent rows of data. This can result in a partial update of the data or several concurrent exceptions, each handled by the user alone. The code in Figure 7 shows another potential exception when forcing a user's changes to the underlying database. There's a very small opportunity. Another user changed the underlying database between calling the Updaterow program and executing the Dacustomer Update method. This will result in an unhandled dbconcurrencyexception. One possible workaround is to put the update method into a try ... Catch block, but the second catch code may resemble the first, and potentially generate its own concurrency exception, requires more Try ... Catch block, until infinity. A better solution is to put this code into a separate program that can be invoked when multiple concurrent exceptions occur.
using SQL Server triggers
The person who developed the customer service application for the company has handled the dbconcurrencyexception exception code that Joe received when he updated the customer's information, and the application worked well-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 Sally uses the basic information of the order including the mailing address. She then opens the OrderDetails screen and adds two items to the order. Each OrderDetail record includes OrderID, ProductID, Quantity, and price. Once the order has been completed, Sally clicks the Place order button to insert it into the database. I simplified the code by adding hard-coded values for the OrderDetail record, as shown in Figure 3.
The order was successfully inserted and the database, but Sally received a prompt to show that one item is out of stock and the order cannot be shipped within two weeks. The customer realized that he was not in town when the parcel was delivered and asked if he could change the shipping address. Sally had changed the order information before using the maintenance screen, so she said she was willing to help. She opens the appropriate screen and changes the shipping address for the customer. But when she clicked on the Save Changes button, she saw an error dialog box similar to Joe's, and the application crashed.
The key to understanding why generating dbconcurrencyexception is the underlying database. Recall in Figure 5 that I set a trigger on the OrderDetail table, and all inserts, updates, and deletes will call it. Trg_updateordertotal multiplies the total price of the price update order by calculating the number of each orderdetail line in the ordering. When Sally establishes an order with two items, the application first inserts a new row into the orders, and then inserts two new rows into the OrderDetail. After each OrderDetail record is established, the invocation trigger updates 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. The default value of 0 is then passed into SQL Server with the new orders record, which is correct when the order is established. The database then updates the total column, but the data table in the application still uses 0 for the order price. When Sally tries to update the order record, the data adapter thinks the value of the total column has changed and produced a DBConcurrencyException exception.
The trap is in the past. When Sally has updated the order record, it happens that she hasn't updated the new orders she just created since the last time the data was automatically refreshed. Within 30 minutes of establishing each order, the Dsalldata DataSet object is refreshed, and it updates the orders datasheet with the correct aggregate value. Any updates made after the data is automatically updated (or the application restarts) will work as expected.
The solution to this problem is similar to the previous one: refreshing the data that the user sees. But I can be more proactive, not waiting for concurrency anomalies to happen. I can automatically refresh each of the orders data lines after the OrderDetail record is established. I can change the code in Figure 3 so that it contains a call to a Updaterow program that specifies the line in orders after calling the Daorderdetail Update method. SQL Server will complete the trigger normally, but you cannot rely on it, so the developer may add a time delay so that the trigger has enough time to complete.
Conclusion
Ado. NET DataSet object provides a high performance and a data concurrency type error for the application. Understanding how and why these errors occur will allow you to capture and handle this error well, adding another level of support to your application. This gives the user more choice to maintain the integrity of the underlying data. As applications migrate further to the Internet and intranet, users multiply and the concurrency problem is confronted with greater challenges. Ado.net provides all the necessary tools to solve the problem.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
