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.

Prerequisites

In order to complete this walkthrough, you need:

• Access to the Northwind sample database with permission to perform updates.

Create a new project

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 select OK.

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

Create the Northwind dataset

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

   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. Select Finish to add the dataset to the project.

Create a data-bound DataGridView control

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 DataGridView in 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.

Test the form

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

To test the form

1. Select F5 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.

Handleconcurrency errors

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.

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.");
       }
   }
   

   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
   

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

   Private Sub CustomersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomersBindingNavigatorSaveItem.Click
       UpdateDatabase()
   End Sub
   

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

  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
  

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

  ' 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
  

Test the form

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. Select F5 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.

   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. Selecting No cancels the update and updates the dataset with the values that are currently in the database. Selecting Yes writes the proposed value to the database.

See Also

• Save data back to the database