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 there 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, 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 Basic or 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. Depending on what language you want to use, select Visual Basic Projects or Visual C# Projects in the Project Types pane, and then select Windows Application.
3. Name the project ConcurrencyWalkthrough, then click OK.

   Visual Studio will add the ConcurrencyWalkthrough 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. For more information, see Adding New Data Connections in Server Explorer.

   Security Note   Storing connection-string details (such as the server name, user name, and password) can have implications for the security of your application. Using Windows Integrated Security is a more secure way to control access to a 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.

   An instance named DsAuthors1 appears in the component tray.

4. From the Data tab of the Toolbox, drag a DataSet onto the form.

   The Add Dataset dialog box appears.

5. Confirm that Typed dataset is selected and ConcurrencyWalkthrough.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

1. From the Windows Forms tab of the Toolbox, drag a Button control onto the from and place it above DataGrid1.
2. With the button selected, in the Properties window name the button btnUpdate and set its Text property to Update.
3. From the Windows Forms tab of the Toolbox, drag a second Button object onto the from and place it above DataGrid2.
4. 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:

  ' Visual Basic
  Private Sub resetDatabase()
     ' Fill the DsAuthors1 dataset with data.
     SqlDataAdapter1.Fill(DsAuthors1)
     ' Reset the au_fname in the first row to 'John'.
     DsAuthors1.authors(0).au_fname = "John"
     ' Write the record back to the database.
     SqlDataAdapter1.Update(DsAuthors1)
  End Sub
  
  // C#
  private void resetDatabase()
  {
     // Fill the dsAuthors dataset with data.
     sqlDataAdapter1.Fill(dsAuthors1);
     // Reset the au_fname in the first row to "John".
     dsAuthors1.authors[0].au_fname = "John";
     // Write the record back to the database.
     sqlDataAdapter1.Update(dsAuthors1);
  }

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:

  ' Visual Basic
  Private Sub FillTheDataSets()
     SqlDataAdapter1.Fill(DsAuthors1)
     SqlDataAdapter1.Fill(DsAuthors2)
  End Sub
  
  // C#
  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:

  ' Visual Basic
  Private Sub user2changes()
     ' Simulate a second user changing a record.
     DsAuthors2.authors(0).au_fname = "User 2"
     ' Write it back to the database.
     SqlDataAdapter1.Update(DsAuthors2.GetChanges())
     ' Refresh DsAuthors2 with the updated data.
     SqlDataAdapter1.Fill(DsAuthors2)
  End Sub
  
  // C#
  private void user2changes() 
  {
     // Simulate a second user changing a record.
     dsAuthors2.authors[0].au_fname = "User 2";
     // Write it back to the database.
     sqlDataAdapter1.Update(dsAuthors2.GetChanges());
     // Refresh dsAuthors2 with the updated data.
     sqlDataAdapter1.Fill(dsAuthors2);
  }

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:

   ' Visual Basic
   Private Sub Form1_Load(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles MyBase.Load
      resetDatabase()
      FillTheDataSets()
      user2changes()
   End Sub
   
   // C#
   private void Form1_Load(object sender, System.EventArgs e)
   {
      resetDatabase();
      FillTheDataSets();
      user2changes();
   }

3. Save your project.

Run the Application

1. 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:

   

2. 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:

' Visual Basic
Private Sub updateDatabase()
    Try
        ' Update the database with the changes from DsAuthors1.
        SqlDataAdapter1.Update(DsAuthors1.GetChanges)
        DsAuthors1.AcceptChanges()
        MessageBox.Show("The update was successful!")
    Catch ex As Exception
        ' Display information about update errors.
        MessageBox.Show("Update Failed", ex.GetType.ToString)
    End Try
End Sub

// C#
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("Update Failed", 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 the value was changed earlier by User 2, a concurrency error will be raised.

To update the DsAuthors1 dataset

1. Double-click the Update button.
2. Create the btnUpdate_Click event handler:

   ' Visual Basic
   Private Sub btnUpdate_Click(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles btnUpdate.Click
      ' Change au_fname in the first row of DsAuthors1 to User 1.
      DsAuthors1.authors(0).au_fname = "User 1"
      updateDatabase()
   End Sub
   
   // C#
   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.

   ' Visual Basic
   Private Sub updateDatabase()
       Try
           ' Update the database with the changes from dsAuthors1.
           SqlDataAdapter1.Update(DsAuthors1.GetChanges())
           DsAuthors1.AcceptChanges()
           MessageBox.Show("The update was successful!")
       Catch dbcx As DBConcurrencyException
           createMessage(dbcx)
       Catch ex As Exception
           MessageBox.Show("Update Failed", ex.GetType().ToString())
       End Try
   End Sub
   
   // C#
   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("Update Failed", 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 requery 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:

  ' Visual Basic
  Private Sub createMessage(ByVal dbcx As DBConcurrencyException)
     ' Declare variables to hold the row versions for display 
     ' in the message box.
     Dim strInDs As String = "Original record in DsAuthors1:" _
        & ControlChars.CrLf
     Dim strInDB As String = "Current record in database:" _
        & ControlChars.CrLf
     Dim strProposed As String = "Proposed change:" & ControlChars.CrLf
     Dim strPromptText As String = "Do you want to overwrite the current record in the database with the proposed change?" & ControlChars.CrLf
     Dim strMessage As String
     Dim response As System.Windows.Forms.DialogResult
  
  
     ' Locate the current record in the data source that caused the exception
     Dim rowInDB As DataRow = _
        DsAuthors2.authors.FindByau_id(CType(dbcx.Row("au_id"), String))
  
     ' Loop through the column values.  
     Dim i As Integer
     For i = 0 To dbcx.Row.ItemArray.Length - 1
        strInDs &= dbcx.Row(i, DataRowVersion.Original) & _
           ControlChars.Tab
        strInDB &= rowInDB(i, DataRowVersion.Current) & ControlChars.Tab
        strProposed &= dbcx.Row(i, DataRowVersion.Current) & _
           ControlChars.Tab
     Next
  
     ' Create the message box text string.
     strMessage = strInDs & ControlChars.CrLf
     strMessage &= strInDB & ControlChars.CrLf
     strMessage &= strProposed & ControlChars.CrLf
     strMessage &= strPromptText
        
     ' Display the message box.
     response = MessageBox.Show(strMessage, "Update Failed", _
        MessageBoxButtons.YesNo)
     processResponse(response)
  End Sub
  
  // C#
  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, "Update Failed", 
         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:

  ' Visual Basic
  Private Sub processResponse(ByVal response As _
  System.Windows.Forms.DialogResult)
     ' Execute the appropriate code depending on the button 
     ' selected in the message box.
     Select Case response
        Case System.Windows.Forms.DialogResult.Yes
           ' Overwrite the database with the proposed record.
           DsAuthors1.Merge(DsAuthors2, True)
           SqlDataAdapter1.Update(DsAuthors1)
           DsAuthors1.AcceptChanges()
        Case System.Windows.Forms.DialogResult.No
           ' Cancel proposed changes and refresh DsAuthors1.
           DsAuthors1.Merge(DsAuthors2)
     End Select
  End Sub
  
  // C#
  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 with Visual Basic

1. In the Code Editor, locate the Form_Load event handler.
2. Insert a comma and then btnReset.Click to the end of the handler to make the Form1_Load method the event handler for the Click event of btnReset.

   Private Sub Form1_Load(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles MyBase.Load, btnReset.Click
      resetDatabase()
      FillTheDataSets()
      user2changes()
   End Sub

To create an event handler for the btnReset button with Visual C#

1. Open the form in the designer.
2. Click the Reset button.
3. In the Properties window, click the Events button in the Properties window toolbar.
4. 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 the right signature for a button.click event.

5. 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.

   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.

See Also

C# Walkthroughs -Main Index