Walkthrough: Handling 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 catching a DBConcurrencyException, locating the row that caused the error, and one strategy for you can use for handling 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. After filling the dataset, use the Visual Database Tools in Visual Studio to directly access the Customers data table and change a record.

6. Then on the form, 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.

Prerequisites

In order to complete this walkthrough, you need:

• Access to the Northwind sample database with permission to perform updates. For more information, see How to: Install Sample Databases.

Note

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

Creating a New Project

You begin your walkthrough by creating a new Windows application.

To create a new Windows Application project

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

2. Select a programming language in the Project Types pane.

3. Select Windows Application in the Templates pane.

4. Name the project ConcurrencyWalkthrough, and then click OK.

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

Creating the Northwind Dataset

In this section you will create a dataset named NorthwindDataSet.

To create the NorthwindDataSet

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

   The Data Source Configuration Wizard opens.

2. Select Database on the Choose a Data Source Type page.

3. Select a connection to the Northwind sample database from the list of available connections, or click New Connection if the connection is not available in the list of connections.

   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. Click Next on the Save connection string to the application configuration file page.

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

6. Click Finish to add the dataset to the project.

Creating a Data-bound DataGridView Control

In this section you will 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. From the Data menu, choose Show Data Sources to open the Data Sources Window.

2. From the Data Sources window expand the NorthwindDataSet node and select the Customers table.

3. Click the down arrow on the table node and select DataGridView from 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 bound to the BindingSource that is in turn bound to the Customers table in the NorthwindDataSet.

Checkpoint

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

To test the form

1. Press F5 to run the application

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

2. From the Debug menu, choose Stop Debugging.

Handling Concurrency Errors

How you handle errors is dependent upon the specific business rules that govern your application. For this walkthrough, after a concurrency violation is raised, the following strategy to handle the concurrency 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 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.

Adding Code 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 provided by the raised exception.

In this section you will add code that will attempt to update the database, and handle any DBConcurrencyException that might get raised, as well as any other exception.

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 Sub UpdateDatabase()
   
       Try 
           Me.CustomersTableAdapter.Update(Me.NorthwindDataSet.Customers)
           MsgBox("Update successful")
   
       Catch dbcx As Data.DBConcurrencyException
           Dim response As Windows.Forms.DialogResult
   
           response = MessageBox.Show(CreateMessage(CType(dbcx.Row, NorthwindDataSet.CustomersRow)),
               "Concurrency Exception", MessageBoxButtons.YesNo)
   
           ProcessDialogResult(response)
   
       Catch ex As Exception
           MsgBox("An error was thrown while attempting to update the database.")
       End Try 
   End Sub
   

   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 Sub CustomersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomersBindingNavigatorSaveItem.Click
       UpdateDatabase()
   End Sub
   

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

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 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 Function CreateMessage(ByVal cr As NorthwindDataSet.CustomersRow) As String 
      Return "Database: " & GetRowData(GetCurrentRowInDB(cr), 
                                       Data.DataRowVersion.Default) & vbCrLf &
             "Original: " & GetRowData(cr, Data.DataRowVersion.Original) & vbCrLf &
             "Proposed: " & GetRowData(cr, Data.DataRowVersion.Current) & vbCrLf &
             "Do you still want to update the database with the proposed value?" 
  End Function 
  
  
  '-------------------------------------------------------------------------- 
  ' 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 TempCustomersDataTable As New NorthwindDataSet.CustomersDataTable
  
  Private Function GetCurrentRowInDB(
      ByVal RowWithError As NorthwindDataSet.CustomersRow
      ) As NorthwindDataSet.CustomersRow
  
      Me.CustomersTableAdapter.Fill(TempCustomersDataTable)
  
      Dim currentRowInDb As NorthwindDataSet.CustomersRow =
          TempCustomersDataTable.FindByCustomerID(RowWithError.CustomerID)
  
      Return currentRowInDb
  End Function 
  
  
  '-------------------------------------------------------------------------- 
  ' This method takes a CustomersRow and RowVersion  
  ' and returns a string of column values to display to the user. 
  '-------------------------------------------------------------------------- 
  Private Function GetRowData(ByVal custRow As NorthwindDataSet.CustomersRow,
      ByVal RowVersion As Data.DataRowVersion) As String 
  
      Dim rowData As String = "" 
  
      For i As Integer = 0 To custRow.ItemArray.Length - 1
          rowData &= custRow.Item(i, RowVersion).ToString() & " " 
      Next 
  
      Return rowData
  End Function
  

  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;
  }
  

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 abandon the local changes and refresh the data table with the record currently in the database. If the user chooses yes, the Merge method is called with the preserveChanges argument set to true. This will cause 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 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 Sub ProcessDialogResult(ByVal response As Windows.Forms.DialogResult)
  
      Select Case response
  
          Case Windows.Forms.DialogResult.Yes
              NorthwindDataSet.Customers.Merge(TempCustomersDataTable, True)
              UpdateDatabase()
  
          Case Windows.Forms.DialogResult.No
              NorthwindDataSet.Customers.Merge(TempCustomersDataTable)
              MsgBox("Update cancelled")
      End Select 
  End Sub
  

  // 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;
      }
  }
  

Testing

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. Press F5 to run the application.

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

3. From 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 select Show Table Data.

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

   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), change the ContactName to Maria Anders1.

9. Click the Save button.

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

10. Clicking No cancels the update and updates the dataset with the values currently in the database, whereas clicking Yes writes the proposed value to the database.

See Also

Concepts

What's New for Data Application Development in Visual Studio 2012

Binding Windows Forms Controls to Data in Visual Studio

Preparing Your Application to Receive Data

Fetching Data into Your Application

Binding Controls to Data in Visual Studio

Editing Data in Your Application

Validating Data

Saving Data

Other Resources

Data Walkthroughs

Connecting to Data in Visual Studio