Page view counter

Implementing Optimistic Concurrency

This is the Visual C# tutorial    (Switch to the Visual Basic tutorial)

A web application that can have multiple users updating or deleting the same data at the same time risks having one user’s actions overwriting another user’s actions. In this tutorial we learn how to guard against this risk by implementing optimistic concurrency control when using a DataList to update and delete data.

Created by Scott Mitchell

Download the code for this tutorial   |   Download the tutorial in PDF format

Introduction

A web application that can have multiple users simultaneously updating or deleting data introduces the possibility of one user’s modifications overwriting another concurrent user’s. When designing such an application, it’s important to choose an appropriate concurrency control technique. As we discussed in the Implementing Optimistic Concurrency tutorial, there are three concurrency control strategies available:

  • Do Nothing —if concurrent users are modifying the same record, let the last commit win (the default behavior)
  • Optimistic Concurrency — assume that while there may be concurrency conflicts every now and then, the vast majority of the time such conflicts won’t arise; therefore, if a conflict does arise, simply inform the user that their changes can’t be saved because another user has modified the same data
  • Pessimistic Concurrency — assume that concurrency conflicts are commonplace and that users won’t tolerate being told their changes weren’t saved due to another user’s concurrent activity; therefore, when one user starts updating a record, lock it, thereby preventing any other users from editing or deleting that record until the user commits their modifications

All of our DataList editing tutorials thus far have used the default concurrency resolution strategy — namely, we’ve let the last write win. In this tutorial we’ll examine how to implement optimistic concurrency control.

Step 1: Understanding How Optimistic Concurrency is Implemented

Optimistic concurrency control works by ensuring that the record being updated or deleted has the same values as it did when the updating or deleting process started. For example, when clicking the Edit button in an editable DataList, the record’s values are read from the database and displayed in TextBoxes and other Web controls. These original values need to be persisted. Once the user completed his modifications and clicks the Update button, the original values are sent to the BLL and then the DAL to be compared with the current database values. The Data Access Layer issues a SQL statement that only updates the record if the original values that the user started editing are identical to the values still in the database. Figure 1 depicts this sequence of events.

For the Update or Delete to Succeed, the Original Values Must Be Equal to the Current Database Values

Figure 1: For the Update or Delete to Succeed, the Original Values Must Be Equal to the Current Database Values (Click to view full-size image)

There are various approaches to implementing optimistic concurrency (see Peter A. Bromberg’s Optmistic Concurrency Updating Logic for a brief look at a number of options). The ADO.NET Typed DataSet provides one implementation that can be configured with just the tick of a checkbox. Enabling optimistic concurrency for a TableAdapter in the Typed DataSet augments the TableAdapter’s UPDATE and DELETE statements to include a comparison of all of the original values in the WHERE clause. We created such a Typed DataSet in the Implementing Optimistic Concurrency tutorial (named NorthwindOptimisticConcurrency) along with a BLL class named ProductsOptimisticConcurrencyBLL, which was designed to work with the NorthwindOptimisticConcurrency Typed DataSet.

In this tutorial we’ll a DataList whose editing and deleting interface uses the optimistic concurrency-enabled BLL and DAL.

Note: If you’ve yet to work through the Implementing Optimistic Concurrency tutorial, please do so before continuing with this tutorial. The Implementing Optimistic Concurrency tutorial provides a more comprehensive overview of how optimistic concurrency works and walks through the process of creating the BLL and DAL to implement optimistic concurrency.

Step 2: Creating a DataList Whose Items Can Be Edited and Deleted

When implementing optimistic concurrency through a DataList, it is our responsibility to maintain the original values — those values present when updating or deleting — and to pass those original values along with the edit- or delete-related values to the Business Logic Layer. Before we concern ourselves with maintaining this information, let’s first focus on building a DataList whose items can be edited and deleted. In particular, let’s create a DataList that lists products and allows their name and price to be updated.

Start by opening the OptimisticConcurrency.aspx page in the EditDeleteDataList folder. Drag a DataList from the Toolbox onto the Designer and set is ID property to Products. From the DataList’s smart tag, create a new ObjectDataSource named ProductsDataSource that is configured to retrieve data from the ProductsBLL class’s GetProducts() method. As with our previous tutorials, be sure to set the drop-down lists in the ObjectDataSource’s UPDATE, INSERT, and DELETE tabs to (None).

Set the Drop-Down Lists to (None) in the UPDATE, INSERT, and DELETE tab

Figure 2: Set the Drop-Down Lists to (None) in the UPDATE, INSERT, and DELETE tab (Click to view full-size image)

After the ObjectDataSource has been configured, Visual Studio will create a default ItemTemplate for the DataList that includes each product data field’s name and value. Replace this ItemTemplate with one that shows just the product’s ProductName and UnitPrice fields.

To provide support for editing and deleting, we need to add two Buttons to the ItemTemplate — one to delete the product and one to make the product editable. Recall that these Buttons must have the CommandName values of “Edit” and “Delete” so that when clicked the DataList’s EditCommand and DeleteCommand events are fired upon postback.

My ItemTemplate displays the product’s name in an <h4> element and the price formatted as a currency; feel free to customize the ItemTemplate’s look and feel to your own taste.

Note: This tutorial does not step through the process of creating the DataList’s ItemTemplate or EditItemTemplate, but instead focuses on implementing optimistic concurrency. For a more detailed look at creating these two templates for the DataList, refer to the Displaying Data with the DataList and Repeater Controls and Overview of Editing and Deleting Data in the DataList tutorials.

<ItemTemplate> <h4> <asp:Label ID="ProductNameLabel" runat="server" Text='<%# Eval("ProductName") %>' /> </h4> Price: <asp:Label ID="UnitPriceLabel" runat="server" Text='<%# Eval("UnitPrice", "{0:C}") %>' /> <br /><br /> <asp:Button runat="server" ID="EditButton" Text="Edit" CommandName="Edit" />     <asp:Button runat="server" ID="DeleteButton" Text="Delete" CommandName="Delete" /> <br /><br /> </ItemTemplate>

When the Edit button is clicked, a postback occurs and the DataList’s EditCommand event fires. We’ll soon create an event handler for this event that will make the selected product editable, which causes that particular product’s EditItemTemplate to be rendered in place of its ItemTemplate. Therefore, we need to create an EditItemTemplate for the Products DataList. This EditItemTemplate should include a TextBox for the product’s name and price with a RequiredFieldValidator for the name and a CompareValidator to ensure that the price is a provided in a valid currency format and that its value, if provided, is greater than or equal to zero. Additionally, we need two Button controls — an Update and Cancel button — whose CommandName properties are set to “Update” and “Cancel”, respectively.

<EditItemTemplate> Product: <asp:TextBox runat="server" ID="ProductName" Text='<%# Eval("ProductName") %>' /> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="ProductName" ErrorMessage="You must provide the product's name." runat="server">*</asp:RequiredFieldValidator> <br /> Price: $<asp:TextBox runat="server" ID="UnitPrice" Text='<%# Eval("UnitPrice", "{0:N2}") %>' Columns="8" /> <asp:CompareValidator ID="CompareValidator1" ControlToValidate="UnitPrice" ErrorMessage="The product's price must be a valid currency value greater than or equal to zero. Do not include the currency symbol." Operator="GreaterThanEqual" Type="Currency" ValueToCompare="0" runat="server">*</asp:CompareValidator><br /><br /> <asp:Button ID="UpdateButton" Text="Update" CommandName="Update" runat="server" />     <asp:Button ID="CancelButton" Text="Cancel" CommandName="Cancel" CausesValidation="False" runat="server" /> <br /><br /> </EditItemTemplate>

Creating the EditCommand and CancelCommand Event Handlers

At this point we’ve created the DataList’s ItemTemplate and EditItemTemplate, but still need to craft the event handlers to toggle a product from read-only mode to editable mode and vice versa. As discussed in the Overview of Editing and Deleting Data in the DataList tutorial, the EditCommand event handler needs to set the DataList’s EditItemIndex property to the index of the product whose Edit button was clicked. Contrariwise, the CancelCommand event handler needs to reset the EditItemIndex to a value that does not correspond with any product index (such as -1).

protected void Products_EditCommand(object source, DataListCommandEventArgs e) { Products.EditItemIndex = e.Item.ItemIndex; Products.DataBind(); } protected void Products_CancelCommand(object source, DataListCommandEventArgs e) { Products.EditItemIndex = -1; Products.DataBind(); }

With these event handlers in place, take a moment to visit the page through a browser. Initially, all of the products’ names and prices should be displayed, as shown in Figure 3. Clicking the Edit button will render the selected product in its edit mode. At this point, the Update button does not do anything; the Cancel button, however, returns the DataList to its pre-editing state (see Figure 4).

Each Product’s Name and Price is Displayed

Figure 3: Each Product’s Name and Price is Displayed (Click to view full-size image)

Clicking the Edit Button Renders the Selected Product’s EditItemTemplate

Figure 4: Clicking the Edit Button Renders the Selected Product’s EditItemTemplate (Click to view full-size image)

Step 3: Remembering the Original Values When Editing

When editing a record using optimistic concurrency control, it is imperative that the original values of all of the editable fields be remembered so that when the user has completed their edits and clicks Update, the original values can be compared with the current database values to determine whether or not another user has modified the data. The GridView automatically remembers these original values when editing. With the DataList, however, we are responsible for writing code to remember these values.

The GridView remembers the original values across postbacks using its control state. As discussed in the Overview of Editing and Deleting Data in the DataList tutorial, control state is similar to view state in that it’s used to remember values across postback. Control state, however, cannot be disabled by the page developer, whereas view state can. For the DataList, we can manually add the original values to the ASP.NET page’s view state collection when binding data to the editable row in the ItemDataBound event handler. By placing these original values in the view state, their values will be remembered across postbacks. Later, when creating the UpdateCommand event handler, we’ll take these original values and pass them into the BLL, which will only update the product if the original values match up to the current database values.

To write to the page’s view state, use the Page class’s ViewState property. Create an event handler for the DataList’s ItemDataBound event so that when processing the editable item the original values are saved in view state:

protected void Products_ItemDataBound(object sender, DataListItemEventArgs e) { if (e.Item.ItemType == ListItemType.EditItem) { // Read the edited item's original values into the Page's view state TextBox ProductName = (TextBox)e.Item.FindControl("ProductName"); TextBox UnitPrice = (TextBox)e.Item.FindControl("UnitPrice"); ViewState["original_productName"] = ProductName.Text; ViewState["original_unitPrice"] = UnitPrice.Text; } }

The conditional at the beginning of this event handler only saves the original values for the item being edited. It then reads the values in the ProductName and UnitPrice TextBox Web controls from the edited product’s EditItemTemplate. The FindControl("controlID") method is used to reference the TextBoxes that hold the name and price values just retrieved from the database. These values are then stored in the view state variables original_productName and original_unitPrice.

Our DataList has only two editable inputs — the product’s name and price — and therefore we only need to use two view state variables. In general, for each editable item in the DataList, reference the applicable Web control in the EditItemTemplate using the FindControl("controlID") method and then store its value in a view state variable.

Step 4: Passing the Original and New Values to the UpdateProduct Method

When a user clicks the Edit button to update a product, the latest data is retrieved from the database and displayed in the selected product’s EditItemTemplate. These database values are stored in two view state variables. After the user has completed making his changes and clicks the Update button, a postback ensues and the DataList’s UpdateCommand event fires. We need to create an event handler for this event that reads the new product values from the EditItemTemplate and passes both the new and original values to the Business Logic Layer.

Back in the Implementing Optimistic Concurrency tutorial, we looked at how to create a Typed DataSet that inherently supported optimistic concurrency control and built such a DAL (NorthwindOptimisticConcurrency.xsd). In that tutorial we also created the ProductsOptimisticConcurrencyBLL class to work with the NorthwindOptimisticConcurrency.xsd DAL. To use optimistic concurrency control in our DataList example, we’ll interface directly with the ProductsOptimisticConcurrencyBLL class from the UpdateCommand event handler.

The ProductsOptimisticConcurrencyBLL class’s UpdateProduct method overloads expect both the new values and the original ones. Therefore, our UpdateCommand event handler needs to read in the updated value and reference the original values from view state, as the following code shows:

protected void Products_UpdateCommand(object source, DataListCommandEventArgs e) { // Make sure the validators on the page are valid if (!Page.IsValid) return; // Read in the ProductID int productID = Convert.ToInt32(Products.DataKeys[e.Item.ItemIndex]); // Read in the original values string original_productName = null; decimal? original_unitPrice = null; if (!string.IsNullOrEmpty((string)ViewState["original_productName"])) original_productName = (string)ViewState["original_productName"]; if (!string.IsNullOrEmpty((string)ViewState["original_unitPrice"])) original_unitPrice = decimal.Parse((string)ViewState["original_unitPrice"], System.Globalization.NumberStyles.Currency); // Read in the new values string new_productName = null; decimal? new_unitPrice = null; TextBox ProductName = (TextBox)e.Item.FindControl("ProductName"); TextBox UnitPrice = (TextBox)e.Item.FindControl("UnitPrice"); if (ProductName.Text.Trim().Length > 0) new_productName = ProductName.Text.Trim(); if (UnitPrice.Text.Trim().Length > 0) new_unitPrice = decimal.Parse(UnitPrice.Text.Trim(), System.Globalization.NumberStyles.Currency); // Call the UpdateProduct method in ProductsOptimisticConcurrencyBLL ProductsOptimisticConcurrencyBLL optimisticProductsAPI = new ProductsOptimisticConcurrencyBLL(); optimisticProductsAPI.UpdateProduct(new_productName, new_unitPrice, productID, original_productName, original_unitPrice, productID); // Return the DataList to its pre-editing state Products.EditItemIndex = -1; Products.DataBind(); }

The UpdateCommand event handler starts by ensuring that the page is valid. As we discussed in the Adding Validation Controls to the DataList’s Editing Interface tutorial, it’s prudent to make sure that Page.IsValid returns True before working with user-entered data since the client-side validation logic can be bypassed by users whose browsers do not include or have JavaScript support disabled.

Next, the edited row’s ProductID is read from the DataKeys collection. Following that, the original values are read from the ViewState collection into local variables and then the new values are accessed from the TextBoxes in the EditItemTemplate. Next, an instance of the ProductsOptimisticConcurrencyBLL class is created and the UpdateProduct method is invoked, passing in the original and new values. Finally, after the update completes, the DataList is returned to its pre-editing state.

With the UpdateCommand event handler in place, visit the page through a browser. Click the Edit button for Chai and change the price to $20.00. Since you are the only one editing this record, the update should succeed and the user experience should be identical to that when updating a DataList without optimistic concurrency control.

Open another browser window and point it to the OptimisticConcurrency.aspx web page. Click the Edit button for Chai in both browsers. In the first browser window, change the product name from “Chai” to “Chai Tea” and click Update. Doing so should update the database and return the DataList to its pre-editing state, showing the product name now as “Chai Tea”. In the second browser window, which should still be in Edit mode, change the price to $25.50, but leave the product name as “Chai”. Upon clicking Update, a DBConcurrencyException will be thrown, as shown in Figure 5.

When a Concurrency Violation is Detected, a DBConcurrencyException is Thrown

Figure 5: When a Concurrency Violation is Detected, a DBConcurrencyException is Thrown

As Figure 6 illustrates, this exception was thrown because the original values in the second browser window — “Chai” and $19.95 — did not match up to the current values in the database (a product name of “Chai Tea”). In the face of such a concurrency violation, the DAL throws a DBConcurrencyException.

The Original Values Did Not Match Up to the Current Database Values

Figure 6: The Original Values Did Not Match Up to the Current Database Values

When such an exception is raised, we should display a message to the user instructing them that their update was cancelled because of a concurrency violation. This can be accomplished by placing the call to UpdateProduct in a Try ... Catch block.

Start by adding a Label immediately above the DataList. Set its ID property to UpdateConcurrencyViolationMessage, its CssClass property to “Warning”, its Visible property to False, its EnableViewState property to False, and its Text property to, “The record you attempted to update has been modified by another user since you started the update process. Your changes have been replaced with the current values. Please review the existing values and make any needed changes.” The Warning CSS class, defined in Styles.css, causes the Label’s text to be displayed in a large, red, italicized font, as shown in Figure 7.

Add a Label Above the DataList

Figure 7: Add a Label Above the DataList (Click to view full-size image)

With the UpdateConcurrencyViolationMessage Label in place, all that remains is to add the Try ... Catch block to the UpdateCommand event handler. In particular, we need to place the call to the UpdateProduct method in the Try ... Catch block. If a DBConcurrencyException is raised, we want to display the UpdateConcurrencyViolationMessage Label and rebind the data to the DataList, so that the other user’s changes are displayed and become the new original values.

try { // Call the UpdateProduct method in ProductsOptimisticConcurrencyBLL ProductsOptimisticConcurrencyBLL optimisticProductsAPI = new ProductsOptimisticConcurrencyBLL(); optimisticProductsAPI.UpdateProduct(new_productName, new_unitPrice, productID, original_productName, original_unitPrice, productID); // Return the DataList to its pre-editing state Products.EditItemIndex = -1; Products.DataBind(); } catch (DBConcurrencyException) { // Display the UpdateConcurrencyViolationMessage Label UpdateConcurrencyViolationMessage.Visible = true; // Re-read the values from the database Products.DataBind(); } catch { // Some other kind of exception occurred throw; }

By placing the code to return the DataList to its pre-editing state within the Try block, I have caused the DataList to remain in edit mode if a DBConcurrencyException is raised. If, instead, you want to return the DataList to its pre-editing state, be sure to set the DataList’s EditItemIndex property back to -1 before calling Products.DataBind() in the catch block. If an exception other than a DBConcurrencyException is raised when attempting to update the product, the exception is re-thrown.

With the exception handling code in place, revisit the OptimisticConcurrency.aspx page in a browser and repeat the steps that previously resulted in a DBConcurrencyException message in the browser. This time, the concurrency violation yields a user-friendly message (see Figure 8).

When a Concurrency Violation Occurs, a Friendly Error Message is Displayed

Figure 8: When a Concurrency Violation Occurs, a Friendly Error Message is Displayed (Click to view full-size image)

Step 5: Implementing Optimistic Concurrency Control for Deletes

Optimistic concurrency control applies to deleting as well as updating. With optimistic concurrency control, when a user deletes a product, the original product values — those that were present when the data was read from the database and assigned to the DataList — are compared against the current database values. If there have been any changes, the delete fails (see Figure 9).

Deleting a Record Whose Data Has Changed Results in a Concurrency Violation

Figure 10: Deleting a Record Whose Data Has Changed Results in a Concurrency Violation

The ProductsOptimisticConcurrencyBLL class contains a DeleteProduct method that, in addition to the ProductID value, expects the original values of all of the other product data field values to be passed in (ProductName, CategoryID, SupplierID, and so on). If there are any changes in any of the original values and the current values in the database, the delete will fail. Since we’re only displaying the product’s name and price in our web page, though, do we want the delete to fail whenever any product value has been changed, or only if the product name or price has been changed since the data was bound to the DataList?

The answer to this question depends on what you are expecting from optimistic concurrency control. Do you want to stop the user from deleting a record only if one of the displayed values has changed (since the user’s decision to delete a record might be due to its displayed values), or do you want to have the delete fail if there’s been any modification to the record? For me, the former approach seems more logical.

Regardless of the approach taken, we need to first add a Label Web control to the page to display an explanation if there is a concurrency violation when deleting. Add a new Label Web control and set its ID to DeleteConcurrencyViolationMessage. As with the UpdateConcurrencyViolationMessage Label, set the DeleteConcurrencyViolationMessage Label’s CssClass property to “Warning” and its EnableViewState and Visible properties to False. Lastly, set its Text property to, “The record you attempted to delete has been modified or deleted by another user since you visited the page. Your delete was cancelled to allow you to review the other user's changes and determine if you want to continue deleting this record.” After adding this Label, the Designer should look similar to Figure 10.

An Additional Label Has Been Added to the Page

Figure 11: An Additional Label Has Been Added to the Page (Click to view full-size image)

To pursue the “stop the delete only if one of the displayed values has been changed by another user” choice we need to create a DeleteProduct overload that accepts just the original ProductName and UnitPrice values. The DAL’s Delete method, however, expects the original values for all of the product’s data fields. Therefore, we need to read in the current data from the database and pass the DAL’s Delete method the original values for those fields displayed in the web page and the current database values for the other data fields.

Add the following DeleteProduct method overload to the ProductsOptimisticConcurrencyBLL class:

[System.ComponentModel.DataObjectMethodAttribute (System.ComponentModel.DataObjectMethodType.Delete, false)] public bool DeleteProduct(int original_productID, string original_productName, decimal? original_unitPrice) { // Read in the current values from the database NorthwindOptimisticConcurrency.ProductsOptimisticConcurrencyDataTable products = Adapter.GetProductByProductID(original_productID); if (products.Count == 0) // no matching record found, return false return false; NorthwindOptimisticConcurrency.ProductsOptimisticConcurrencyRow product = products[0]; int? current_supplierID = null; int? current_categoryID = null; string current_quantityPerUnit = null; short? current_unitsInStock = null; short? current_unitsOnOrder = null; short? current_reorderLevel = null; bool current_discontinued; if (!product.IsSupplierIDNull()) current_supplierID = product.SupplierID; if (!product.IsCategoryIDNull()) current_categoryID = product.CategoryID; if (!product.IsQuantityPerUnitNull()) current_quantityPerUnit = product.QuantityPerUnit; if (!product.IsUnitsInStockNull()) current_unitsInStock = product.UnitsInStock; if (!product.IsUnitsOnOrderNull()) current_unitsOnOrder = product.UnitsOnOrder; if (!product.IsReorderLevelNull()) current_reorderLevel = product.ReorderLevel; current_discontinued = product.Discontinued; // Now, call the Delete method, passing in the original and current values, // where appropriate int rowsAffected = Adapter.Delete(original_productID, original_productName, current_supplierID, current_categoryID, current_quantityPerUnit, original_unitPrice, current_unitsInStock, current_unitsOnOrder, current_reorderLevel, current_discontinued); // Return true if precisely one row was deleted, otherwise false return rowsAffected == 1; }

This method starts by reading in the current values from the database and assigning all but the ProductName and UnitPrice values to local variables. Next, the DAL’s Delete method is called, passing in the original values for the product’s name and price, and the current values for all other rows.

Recall that the TableAdapters in a Typed DataSet provide two patterns for modifying their contents:

  • The Batch Update Pattern — a single DataRow, a collection of DataRows, a DataTable, or DataSet is passed into the TableAdapter’s Update method. The Update method enumerates the set of DataRows received and performs the appropriate UPDATE, INSERT, and DELETE statements depending on the DataRow’s RowState property. The UpdateProduct overloads use the Batch Update pattern.
  • The DB-Direct Pattern — scalar values are passed into the TableAdapter’s Insert, Update, or Delete methods, and a single database record is affected. The DeleteProduct method uses the DB-Direct pattern.

The reason I bring up these different TableAdapter patterns is because each handles optimistic concurrency control differently. As we saw in step 4, when there is a concurrency violation using the Batch Update pattern, a DBConcurrencyException is thrown. With the DB-Direct pattern, no exception is thrown — the update or delete fails silently. To ascertain whether a concurrency violation occurred, we can consult the return value of the Update or Delete method, which indicates how many rows were affected. If no rows were affected, it may have been because of a concurrency violation. Alternatively, it may have been because the record attempting to be updated or deleted has already been deleted or has had its primary key value changed.

The final step is to create an event handler for the DataList’s DeleteCommand event. In this event handler, we need to read in the ProductID, ProductName, and UnitPrice values, and call the ProductsOptimisticConcurrencyBLL class’s DeleteProduct method. If the DeleteProduct method returns false, indicating that no records were deleted, we need to display the DeleteConcurrencyViolationMessage Label. Regardless of whether the delete failed or succeeded, we need to rebind the data to the Products DataList. The following code accomplishes these objectives:

protected void Products_DeleteCommand(object source, DataListCommandEventArgs e) { // Read in the ProductID int productID = Convert.ToInt32(Products.DataKeys[e.Item.ItemIndex]); // Read in the current name and price values for the product being deleted Label ProductNameLabel = (Label)e.Item.FindControl("ProductNameLabel"); Label UnitPriceLabel = (Label)e.Item.FindControl("UnitPriceLabel"); string original_productName = ProductNameLabel.Text; decimal? original_unitPrice = null; if (!string.IsNullOrEmpty(UnitPriceLabel.Text)) original_unitPrice = decimal.Parse(UnitPriceLabel.Text, System.Globalization.NumberStyles.Currency); // Delete the product using the ProductsOptimisticConcurrencyBLL class ProductsOptimisticConcurrencyBLL optimisticProductsAPI = new ProductsOptimisticConcurrencyBLL(); bool deleteSucceeded = optimisticProductsAPI.DeleteProduct (productID, original_productName, original_unitPrice); // If the delete failed, display the // DeleteConcurrencyViolationMessage Label if (!deleteSucceeded) DeleteConcurrencyViolationMessage.Visible = true; // Rebind the data to the DataList Products.DataBind(); }

To test out the optimistic concurrency functionality for deletes, open two browser windows and point both to the OptimisticConcurrency.aspx page. In one browser window, edit the Chai product, changing its name to “Chai Tea”. After saving the change, attempt to delete Chai in the second browser window. The DeleteCommand event handler will pass in the original product name (“Chai”) into the DAL’s Delete method. The SQL DELETE statement executed by the DAL’s Delete method won’t affect any rows because it is written such that it only deletes a row where the original values passed in match up to the current database values. The current product name, however, is “Chai Tea”, hence the delete will fail silently. The BLL’s DeleteProduct method will return false, since no rows were affected.

Back in the DeleteCommand event handler, a return value of false causes the DeleteConcurrencyViolationMessage Label to be displayed, displaying the friendly error message shown in Figure 11.

If the Delete Does Not Affect Any Rows, a Explanation Message is Displayed

Figure 11: If the Delete Does Not Affect Any Rows, a Explanation Message is Displayed (Click to view full-size image)

Summary

When building a web application where you expect multiple, concurrent users to be editing and deleting the same database records, it may be worthwhile to implement optimistic concurrency. As we saw in a previous tutorial, the ADO.NET TableAdapters can be configured to support optimistic concurrency and, depending on the update pattern used, will either throw a DBConcurrencyException or fail silently in the face of a concurrency violation.

To include optimistic concurrency support in an editable DataList, we need to take steps to remember the original values across postbacks. Such information can be saved to the page’s ViewState collection in the ItemDataBound event handler. Then, in the UpdateCommand event handler, this original data can be sent along with the new values to the Business Logic Layer. Similarly, to provide optimistic concurrency support when deleting, the original values bound to the DataList must be read and sent to the BLL from the DeleteCommand event handler.

Happy Programming!

About the Author

Scott Mitchell, author of seven ASP/ASP.NET books and founder of 4GuysFromRolla.com, has been working with Microsoft Web technologies since 1998. Scott works as an independent consultant, trainer, and writer. His latest book is Sams Teach Yourself ASP.NET 2.0 in 24 Hours. He can be reached at mitchell@4GuysFromRolla.com. or via his blog, which can be found at http://ScottOnWriting.NET.

Special Thanks To…

This tutorial series was reviewed by many helpful reviewers. Lead reviewer for this tutorial was Ken Pespisa. Interested in reviewing my upcoming MSDN articles? If so, drop me a line at mitchell@4GuysFromRolla.com.

Next Tutorial

Visual C# Tutorials

(Switch to Visual Basic tutorials)

Scott’s Book

This tutorial was written by Scott Mitchell who has also written the following book on learning ASP.NET.

»  More Books

Microsoft Communities