N-Layered Web Applications with ASP.NET 3.5 Part 5: Dealing with Concurrency

After you’ve read the previous series, be sure to check out part one as well as it describes the new application’s architecture. Additionally, you may want to read the earlier parts that deal with Validation, Sorting, Paging and Filtering. 

Quick Links to All Articles

Previous Series
Building Layered Web Applications with Microsoft ASP.NET 2.0 - Part 1
Building Layered Web Applications with Microsoft ASP.NET 2.0 - Part 2
Building Layered Web Applications with Microsoft ASP.NET 2.0 - Part 3
Custom Sorting with N-Layer Design Classes and the GridView

Current Series
N-Layered Web Applications with ASP.NET 3.5 Part 1: General Introduction
N-Layered Web Applications with ASP.NET 3.5 Part 2: Introducing the Validation Framework
N-Layered Web Applications with ASP.NET 3.5 Part 3: Advanced Validation Topics
N-Layered Web Applications with ASP.NET 3.5 Part 4: Sorting, Paging and Filtering
N-Layered Web Applications with ASP.NET 3.5 Part 5: Dealing with Concurrency
N-Layered Web Applications with ASP.NET 3.5 Part 6: Security

Many web sites that I have seen (and built) don’t do much to handle concurrency issues. That is, they don’t provide support for situations where two users try to update the same data at the same time. Depending on the use of the application and number of users your site has, this may or may not be a problem. In case you’re not aware of concurrency issues, consider the following example:

1. User Tom gets a list of addresses for a specific Contact Person. He clicks Edit on one of the addresses to put it in Edit mode.
2. User Jacqueline gets the same address record, and puts it in Edit mode as well.
3. Tom changes the Zip code of the address and saves the changes.
4. Jacqueline changes the city and saves her changes as well.

Because in step two Jacqueline got a local copy of the address record, stored in the form’s edit fields, she effectively overwrites the changes made by Tom as soon as she updates the record in step 4. That is, the Edit form still contained the old zip code she got from the database. When she saved the form, she sent the old zip code, completely ignoring the change made by Tom.

So how do you handle these situations? In some cases, it’s acceptable to simply ignore it. On a low-traffic site, or in an application where it’s unlikely that two users try to modify the same record, the risk of failure and the costs associated with it are lower than the efforts it takes to implement concurrency checks. However in other cases, it’s important to have a good concurrency strategy.

In the following sections, I’ll detail the changes you need to make to implement concurrency checks at different levels of the application, including:

1. The database – including changes to tables and stored procedures
2. The BusinessEntities, Bll and Dal namespaces – including changes to the BusinessBase and the various *DB classes.
3. The User Interface – which must now know how to deal with concurrency exceptions.

Handling Concurrency at the Database Level

To implement concurrency handling in your database, you have a few options. One common technique is to keep track of the date a row was last modified. When you update a row, you pass in the date the row was last modified and use it in the WHERE clause. If the datetime field matches, you know the row hasn’t been modified. But if no rows were updated, you know your WHERE clause didn’t match as someone else has updated the row in the mean time. Take a look at the following T-SQL statement to see how this would work:

	UPDATE
  ContactPerson
SET
  FirstName = @firstName
  …
  UpdateDateTime = GetDate()
WHERE
 Id = @id
  AND UpdateDateTime = @updateDateTime

While easy to implement, this solution has one big drawback: it only works for updates you explicitly make through T-SQL code. So if you make a change using something like SQL Server Management Studio or through SQL statements that don’t take this mechanism into account, the UpdateDateTime won’t be updated and your concurrency check might fail. (Note: you could work around this using triggers that update the UpdateDateTime after a row has been updated)

If you’re using SQL Server, there’s a much easier solution available: using a timestamp column (other database systems have similar functionality). Unlike its name seems to suggest, a timestamp doesn’t really have anything to do with the time. It’s a unique version number for a row that is updated automatically by SQL Server every time you make a change to a row. To avoid the confusion, the timestamp column is now called rowversion in SQL Server 2005 and later so you’re advised to use that instead in your T-SQL scripts. Whenever you need to update a row from within the Data Access Layer, you pass in the value of this timestamp column and then use it in the WHERE clause. In the sample application, I gave each table in the database a timestamp column and called it ConcurrencyId. Stored procedures that update a row in the database then take this column into account as follows:

	CREATE PROCEDURE sprocContactPersonInsertUpdateSingleItem
  
  @id int OUTPUT,
  …
  @concurrencyId rowversion OUTPUT

AS
  …
  UPDATE ContactPerson SET
    FirstName = @firstName,
    …
  WHERE 
    Id = @id
    AND ConcurrencyId = @concurrencyId

  SELECT @ReturnValue = @id
    …

  SET @concurrencyId = (SELECT ConcurrencyId FROM ContactPerson 
             WHERE Id = @ReturnValue)
  …

This procedure has a parameter called @concurrencyId which contains the rowversion which was retrieved when the row was initially loaded (using GetList or GetItem methods). This value is then used in the WHERE clause for the UPDATE statement. In the end, the @concurrencyId parameter is filled with the rowversion for the row. When the update was applied successfully, this means that @concurrencyId now contains a new version number. When the update didn’t succeed, nothing was updated and @concurrency will contain the row version that the row had before the update attempt.

Handling Concurrency in the Bll and Dal Namespaces

Now that you have seen a brief overview of handling concurrency at the database level, it’s time to take a look at the implementation of it in the Contact Manager Application used in this article series.

The implementation of the concurrency model in the Contact Manager Application affects a number of classes in the BusinessEntities and Dal namespaces, including:

1. BusinessBase in the BusinessEntities namespace.
2. The individual data access classes in the Dal namespace.

The following sections look at each of these changes in detail.

Changing the BusinessBase Class

The BusinessBase class has been extended with a ConcurrencyId property of type byte[] (the .NET counterpart of the rowversion in SQL Server):

	public abstract class BusinessBase : ValidationBase
{
  … 
  public byte[] ConcurrencyId { get; set; }
}

Since all main business entities in the BusinessEnities namespace inherit from BusinessBase, they all get this new property for free.

Changing the Individual DAL Classes

The next step is loading an existing concurrency ID in a business entity when it’s constructed from the database data. Each SELECT stored procedure (the SelectSingleItem and SelectList versions) now includes the ConcurrencyId column in the SELECT statement which is then loaded in FillDataRecord in the corresponding DAL classes. For example, FillDataRecord in EmailAddressDB now looks like this:

	private static EmailAddress FillDataRecord(IDataRecord myDataRecord)
{
  EmailAddress myEmailAddress = new EmailAddress();
  …
  myEmailAddress.ConcurrencyId = (byte[])myDataRecord.GetValue(
            myDataRecord.GetOrdinal("ConcurrencyId"));
  return myEmailAddress;
}

Now that the data is loaded, the next important step is to pass it to the database when the business entity is updated. This is done in the new SetSaveParameters in the database Helpers class that sets up parameters shared by all business entities:

	internal static void SetSaveParameters(
               SqlCommand command, BusinessBase businessBase)
{
  … // other parameters are set here

  DbParameter rowVersion = command.CreateParameter();
  rowVersion.ParameterName = concurrencyParamName;
  rowVersion.Direction = ParameterDirection.InputOutput;
  rowVersion.DbType = DbType.Binary;
  rowVersion.Size = 8;

  if (businessBase.ConcurrencyId == null)
  {
    rowVersion.Value = DBNull.Value;
  }
  else
  {
    rowVersion.Value = businessBase.ConcurrencyId;
  }
  command.Parameters.Add(rowVersion);

  …
}

This method is called by all business entities that implement concurrency checks. Therefore, the method accepts a BusinessBase instance so it can be reused across all entities.

Another change I made in the Save methods of the business entities is the way I check the return value of ExecuteNonQuery. Previously, it simply ignored this value, but it’s now used to determine whether the update succeeded or not:

	myConnection.Open();
int numberOfRecordsAffected = myCommand.ExecuteNonQuery();
if (numberOfRecordsAffected == 0)
{
  throw new DBConcurrencyException(@"Can't update e-mail address as it has 
            been updated by someone else");
}

When numberOfRecordsAffected is zero, it means the stored procedure didn’t update any rows because the rowversion or the Id passed in didn’t match the existing one stored in the database. In that case, the code throws a DBConcurrencyException which can be handled in the UI.

Now that you’ve seen the require changes in the various business and Data Access Layers, the next thing you need to look at is the way concurrency is dealt with in the Presentation Layer.

Handling Concurrency in the UI of the Contact Manager Application

The first step in handling the concurrency problem in the UI is catching the exception that might be thrown by the DAL classes. The AddEditContactPerson.aspx page has the following code that gets executed when you try to save a contact person:

	if (myContactPerson.Validate()) 
{ 
  try
  { 
    ContactPersonManager.Save(myContactPerson);
    EndEditing();
  } 
  catch (DBConcurrencyException) 
  { 
    plcConcurrency.Visible = true;
  } 
}

Pretty straight forward. Simply catch a DBConcurrencyException and you’re done. The real work takes place in the PlaceHolder control that is shown when the error occurs. The control looks like this:

	<asp:PlaceHolder ID="plcConcurrency" runat="server" Visible="false">
  <div class="ErrorMessage">An error occurred while updating this 
      contact person. Another user has modified the record. 
      What would you like to do?<br />
  </div>
  <asp:Button ID="btnOverwrite" runat="server" onclick="btnOverwrite_Click"
          Text="Save changes anyway" />
  <asp:Button ID="btnReload" runat="server" onclick="btnReload_Click" 
          Text="Reload data from database (overwrites your changes)" />
</asp:PlaceHolder>

The user gets two options here: overwrite the changes in the database made by another user, or undo the changes the user made and reload the data from the database.

The code for each option is remarkably simple. Here’s the code for the Overwrite button:

	protected void btnOverwrite_Click(object sender, EventArgs e) 
{ 
  ContactPerson myContactPerson = ContactPersonManager.GetItem(contactPersonId);
  ViewState["ConcurrencyId"] = myContactPerson.ConcurrencyId;
  SaveContactPerson();
} 

All you need to do is get the latest version of the contact person from the database and store its ConcurrencyId in ViewState so it’s available in the SaveContactPerson method. SaveContactPerson carries out the necessary validation as usual and then calls a helper method that populates a ContactPerson from control- and ViewState like this:

	private void LoadContactPersonFromFormControls(ContactPerson myContactPerson) 
{ 
  myContactPerson.FirstName = txtFirstName.Text;
  myContactPerson.MiddleName = txtMiddleName.Text;
  myContactPerson.LastName = txtLastName.Text;
  myContactPerson.DateOfBirth = calDateOfBirth.SelectedDate;
  myContactPerson.Type = (PersonType)Convert.ToInt32(lstType.SelectedValue);
  myContactPerson.ConcurrencyId = (byte[])ViewState["ConcurrencyId"];
} 

When the user clicks the Overwrite button, the ConcurrencyId of the *new* record in the database is stored in ViewState. Consequently, this ID is then used to reconstruct the ContactPerson before it’s saved. The database then sees that the record it must save matches the latest version in the ContactPerson table and happily applies the update.

The Reload button is even simpler:

	protected void btnReload_Click(object sender, EventArgs e) 
{ 
  ContactPerson myContactPerson = ContactPersonManager.GetItem(contactPersonId);
  LoadFormControlsFromContactPerson(myContactPerson);
} 

This code gets a fresh ContactPerson from the database and then repopulates the form controls with the latest data. After that, the user can make any changes she sees fit and then update the record in the database by clicking the standard Save button.

To simulate a concurrency problem and see how this works in a real-world example page, carry out the following steps:

1. Browse to the Contact Manager Application, If you have the solution open in Visual Studio / Visual Web Developer, make sure the web site is the startup project and hit F5
2. In the list of contacts, click Edit to edit an existing contact person. Don’t make any changes to the contact yet, and don’t save the record.
3. Switch back to Visual Studio, open the ContactPerson table in the database using the Database Explorer / Server Explorer window and then make a change to the person you’re editing in the web environment. For example, change the person’s first name.
4. Go back to the web page and click the Save link. You’ll now see the two options appear; allowing you chose what to do with the data.

Because the AddEditContactPerson.aspx page is all hand coded, this concurrency behavior is pretty easy to implement. You can, however, apply the same principles to the lists of contact data like addresses and phone numbers. The Default.aspx page demonstrates this behavior for the Addresses collections. Note that I didn’t implement it for the other two collections; instead you’ll get a Yellow Screen of Death error when a concurrency conflict occurs. I won’t go into the code in full details, but this is globally how the page works:

1. Right below the FormView that allows you to enter new addresses I added a PlaceHolder control similar to the one in the AddEditContactPerson.aspx page.
    
2. In gvAddresses_RowUpdating (that is fired before the actual item in the GridView is updated) I check if ViewState contains a value for the AddressConcurrenceId key. If it does, it means a concurrency occurred earlier and the user decided to overwrite the changes in the database. In that case, I assign e.NewValues["ConcurrencyId"] the value of the concurrency key in ViewState.
    
3. In gvAddresses_RowUpdated, that is fired after the record has been updated, I check for a concurrency error with the following code:

   			if (e.Exception != null && 
             e.Exception.InnerException is DBConcurrencyException)
   {
     e.ExceptionHandled = true;
     e.KeepInEditMode = true;
     plcConcurrency.Visible = true;
   }
   else
   {
     plcConcurrency.Visible = false;
   }
   

4. The final step is handling the Click event of the two buttons. Reloading the data (rejecting the changes the user made) is easy: simply rebind the GridView:

   			protected void btnReload_Click(object sender, EventArgs e)
   {
     gvAddresses.DataBind();
   }

   The other option, overwriting the changes in the database and ignoring the changes made by another user looks like this:

   			protected void btnOverwrite_Click(object sender, EventArgs e)
   {
     Address myAddress = 
          AddressManager.GetItem(
              (int)gvAddresses.DataKeys[gvAddresses.EditIndex].Values[0]);
     ViewState["AddressConcurrenceId"] = myAddress.ConcurrencyId;
     gvAddresses.UpdateRow(gvAddresses.EditIndex, true);
     gvAddresses.EditIndex = -1;
   }

   This code gets the latest address from the database, stores the value of the ConcurrencyId in ViewState (which is used later in the RowUpdating event explained in step 2) and then manually calls UpdateRow to force an update of the data held in the editable row of the GridView. When the update is done, the code sets EditIndex to -1 to take the GridView out of edit mode. UpdateRow in turn causes RowUpdating to fire which retrieves the concurrency ID from ViewState, assigns it to the Address instance which then uses it to successfully update the data in the database.

With the example I have shown here, it’s pretty easy to implement concurrency in your web application. Yes, it requires some work in the different layers of the application, but in most cases, it will be well worth the cost. Once you get the hang of it, implementing concurrency in your own business entities may be done in just a couple of minutes. Time well spent if it helps you to prevent users from overwriting each other’s changes.

Summary

In this part of the article series on N-Layer design you saw how to deal with concurrency, in order to prevent two users from overwriting each other’s changes.

Dealing with concurrency is often skipped in many real-world applications because many developers either don’t give it enough thought or think it’s too complicated to implement and not worth the cost.

However, with the concepts I have demonstrated in this article, it’s easy to implement concurrency behavior at a low cost and with the smallest impact on the code and its performance.

In the final part of this long running article series I’ll deal with another multi-user issue: security. How can you stop unauthenticated users from entering the system and how can you make sure only authorized users can make changes to your system and its underlying database. Stay tuned.

Downloads

• Full source for this application in C#
• Full source for this application in VB.NET