Handle a concurrency exception

 

Concurrency exceptions (DBConcurrencyException) are raised when two users attempt to change the same data in a database at the same time. In this walkthrough, you create a Windows application that illustrates how to catch a DBConcurrencyException, locate the row that caused the error, and learn a strategy for how to handle it.

This walkthrough takes you through the following process:

  1. Create a new Windows Application project.

  2. Create a new dataset based on the Northwind Customers table.

  3. Create a form with a DataGridView to display the data.

  4. Fill a dataset with data from the Customers table in the Northwind database.

  5. Use the Visual Database Tools in Visual Studio to directly access the Customers data table and change a record.

  6. Change the same record to a different value, update the dataset, and attempt to write the changes to the database, which results in a concurrency error being raised.

  7. Catch the error, then display the different versions of the record, allowing the user to determine whether to continue and update the database, or to cancel the update.

In order to complete this walkthrough, you need:

System_CAPS_ICON_note.jpg Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or the edition that you're using. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Customizing Development Settings in Visual Studio.

You begin your walkthrough by creating a new Windows application.

To create a new Windows application project

  1. On the File menu, create a new project.

  2. In the Project Types pane, select a programming language.

  3. In the Templates pane, select Windows Application.

  4. Name the project ConcurrencyWalkthrough, and then selectOK.

    Visual Studio adds the project to Solution Explorer and displays a new form in the designer.

In this section, you create a dataset named NorthwindDataSet.

To create the NorthwindDataSet

  1. On the Data menu, choose Add New Data source.

    The Data Source Configuration Wizard opens.

  2. On the Choose a Data Source Typescreen, select Database.

  3. Select a connection to the Northwind sample database from the list of available connections.If the connection is not available in the list of connections,selectNew Connection

    System_CAPS_ICON_note.jpg Note

    If you are connecting to a local database file, select No when asked if you would you like to add the file to your project.

  4. On the Save connection string to the application configuration filescreen, select Next.

  5. Expand the Tables node and select the Customers table. The default name for the dataset should be NorthwindDataSet.

  6. SelectFinish to add the dataset to the project.

In this section, you create a DataGridView by dragging the Customers item from the Data Sources window onto your Windows Form.

To create a DataGridView control that is bound to the Customers table

  1. On the Data menu, choose Show Data Sources to open the Data Sources Window.

  2. In the Data Sources window, expand the NorthwindDataSet node, and then select the Customers table.

  3. Select the down arrow on the table node, and then select DataGridViewin the drop-down list.

  4. Drag the table onto an empty area of your form.

    A DataGridView control named CustomersDataGridView and a BindingNavigator named CustomersBindingNavigator are added to the form that's bound to the BindingSource.This, is in, is turn bound to the Customers table in the NorthwindDataSet.

You can now test the form to make sure it behaves as expected up to this point.

To test the form

  1. SelectF5 to run the application

    The form appears with a DataGridView control on it that's filled with data from the Customers table.

  2. On the Debug menu, selectStop Debugging.

How you handle errors is depends on the specific business rules that govern your application. For this walkthrough, we use the following strategy as an example for how tohandle the concurrency error.

The applicationpresents the user with three versions of the record:

  • The current record in the database

  • The original record that's loaded into the dataset

  • The proposed changes in the dataset

The user is then able to either overwrite the database with the proposed version, or cancel the update and refresh the dataset with the new values from the database.

To enable the handling of concurrency errors

  1. Create a custom error handler.

  2. Display choices to the user.

  3. Process the user's response.

  4. Resend the update, or reset the data in the dataset.

Addcode to handle the concurrency exception

When you attempt to perform an update and an exception gets raised, you generally want to do something with the information that's provided by the raised exception.

In this section, you add code that attempts to update the database.You also handle any DBConcurrencyException that might get raised, as well as any other exceptions.

System_CAPS_ICON_note.jpg Note

The CreateMessage and ProcessDialogResults methods will be added later in this walkthrough.

To add error handling for the concurrency error
  1. Add the following code below the Form1_Load method:

            private void UpdateDatabase()
            {
                try
                {
                    this.customersTableAdapter.Update(this.northwindDataSet.Customers);
                    MessageBox.Show("Update successful");
                }
                catch (DBConcurrencyException dbcx)
                {
                    DialogResult response = MessageBox.Show(CreateMessage((NorthwindDataSet.CustomersRow)
                        (dbcx.Row)), "Concurrency Exception", MessageBoxButtons.YesNo);
    
                    ProcessDialogResult(response);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("An error was thrown while attempting to update the database.");
                }
            }
    

  2. Replace the CustomersBindingNavigatorSaveItem_Click method to call the UpdateDatabase method so it looks like the following:

            private void customersBindingNavigatorSaveItem_Click(object sender, EventArgs e)
            {
                UpdateDatabase();
            }
    

Displaychoices to the user

The code you just wrote calls the CreateMessage procedure to display error information to the user. For this walkthrough, you use a message box to display the different versions of the record to the user.This enables the user to choose whether to overwrite the record with the changes or cancel the edit. Once the user selects an option (clicks a button) on the message box, the response is passed to the ProcessDialogResult method.

To create the message to display to the user
  • Create the message by adding the following code to the Code Editor. Enter this code below the UpdateDatabase method.

            private string CreateMessage(NorthwindDataSet.CustomersRow cr)
            {
                return
                    "Database: " + GetRowData(GetCurrentRowInDB(cr), DataRowVersion.Default) + "\n" +
                    "Original: " + GetRowData(cr, DataRowVersion.Original) + "\n" +
                    "Proposed: " + GetRowData(cr, DataRowVersion.Current) + "\n" +
                    "Do you still want to update the database with the proposed value?";
            }
    
    
            //--------------------------------------------------------------------------
            // This method loads a temporary table with current records from the database
            // and returns the current values from the row that caused the exception.
            //--------------------------------------------------------------------------
            private NorthwindDataSet.CustomersDataTable tempCustomersDataTable = 
                new NorthwindDataSet.CustomersDataTable();
    
            private NorthwindDataSet.CustomersRow GetCurrentRowInDB(NorthwindDataSet.CustomersRow RowWithError)
            {
                this.customersTableAdapter.Fill(tempCustomersDataTable);
    
                NorthwindDataSet.CustomersRow currentRowInDb = 
                    tempCustomersDataTable.FindByCustomerID(RowWithError.CustomerID);
    
                return currentRowInDb;
            }
    
    
            //--------------------------------------------------------------------------
            // This method takes a CustomersRow and RowVersion 
            // and returns a string of column values to display to the user.
            //--------------------------------------------------------------------------
            private string GetRowData(NorthwindDataSet.CustomersRow custRow, DataRowVersion RowVersion)
            {
                string rowData = "";
    
                for (int i = 0; i < custRow.ItemArray.Length ; i++ )
                {
                    rowData = rowData + custRow[i, RowVersion].ToString() + " ";
                }
                return rowData;
            }
    

Process the user's response

You 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 abandon the local changes and refresh the data table with the record that's currently in the database. If the user chooses yes, the Merge method is called with the preserveChanges argument set to true. This causes the update attempt to be successful, because the original version of the record now matches the record in the database.

To process the user input from the message box
  • Add the following code below the code that was added in the previous section.

            // This method takes the DialogResult selected by the user and updates the database 
            // with the new values or cancels the update and resets the Customers table 
            // (in the dataset) with the values currently in the database.
    
            private void ProcessDialogResult(DialogResult response)
            {
                switch (response)
                {
                    case DialogResult.Yes:
                        northwindDataSet.Merge(tempCustomersDataTable, true, MissingSchemaAction.Ignore);
                        UpdateDatabase();
                        break;
    
                    case DialogResult.No:
                        northwindDataSet.Merge(tempCustomersDataTable);
                        MessageBox.Show("Update cancelled");
                        break;
                }
            }
    

You can now test the form to make sure it behaves as expected. To simulate a concurrency violation you need to change data in the database after filling the NorthwindDataSet.

To test the form

  1. SelectF5 to run the application.

  2. After the form appears, leave it running and switch to the Visual Studio IDE.

  3. On the View menu, choose Server Explorer.

  4. In Server Explorer, expand the connection your application is using, and then expand the Tables node.

  5. Right-click the Customers table, and then select Show Table Data.

  6. In the first record (ALFKI) change ContactName to Maria Anders2.

    System_CAPS_ICON_note.jpg Note

    Navigate to a different row to commit the change.

  7. Switch to the ConcurrencyWalkthrough's running form.

  8. In the first record on the form (ALFKI), changeContactName to Maria Anders1.

  9. Select the Save button.

    The concurrency error is raised, and the message box appears.

  10. SelectingNo cancels the update and updates the dataset with the values that are currently in the database.SelectingYes writes the proposed value to the database.

Save data back to the database

Show: