Data Concurrency In Ado.net

Data Concurrency in ADO.NET When multiple users attempt to modify data at the same time, controls need to be established in order to prevent one user's modifications from adversely affecting modifications from simultaneous users. The system of handling what happens in this situation is called concurrency control.

Types of Concurrency Control In general, there are three common ways to manage concurrency in a database:  Pessimistic concurrency control - a row is unavailable to users from the time the record is fetched until it is updated in the database.  Optimistic concurrency control - a row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed results in a concurrency violation.  "Last in wins" - a row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records. Pessimistic Concurrency Pessimistic concurrency is typically used for two reasons. First, in some situations there is high contention for the same records. The cost of placing locks on the data is less than the cost of rolling back changes when concurrency conflicts occur. Pessimistic concurrency is also useful for situations where it is detrimental for the record to change during the course of a transaction. A good example is an inventory application. Consider a company representative checking inventory for a potential customer. You typically want to lock the record until an order is generated, which would generally flag the item with a status of ordered and remove it from available inventory. If no order is generated, the lock would be released so that other users checking inventory get an accurate count of available inventory. However, pessimistic concurrency control is not possible in a disconnected architecture. Connections are open only long enough to read the data or to update it, so locks cannot be sustained for long periods. Moreover, an application that holds onto locks for long periods is not scalable. Note: If your underlying data source supports transactions, you can simulate pessimistic concurrency by updating your data within a transaction. Optimistic Concurrency In optimistic concurrency, locks are set and held only while the database is being accessed. The locks prevent other users from attempting to update records at the same instant. The data is always available except for the exact moment that an update is taking place. When an update is attempted, the original version of a changed row is compared against the existing row in the database. If the two are different, the update fails with a concurrency error. It is up to you at that point to reconcile the two rows, using business logic that you create. Last in Wins With "last in wins," no check of the original data is made and the update is simply written to the database. It is understood that the following scenario can occur:  User A fetches a record from the database.  User B fetches the same record from the database, modifies it, and writes the updated record back to the database.  User A modifies the 'old' record and writes it back to the database. In the above scenario, the changes User B made were never seen by User A. Be sure that this situation is acceptable if you plan to use the "last in wins" approach of concurrency control.

Concurrency Control in ADO.NET and Visual Studio .NET

ADO.NET and Visual Studio .NET use optimistic concurrency, because the data architecture is based on disconnected data. Therefore, you need to add business logic to resolve issues with optimistic concurrency. If you choose to use optimistic concurrency, there are two general ways to determine if changes have occurred: the version approach (true version numbers or date-time stamps) and the saving all values approach. Version Number Approach In the version number approach, the record to be updated must have a column that contains a date-time stamp or version number. The date-time stamp or a version number is saved on the client when the record is read. This value is then made part of the update. One way to handle concurrency is to update only if value in the WHERE clause matches the value on the record. The SQL representation of this approach is: UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2 WHERE DateTimeStamp = @origDateTimeStamp Alternatively, the comparison can be made using the version number: UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2 WHERE RowVersion = @origRowVersionValue If the date-time stamps or version numbers match, the record in the data store has not changed and can be safely updated with the new values from the dataset. An error is returned if they don't match. You can write code to implement this form of concurrency checking in Visual Studio .NET. You will also have to write code to respond to any update conflicts. To keep the date-time stamp or version number accurate, you need to set up a trigger on the table to update it when a change to a row occurs. Saving All Values Approach An alternative to using a date-time stamp or version number is to get copies of all the fields when the record is read. The DataSet object in ADO.NET maintains two versions of each modified record: an original version (that was originally read from the data source) and a modified version, representing the user updates. When attempting to write the record back to the data source, the original values in the data row are compared against the record in the data source. If they match, it means that the database record has not changed since it was read. In that case, the changed values from the dataset are successfully written to the database. Each data adapter command has a parameters collection for each of its four commands (DELETE, INSERT, SELECT, and UPDATE). Each command has parameters for both the original values, as well as the current (or modified) values. Note: Adding new records (the INSERT command) only require the current values since no original record exists and removing records (the DELETE command) only requires the original values in order to locate the record to delete. The following example shows the command text for a dataset command that updates a typical Customers table. The command is specified for dynamic SQL and optimistic concurrency. UPDATE Customers SET CustomerID = @currCustomerID, CompanyName = @currCompanyName, ContactName = @currContactName, ContactTitle = currContactTitle, Address = @currAddress, City = @currCity, PostalCode = @currPostalCode, Phone = @currPhone, Fax = @currFax WHERE (CustomerID = @origCustomerID) AND (Address = @origAddress OR @origAddress IS NULL AND Address IS NULL) AND (City = @origCity OR @origCity IS NULL AND City IS NULL) AND (CompanyName = @origCompanyName OR @origCompanyName IS NULL AND CompanyName IS NULL) AND (ContactName = @origContactName OR @origContactName IS NULL AND ContactName IS NULL) AND

(ContactTitle = @origContactTitle OR @origContactTitle IS NULL AND ContactTitle IS NULL) AND (Fax = @origFax OR @origFax IS NULL AND Fax IS NULL) AND (Phone = @origPhone OR @origPhone IS NULL AND Phone IS NULL) AND (PostalCode = @origPostalCode OR @origPostalCode IS NULL AND PostalCode IS NULL); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Phone, Fax FROM Customers WHERE (CustomerID = @currCustomerID) Note that the nine SET statement parameters represent the current values that will be written to the database, whereas the nine WHERE statement parameters represent the original values that are used to locate the original record. The first nine parameters in the SET statement correspond to the first nine parameters in the parameters collection. These parameters would have their SourceVersion property set to Current. The next nine parameters in the WHERE statement are used for optimistic concurrency. These placeholders would correspond to the next nine parameters in the parameters collection, and each of these parameters would have their SourceVersion property set to Original. The SELECT statement is used to refresh the dataset after the update has occurred. It is generated when you set the Refresh the DataSet option in the Advanced SQL Generations Options dialog box. Note: The above SQL uses named parameters, whereas oleDbDataAdapter commands use question marks (?) as parameter placeholders. By default Visual Studio will create these parameters for you if you select the Optimistic Currency option in the DataAdapter Configuration Wizard. It is up to you to add code to handle the errors based upon your own business requirements. ADO.NET provides a DBConcurrencyException object that returns the row that violates the concurrency rules. For more information, see Handling Concurrency Errors.

Handling a Concurrency Exception In this walkthrough you will create a Windows application that raises a concurrency error and illustrates one strategy for handling it. The walkthrough simulates two users working with the same data at the same time. The Windows Form you will create allows you to act as both users from a single form. This walkthrough will demonstrate the following tasks: 1. User 1 and User 2 fill their respective datasets with the same data. 2. User 2 edits a record, updates the dataset, and writes the changes to the data source. 3. User 1 edits the same record, updates the dataset, and attempts to write the changes to the data source, which results in a concurrency error being raised. You will catch the error, and then display the different versions of the record, allowing the user (you) to determine what should happen with the pending changes made by User 1. Note: Using a dataset is only one option for data access, and is not the optimal choice in some scenarios. Nonetheless, datasets are usually the right choice in Windows Forms applications, and this walkthrough illustrates one scenario in which datasets are an appropriate choice. Prerequisites for this walkthrough:  Access to the Pubs SQL Server sample database with permission to perform updates.

Create a New Project and Data Connection You begin your walkthrough by creating a new Windows application in Visual C#. To create a new project

1. From the File menu, point to New, and then click Project to display the New Project dialog box. 2. Select Visual C# Projects in the Project Types pane, and then select Windows Application. 3. Name the project concurrency_walkthrough, and then click OK. Visual Studio will add the concurrency_walkthrough project to Solution Explorer and display a new Windows Form in the designer. To create a new data connection 1. In Server Explorer, create a new connection to the Pubs sample database. 2. In Server Explorer, expand the connection you created in the previous step. 3. Expand the Tables area. 4. Drag the authors table onto your form. A Connection object and a DataAdapter object appear in the component tray below the form. Create the Datasets In this section you will create two datasets named DsAuthors1 and DsAuthors2. These datasets will represent the data that the two simultaneous users are working with. Next you will add two DataGrid controls to the form and bind them to the datasets. Finally, two Button controls will be added to the form: an Update button and a Reset button. The Update button will change a record in DsAuthors1 and attempt to write the change to the database. The Reset button resets the database back to the original record so the walkthrough will work if run more than once. To create two new datasets 1. Select the DataAdapter object. 2. From the Data menu, select Generate Dataset. The Generate Dataset dialog box appears. 3. Select New and name the dataset DsAuthors. 4. An instance named DsAuthors1 appears in the component tray. 5. From the Data tab of the Toolbox, drag a DataSet onto the form. 6. The Add Dataset dialog box appears. 7. Confirm that Typed dataset is selected and concurrency_walkthrough.DsAuthors appears in the Name box. An instance named DsAuthors2 appears in the component design tray.

Bind the Data and Add Buttons The data grids are used only to display the data. No data should be edited in the data grids during this walkthrough. To add two DataGrids to the form 1. From the Windows Forms tab of the Toolbox, drag a DataGrid object onto the left side of your form. 2. From the Windows Forms tab of the Toolbox, drag a DataGrid object onto the right side of your form. To bind the datasets to the DataGrid Controls 1. Select DataGrid1 and set the following properties in the Properties window: Property Setting DataSource DsAuthors1 DataMember authors CaptionText DsAuthors1 2. Select DataGrid2 and set the following properties in the Properties window: Property Setting DataSource DsAuthors2

DataMember Authors CaptionText DsAuthors2 To add the Update and Reset buttons to the form 3. From the Windows Forms tab of the Toolbox, drag a Button control onto the form and place it above DataGrid1. 4. With the button selected, in the Properties window name the button btnUpdate and set its Text property to Update. 5. From the Windows Forms tab of the Toolbox, drag a second Button object onto the form and place it above DataGrid2. 6. With the button selected in the Properties window, name the button btnReset and set its Text property to Reset.

Reset the Database You will add code to the form that will reset the database to known values (in case you want to run the walkthrough more than once). To add code to reset the database Right-click the form, choose View Code from the shortcut menu, and then insert the following code: private void resetDatabase() { sqlDataAdapter1.Fill(dsAuthors1); // Fill the dsAuthors dataset with data. dsAuthors1.authors[0].au_fname = "John"; // Reset the au_fname in the first row to "John". sqlDataAdapter1.Update(dsAuthors1); // Write the record back to the database. }

Fill the Datasets This step fills both datasets with the same data. To add code to fill the datasets from the database Insert the following code into the Code Editor: private void filltheDataSets() { sqlDataAdapter1.Fill(dsAuthors1); sqlDataAdapter1.Fill(dsAuthors2); }

Simulate Changes by User 2 To add code to simulate changes by User 2 Insert the following code into the Code Editor: private void user2changes() { dsAuthors2.authors[0].au_fname = "User 2"; // Simulate a second user changing a record. sqlDataAdapter1.Update(dsAuthors2.GetChanges()); // Write it back to the database. sqlDataAdapter1.Fill(dsAuthors2); // Refresh dsAuthors2 with the updated data. } Create the Form_Load Event Handler To add code into the Form_Load event to initialize the walkthrough 1. Double-click an empty area of the form to automatically create the Form_Load event handler. 2. Add code so the event handler looks like the following:

private void Form1_Load(object sender, System.EventArgs e) { resetDatabase(); filltheDataSets(); user2changes(); } 3. Save your project.

Run the Application

Press F5 to run the application. The form appears with two datagrids filled with data from the authors table in the Pubs database. The au_fname field of the first record in DsAuthors1 should be John. The au_fname field of the first record in DsAuthors2 should be User 2. Your form will look similar to the following:

From the Debug menu, select Stop Debugging.

Update the Dataset and Write the Changes to the Database Next, you will write code that will attempt to update the database with changes from the DsAuthors1 dataset. If successful, the AcceptChanges method of the dataset is called and a message box displays a success message. If the update fails for any reason, the error is caught and a message box displays the error message, with the type of error object as the title of the message box. Because this walkthrough is designed to raise a concurrency error, the successful message box is shown for completeness. Note: This Try…Catch block will catch any error. Later in this walkthrough you will add an additional catch statement to specifically handle the concurrency error. To update the database 1. Call the Update method. 2. Create an exception handler that displays a message box. Your code should look like this: private void updateDatabase() { try { // Update the database with the changes from dsAuthors1. sqlDataAdapter1.Update(dsAuthors1.GetChanges()); dsAuthors1.AcceptChanges();

}

MessageBox.Show("The update was successful."); } catch (Exception ex) { // Display information about update errors. MessageBox.Show(ex.Message, ex.GetType().ToString()); }

Next, you will add code that changes the au_fname column in the DsAuthors1 dataset. The code then calls the updateDatabase procedure to try to write this change to the database. Because User 2 changed the value earlier, a concurrency error will be raised. To update the DsAuthors1 dataset 1. Double-click the Update button. 2. Create the btnUpdate_Click event handler: private void btnUpdate_Click(object sender, System.EventArgs e) { // Change au_fname in the first row of dsAuthors1 to "User 1". dsAuthors1.authors[0].au_fname = "User 1"; updateDatabase(); } 3. Save your project. 4. Press F5 to run the application. 5. Click the Update button to change the au_fname field of the first record to User 1. The concurrency error will be raised. Handling Concurrency Errors How you handle the error is dependent upon the specific business rules that govern your application. The following strategy to handle the error will be used as an illustration. The application will present the user with three versions of the record:  The current record in the database  The original record in the dataset  The proposed changes in the dataset The user will then be able to either overwrite the database with the proposed change or cancel the update and refresh the dataset. Creating a Custom Error Handler When you perform an update, you generally want to do so in a structured exception handler so that you can catch errors. In the code you used above, you used a try...catch block that caught all errors — that is, a structure that includes a generic catch statement for any error. You can also catch specific errors, so that you can respond appropriately. To illustrate, this walkthrough will add an exception handler for one specific error — namely, a concurrency error, which you can examine using the DbConcurrencyException object. Here, you will handle this error by displaying information to the user. To add specific handling for the DBConcurrencyException error 1. If the application is still running, exit run mode to return to the Code Editor. 2. Add a second catch statement above the existing one in the updateDatabase method. 3. Pass the DBConcurrencyException object to the createMessage procedure, which you will create in the next section. private void updateDatabase() { try { // Update the database with the changes from dsAuthors1.

sqlDataAdapter1.Update(dsAuthors1.GetChanges()); dsAuthors1.AcceptChanges(); MessageBox.Show("The update was successful!");

} catch (DBConcurrencyException dbcx) { createMessage(dbcx); } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } Displaying Choices to the User The code you just wrote calls the createMessage procedure to display error information to the user. For this walkthrough, you will use a message box to display the different versions of the record to the user and allow the user to choose whether to overwrite the record with new changes or cancel the edit. Note For simplicity's sake, this walkthrough uses the second dataset (DsAuthors2) as the data source for fetching the current record in the database. In a real-world application you would require the actual data source to fetch the current value of the record that raised the error. To create the createMessage procedure Create the error handler by adding the following code to the Code Editor: private void createMessage(DBConcurrencyException dbcx) { // Declare variables to hold the row versions for display in the message box. string strInDs = "Original record in dsAuthors1:\n"; string strInDB = "Current record in database:\n"; string strProposed = "Proposed change:\n"; string strPromptText = "Do you want to overwrite the current record in the " + " database with the proposed change?\n"; string strMessage; System.Windows.Forms.DialogResult response; // Loop through the column values. DataRow rowInDB = dsAuthors2.authors.FindByau_id(dbcx.Row["Au_ID"].ToString()); for (int i = 0; i < dbcx.Row.ItemArray.Length; i++) { strInDs += dbcx.Row[i, DataRowVersion.Original] + "\n"; strInDB += rowInDB[i, DataRowVersion.Current] + "\n"; strProposed += dbcx.Row[i, DataRowVersion.Current] + "\n"; } // Create the message box text string. strMessage = strInDs + "\n" + strInDB + "\n" + strProposed + "\n" + strPromptText; // Display the message box. response = MessageBox.Show(strMessage, dbcx.Message, MessageBoxButtons.YesNo); processResponse(response); } Processing the User's Response You will also need code to process the user's response to the message box. The options are either to overwrite the current record in the database with the proposed change or not. If the user chooses yes, the Merge method of DsAuthors1 is called with the preserveChanges argument set

to true. This takes the original versions of the data rows in DsAuthors2 and merges them with the current versions of data rows in DsAuthors1. This will cause the update attempt to be successful, because the original version of the record now matches the database. To process the user input from the message box Add the following code to the Code Editor: private void processResponse(System.Windows.Forms.DialogResult response) { // Execute the appropriate code depending on the button selected in the message box. switch (response) { case System.Windows.Forms.DialogResult.Yes : // Overwrite the database with the proposed record. dsAuthors1.Merge(dsAuthors2, true); sqlDataAdapter1.Update(dsAuthors1); dsAuthors1.AcceptChanges(); break; case System.Windows.Forms.DialogResult.No : // Cancel proposed changes and refresh dsAuthors1. dsAuthors1.Merge(dsAuthors2); break; } } Resetting the Data To reset the form, the Form1_Load method will be modified to execute when the Reset button is clicked. Because Visual Basic and Visual C# have different event models, there are different ways to associate an event handler with an event. Note: One method can be the event handler for multiple events of multiple objects. To create an event handler for the btnReset button  Open the form in the designer.  Click the Reset button.  In the Properties window, click the Events button in the Properties window toolbar.  Locate the Click event, and then click the arrow to see all the methods that could respond to the event.  In this case, Form1_Load appears because it has the right signature for a button.click event.  Select Form1_Load. The code to associate the Form1_Load method with the btnReset.Click event is automatically generated. Form1_Load now responds to both Form1.Load and btnReset.Click. Private void Form1_Load(System.Object sender, System.EventArgs e) { resetDatabase(); filltheDataSets(); user2changes(); } Tip: To view the generated code, double-click the form and expand the dimmed "Windows Form Designer generated code" section. Run the Application 1. Press F5 to run the application. 2. Click the Update button. The concurrency error will be raised, and the message box will appear.

Optimistic Concurrency In a multi-user environment, there are two models for updating data in a database: optimistic concurrency, and pessimistic concurrency. The DataSet object is designed to encourage the use of optimistic concurrency for long-running activities such as when you are remoting data and when users are interacting with data. Pessimistic concurrency involves locking rows at the data source to prevent users from modifying data in a way that affects other users. In a pessimistic model, when a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the lock owner releases it. This model is primarily used in environments where there is heavy contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur. Therefore, in a pessimistic currency model, a user who reads a row with the intention of changing it establishes a lock. Until the user has finished the update and released the lock, no one else can change that row. For this reason, pessimistic concurrency is best implemented when lock times will be short, as in programmatic processing of records. Pessimistic concurrency is not a scalable option when users are interacting with data, causing records to be locked for relatively large periods of time. By contrast, users who use optimistic concurrency do not lock a row when reading it. When a user wants to update a row, the application must determine whether another user has changed the row since it was read. Optimistic concurrency is generally used in environments with a low contention for data. This improves performance as no locking of records is required, and locking of records requires additional server resources. Also, in order to maintain record locks, a persistent connection to the database server is required. Because this is not the case in an optimistic concurrency model, connections to the server are free to serve a larger number of clients in less time. In an optimistic concurrency model, a violation is considered to have occurred if, after a user receives a value from the database, another user modifies the value before the first user has attempted to modify it. The following tables follow an example of optimistic concurrency. At 1:00 p.m., User1 reads a row from the database with the following values: CustID 101

LastName Smith

Column name CustID LastName FirstName

FirstName Bob Original value 101 Smith Bob

Current value 101 Smith Bob

Value in database 101 Smith Bob

At 1:01 p.m., User2 reads the same row. At 1:03 p.m., User2 changes FirstName from "Bob" to "Robert" and updates the database. Column name CustID LastName FirstName

Original value 101 Smith Bob

Current value 101 Smith Robert

Value in database 101 Smith Bob

The update succeeds because the values in the database at the time of update match the original values that User2 has. At 1:05 p.m., User1 changes Bob's first name to "James" and tries to update the row. Column name CustID LastName

Original value 101 Smith

Current value 101 Smith

Value in database 101 Smith

FirstName Bob James Robert At this point, User1 encounters an optimistic concurrency violation because the values in the database no longer match the original values that User1 was expecting. The decision now needs to be made whether to overwrite the changes supplied by User2 with the changes supplied by User1, or to cancel the changes by User1. Testing for Optimistic Concurrency Violations There are several techniques for testing for an optimistic concurrency violation. One involves including a timestamp column in the table. Databases commonly provide timestamp functionality that can be used to identify the date and time when the record was last updated. Using this technique, a timestamp column is included in the table definition. Whenever the record is updated, the timestamp is updated to reflect the current date and time. In a test for optimistic concurrency violations, the timestamp column is returned with any query of the contents of the table. When an update is attempted, the timestamp value in the database is compared to the original timestamp value contained in the modified row. If they match, the update is performed and the timestamp column is updated with the current time to reflect the update. If they do not match, an optimistic concurrency violation has occurred. Another technique for testing for an optimistic concurrency violation is to verify that all the original column values in a row still match those found in the database. For example, consider the following query: SELECT Col1, Col2, Col3 FROM Table1 To test for an optimistic concurrency violation when updating a row in Table1, you would issue the following UPDATE statement: UPDATE Table1 Set Col1 = @NewCol1Value, Set Col2 = @NewCol2Value, Set Col3 = @NewCol3Value WHERE Col1 = @OldCol1Value AND Col2 = @OldCol2Value AND Col3 = @OldCol3Value As long as the original values match the values in the database, the update is performed. If a value has been modified, the update will not modify the row because the WHERE clause will not find a match. Note that it is recommended to always return a unique primary key value in your query. Otherwise, the preceding UPDATE statement may update more than one row, which might not be your intent. If a column at your data source allows nulls, you may need to extend your WHERE clause to check for a matching null reference in your local table and at the data source. For example, the following UPDATE statement verifies that a null reference in the local row still matches a null reference at the data source, or that the value in the local row still matches the value at the data source. UPDATE Table1 Set Col1 = @NewVal1 WHERE (@OldVal1 IS NULL AND Col1 IS NULL) OR Col1 = @OldVal1 You may also choose to apply less restrictive criteria when using an optimistic concurrency model. For example, using only the primary key columns in the WHERE clause results in the data being overwritten regardless of whether the other columns have been updated since the last query. You can also apply a WHERE clause only to specific columns, resulting in data being overwritten unless particular fields have been updated since they were last queried. The DataAdapter.RowUpdated Event The DataAdapter.RowUpdated event can be used in conjunction with the techniques described earlier, to provide notification to your application of optimistic concurrency violations. RowUpdated occurs after each attempt to update a Modified row from a DataSet. This enables you to add special handling code, including processing when an exception occurs, adding custom error information, adding retry logic, and so on. The RowUpdatedEventArgs object returns a RecordsAffected property with the number of rows affected by a particular update command for a modified row in a table. By setting the update command to test for optimistic concurrency, the

RecordsAffected property will, as a result, return a value of 0 when an optimistic concurrency violation has occurred, because no records were updated. If this is the case, an exception is thrown. The RowUpdated event enables you to handle this occurrence and avoid the exception by setting an appropriate RowUpdatedEventArgs.Status value, such as UpdateStatus.SkipCurrentRow. Optionally, you can set DataAdapter.ContinueUpdateOnError to true, before calling Update, and respond to the error information stored in the RowError property of a particular row when the Update is completed. Optimistic Concurrency Example The following is a simple example that sets the UpdateCommand of a DataAdapter to test for optimistic concurrency, and then uses the RowUpdated event to test for optimistic concurrency violations. When an optimistic concurrency violation is encountered, the application sets the RowError of the row that the update was issued for to reflect an optimistic concurrency violation. Note that the parameter values passed to the WHERE clause of the UPDATE command are mapped to the Original values of their respective columns. SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID", nwindConn); // The Update command checks for optimistic concurrency violations in the WHERE clause. custDA.UpdateCommand = new SqlCommand("UPDATE Customers (CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName", nwindConn); custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 30, "CompanyName"); // Pass the original values to the WHERE clause parameters. SqlParameter myParm; myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID"); myParm.SourceVersion = DataRowVersion.Original; myParm = custDA.UpdateCommand.Parameters.Add("@oldCompanyName", SqlDbType.NVarChar, 30, "CompanyName"); myParm.SourceVersion = DataRowVersion.Original; // Add the RowUpdated event handler. custDA.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated); DataSet custDS = new DataSet(); custDA.Fill(custDS, "Customers"); custDA.Update(custDS, "Customers"); // Modify the DataSet contents. foreach (DataRow myRow in custDS.Tables["Customers"].Rows) { if (myRow.HasErrors) Console.WriteLine(myRow[0] + "\n" + myRow.RowError); } protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args) { if (args.RecordsAffected == 0) { args.Row.RowError = "Optimistic Concurrency Violation Encountered";

}

args.Status = UpdateStatus.SkipCurrentRow;

}

Implementing Optimistic Concurrency with Dynamic SQL Visual Studio can implement optimistic concurrency with dynamic SQL. Ultimately, this approach builds an SQL command string with a WHERE clause that contains all of the original data-store values. This string is then invoked as a SQL command on the data store. The WHERE clause will not return any records if changes have occurred to the record in the data store. These concurrency exceptions must be handled according to business needs. To implement optimistic concurrency with dynamic SQL 1. If you do not already have a connection to a database, add one using Server Explorer. 2. Drag a table from Server Explorer onto the designer surface. 3. You will get a DataAdapter object and a Connection object. If you are working with a Windows Application project, a component designer tray will appear at the bottom of the screen. This tray allows visual development of components that have no run-time user interface. If you started with an XML Web service, the entire main window will provide a component design surface. 4. Right-click the DataAdapter, and then select Configure Data Adapter to display the Data Adapter Configuration Wizard. 5. Click Next and then click Next again to bring up the Choose a Query Type page. 6. Make sure that Use SQL statement is selected, and then click Next. 7. In the Generate the SQL Statements page, click the Query Builder button. 8. In the Query Builder, select the fields you want. 9. Select the columns you want, and then click OK. 10. In the Generate the SQL Statements page, click the Advanced Options button and note that Use optimistic concurrency is selected by default. 11. Click OK and then Finish to complete the wizard. You can inspect the results of the configuration by examining the three update-related data adapter command properties: DeleteCommand, InsertCommand, and UpdateCommand.

Implementing Optimistic Concurrency with Stored Procedures Visual Studio can implement optimistic concurrency with stored procedures. Ultimately this approach builds an SQL command with a WHERE clause that contains all of the original data store values. Parameters that include the WHERE clause values are passed to a stored procedure within the data store. The WHERE clause will not return any records if changes have occurred to the data store. These changes can be handled according to business needs. To implement optimistic concurrency with stored procedures 1. If you do not already have a connection to a database, add one. 2. Drag a table from Server Explorer onto the designer surface. 3. You will get an adapter object and a connection object. Note If you are working with a project that has an interface (such as a Windows application or a Web application), a component tray will appear at the bottom of the designer. This tray allows visual development of components that have no run-time user interface. If you are working with other project types (such as an XML Web service), the objects will appear on the Component Designer — that is, the entire main window will provide a component design surface. 4. Right-click the adapter object, and then select Configure Data Adapter from the shortcut menu to display the Data Adapter Configuration Wizard. 5. Click Next and then click Next again for the Choose a Query Type page.

6. Make sure that Create new stored procedures is selected, and then click Next. 7. In the Generate the stored procedures page, click the Query Builder button. 8. In the Query Builder, check the individual fields you want. Note In the Generate the stored procedures page, click the Advanced Options button, and notice the Use optimistic concurrency option is selected by default. 9. Click OK, and then click Next to bring up the Create the Stored Procedures page. 10. In the Create the Stored Procedures page, assign names to each of the four stored procedures that will be generated, and you can have the wizard automatically create them in the database for you or choose to manually create them later. Click Next then Finish to complete the wizard. You can inspect the results of the configuration by examining the three update-related data adapter command properties: the DeleteCommand, InsertCommand, and the UpdateCommand.

Handling Concurrency Errors ADO.NET provides a DBConcurrencyException object to assist in resolving issues arising from concurrency violations. The DBConcurrencyException object returns the data row that caused the error, as well as the message associated with the failed update. The following example attempts to update a data source with the contents of myDataset from within a Try… Catch block, if an error is raised the error message along with the first column of the offending data row is displayed. Note The code below is an illustration of one strategy in handling a database update error. The code assumes several things; an existing connection to a database, an existing dataset named myDataset, as well as the assumption that execution of the update command will raise a concurrency violation. To resolve a concurrency violation 1. Execute the command to update the database from within a Try…Catch block. 2. If an exception is raised, inspect the Catch statement's Row and Message properties to determine what caused the violation. 3. Add code to resolve the error based on your application's business rules. try { SqlDataAdapter1.Update(myDataset); } catch (DBConcurrencyException ex) { string customErrorMessage; customErrorMessage = ex.Message; customErrorMessage += ex.Row[0].ToString(); // Replace the above code with appropriate business logic to resolve the concurrency violation. }