Handle a concurrency exception

Article
11/15/2016

Note

This article applies to Visual Studio 2015. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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:

Create a new Windows Application project.
Create a new dataset based on the Northwind Customers table.
Create a form with a DataGridView to display the data.
Fill a dataset with data from the Customers table in the Northwind database.
Use the Visual Database Tools in Visual Studio to directly access the Customers data table and change a record.
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.
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.

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.

Create a new project

You begin your walkthrough by creating a new Windows application.

To create a new Windows application project

On the File menu, create a new project.
In the Project Types pane, select a programming language.
In the Templates pane, select Windows Application.
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

On the Data menu, choose Add New Data source.

The Data Source Configuration Wizard opens.
On the Choose a Data Source Typescreen, select Database.
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.
On the Save connection string to the application configuration filescreen, select Next.
Expand the Tables node and select the Customers table. The default name for the dataset should be NorthwindDataSet.
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

On the Data menu, choose Show Data Sources to open the Data Sources Window.
In the Data Sources window, expand the NorthwindDataSet node, and then select the Customers table.
Select the down arrow on the table node, and then select DataGridView in the drop-down list.
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

Select F5 to run the application

The form appears with a DataGridView control on it that's filled with data from the Customers table.
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

Create a custom error handler.
Display choices to the user.
Process the user's response.
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.

Note

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

To add error handling for the concurrency error

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

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

Select F5 to run the application.
After the form appears, leave it running and switch to the Visual Studio IDE.
On the View menu, choose Server Explorer.
In Server Explorer, expand the connection your application is using, and then expand the Tables node.
Right-click the Customers table, and then select Show Table Data.
In the first record (ALFKI) change ContactName to Maria Anders2.

Note

Navigate to a different row to commit the change.
Switch to the ConcurrencyWalkthrough's running form.
In the first record on the form (ALFKI), changeContactName to Maria Anders1.
Select the Save button.

The concurrency error is raised, and the message box appears.
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.

Partager via

Handle a concurrency exception

Prerequisites

Create a new project

To create a new Windows application project

Create the Northwind dataset

To create the NorthwindDataSet

Create a data-bound DataGridView control

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

Test the form

To test the form

Handleconcurrency errors

To enable the handling of concurrency errors

Addcode to handle the concurrency exception

To add error handling for the concurrency error

Displaychoices to the user

To create the message to display to the user

Process the user's response

To process the user input from the message box

Test the form

To test the form

See Also

Ressources supplémentaires