Author: Will Shaver
Editors: Neil Vance, Katy Kaiser and friends
Date: July 30th, 2007
Example Files: TimestampConcurrency.exe Self Extracting / TimestampConcurrency.rar
Introduction
In this tutorial I’ll be covering how to implement optimistic concurrency with the use of a timestamp field. This tutorial serves as an adjunct to the existing (and wonderful) tutorial series written by Scott Mitchell.
I use quite a bit of the same code and concepts from the asp.net data access tutorial series so I highly recommend you read up on them before continuing here. The tutorials that you should pay extra attention to are:
- Creating a Data Access Layer
- Creating a Business Logic Layer
- Displaying Data with the ObjectDataSource
- Implementing Optimistic Concurrency
As Scott does in his tutorials, I’m using the Northwind sample database in the App_Data
folder for this tutorial. As my Visual Basic coding skills are sub-par, this tutorial is only available in C#. I’m not going to cover everything discussed in the above tutorials, instead I’ll focus on the changes needed to implement optimistic concurrency using timestamps.
The basic concept for implementing optimistic concurrency with a timestamp is to pass it to the client with the remainder of the editable data, and then when the round-trip is complete, to check it against the current version of the timestamp value in the database. The main advantage of using the timestamp instead of the default implementation of optimistic concurrency is that comparing against all fields tends to be costly on resources and page weight. This cost can be even higher if there are fields of type text
or varchar(max)
.
The timestamp field is somewhat of a misnomer in that it doesn’t have anything to do with time (or stamps), and everything to do with version control. The actual data stored in the timestamp field cannot be converted to a date, as it is much closer to a simple integer. The timestamp field is imbued with the following special properties:
- Read Only
- Guaranteed unique across the entire database
- Automatically updated to a new value every time any field in the row is changed
- The field can have any name, including timestamp
timestamp
sometimes goes by the ninja-namerowversion
, but the table definition still usestimestamp
- Adds 3-5 cold damage per attack
The default ObjectDataSource
implementation of optimistic concurrency doesn’t (easily) allow for comparing only the timestamp field on update, instead the implementation of the ConflictDetection property only allows for OverwriteChanges
or CompareAllValues
. Ideally we’d like a third option called something like CompareTimestamp
. (I tried to implement one with Lutz’s reflector, but the use of sealed and internal types made it impossible to inherit from ObjectDataSource
and ObjectDataSourceView
properly.)
Enough introduction, on with the tutorial!
Step 1: Setting up the Database
Create a new website in Visual Studio. (I’m putting mine in C:\TimestampConcurrency
.) Add the Northwind.MDF
files to the App_Data
folder. Add the App_Code
folder to your website. (You did read the tutorials I mentioned above right? I told you I’m not covering the easy stuff, the first tutorial linked above goes over all this.)

Figure 1: Open the Table Definition for the Products Table
Open your newly installed database in the server explorer. Right click on the Products table and select Open Table Definition. This will display a list of fields with Column Name, Data Type, and Allow Nulls options for each. Add a field called timestamp
with the Data Type of timestamp
, and make sure that Allow Nulls is not checked.

Figure 2: Adding a Timestamp Field to Your Products Table
Save your changes and close the table. You will note that if you right click on the table now and choose Show Table Data it lists the timestamp field as <Binary data>
which isn’t too helpful. If you’d like to see this data, you can with the following select statement:
select convert(int, timestamp) as version from products
Note that it actually should be a bigint
, but Visual Studio and SQL Server Management Studio are bugged on the conversion of bigint
and some other data types. (The query still works, the display doesn’t.) I encourage you to vote for fixing this bug, but it won’t prevent us from implementing timestamp
concurrency, so read on.

Figure 3: A Bug with Conversion of the BIGINT
Type Won’t Slow Us Down Too Much
Step 2: Implement the Data Access Layer with an XSD file
Add a DataSet called Northwind.xsd
to your App_Code
folder. Have it save the connection string to your Northwind database, and select Use SQL Statements. This method also works fine with stored procedures if you’d like to go that route, but we’ll keep it simple with standard SQL statements. For your query, use the famous Select * from Products
and be sure to check all three options in the advanced settings.

Figure 4: Setting up the SQL Statements
Name the queries FillProducts
and GetProducts
on the next screen, and then finish the wizard. Now add a second SQL statement query to your ProductsTableAdapter
by right clicking on it and choosing Add Query. Use Select * from Products where ProductID = @ProductID
as your query. Name the functions FillByProductID
and GetProductByProductID
, exactly as in the standard asp.net tutorials. Save your finished Northwind.xsd
file.
Step 3: Examine the Generated Queries
This step isn’t really necessary, but helps explain what’s going on behind the scenes. Right click anywhere on your xsd file and choose View Code.

Figure 5: Open the XML for the Northwind.xsd File
After opening the code, take a minute to scroll through it, enjoying all the code that you weren’t forced to write. If everything went as planned, there will be five different SQL queries listed in this file. (Insert, Delete, Update, Select, Select by ProductID.) Find the <DeleteCommand>
element and review its <CommandText>
. At the end of the query should be a WHERE
clause, with the timestamp
field automatically included as AND ([timestamp] = @Original_timestamp)
. The <UpdateCommand>
has the exact same ending: the current timestamp is checked against the one that was current when editing began.

Figure 6: The Update Command Query
Step 4: Create the Business Logic Layer
Grab the completed ProductsBLL.cs
file directly from the Creating a Business Logic Layer tutorial. There are a couple of minor modifications that need to happen as follows: First, delete the GetProductsByCategoryID
and GetProductsBySupplierID
functions. You’re welcome to implement them on your own, but they aren’t critical to optimistic concurrency so I’m leaving them out. Next get rid of the business rule in the UpdateProduct
function regarding discontinued products. Business rules are fine, but again, aren’t critical to this tutorial.
In the DeleteProduct
method you’ll note that the Adapter.Delete
method is now requiring two different parameters. A productID
is needed to specify which row to delete, and a byte array called Original_timestamp
. Remember that the timestamp is needed because it is now a parameter in the delete statement we inspected above. The .Net Framework treats timestamps as byte arrays, so we’ll add a byte array called timestamp to our method, and pass it into the Adapter.Delete
method.
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
public bool DeleteProduct(int productID, byte[] timestamp)
{
int rowsAffected = Adapter.Delete(productID, timestamp);
// Return true if precisely one row was deleted, otherwise false
return rowsAffected == 1;
}
In the UpdateProduct
method we’ll also add in our timestamp variable. Then we’ll use the built in DataRow
concurrency check features outlined in tutorial 21 on optimistic concurrency. The good news is that instead of assigning every original field into the old version of the DataRow
as is necessary when using standard optimistic concurrency, only the version needs to be compared. Add the timestamp to the method parameters, and the two lines for assigning the timestamp and accepting changes.
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
public bool UpdateProduct(string productName, int? supplierID, int? categoryID, string quantityPerUnit,
decimal? unitPrice, short? unitsInStock, short? unitsOnOrder, short? reorderLevel,
bool discontinued, int productID, byte [] timestamp)
{
Northwind.ProductsDataTable products = Adapter.GetProductByProductID(productID);
if (products.Count == 0)
// no matching record found, return false
return false;
Northwind.ProductsRow product = products[0];
product.timestamp = timestamp;
product.AcceptChanges();
product.ProductName = productName;
if (supplierID == null) product.SetSupplierIDNull(); else product.SupplierID = supplierID.Value;
if (categoryID == null) product.SetCategoryIDNull(); else product.CategoryID = categoryID.Value;
if (quantityPerUnit == null) product.SetQuantityPerUnitNull(); else product.QuantityPerUnit = quantityPerUnit;
if (unitPrice == null) product.SetUnitPriceNull(); else product.UnitPrice = unitPrice.Value;
if (unitsInStock == null) product.SetUnitsInStockNull(); else product.UnitsInStock = unitsInStock.Value;
if (unitsOnOrder == null) product.SetUnitsOnOrderNull(); else product.UnitsOnOrder = unitsOnOrder.Value;
if (reorderLevel == null) product.SetReorderLevelNull(); else product.ReorderLevel = reorderLevel.Value;
product.Discontinued = discontinued;
// Update the product record
int rowsAffected = Adapter.Update(product);
// Return true if precisely one row was updated, otherwise false
return rowsAffected == 1;
}
Now save your ProductsBLL.cs
file and build the project. Fix any errors before continuing onto step 5.
Step 5: Configuring your ObjectDataSource and GridView for Deleting Rows
To use this beautiful Business Logic Layer that we’ve created, we’ll need to add an ObjectDataSource
to our page. Find the Default.aspx
file and open it in design view. Drag and drop from the toolbox an ObjectDataSource
and a GridView
. Click on the smart tag for your ObjectDataSource
and select Configure Data Source, and then choose our ProductsBLL from the drop down list.

Figure 7: Configure your ObjectDataSource
Remove the original_
from the ObjectDataSource
‘s OldValuesParementerFormatString
property. Leave ConflictDetection
on OverwriteChanges
. If we leave the original_
in the list, the ObjectDataSource
will have the wrong number of parameters for the ProductBLL
class’s delete and update methods.

Figure 8: Remove the original_ prefix
Now use the GridView
‘s smart tag to choose a data source, pointing it to our newly created ObjectDataSource1
control. Enable both Deleting and Editing via the checkboxes on the smart tag. Note that .Net has automatically created a number of fields in our GridView for us, but has left out our timestamp field. This is actually the desired behavior as the timestamp will never contain data useful to a user. (Remember, no jets on the jet-way, no time in the SQL timestamp.)

Figure 9: Configure the GridView’s Data Source
Run the website with debugging enabled, and click Delete on a row you’ve selected for termination. (That extra cold damage comes in handy.) … Exception! Value cannot be null. Hit your browser’s back button, and put a breakpoint in the ProductBLL
‘s delete function. Now attempt to delete another row, and note that the timestamp
is null. It isn’t being passed to the method properly, because ASP.Net doesn’t realize that it is a necessary data key. Stop debugging, and open up the properties for your GridView
Control. To get ASP.Net to properly pass the timestamp add it it to the DataKeyNames
property.

Figure 10: Add the Timestamp Field to the DataKeyNames
Start your website in debug mode, and delete a row. It should work, unless you still have order details data blocking you because of a foreign key constraint. To eliminate this problem run the simple queries DELETE FROM [Order Details]
and DELETE FROM Orders
on your database. You should now be able to delete rows from the table.
Step 6: Configuring your ObjectDataSource and GridView for Editing Rows
Now that deleting is working, edit the values of one of the rows. Exception! Column ‘timestamp’ is read only?!

Figure 11: Timestamp is Read Only Exception
This field is read only in the database, and Visual Studio automatically imported this property when we created the table definition in our xsd file. To be able to properly use the concurrency checking of the DataRow
we’ll need to make it not read only. Find your Products table definition in your Northwind.xsd file and set ReadOnly to false.
I’ll take a moment here to rant: why is this a run-time error and not a compile time error? If the field is marked as read only, the property should only have a get and no set forcing the developer to fix this problem at compile time. Remember that this exception isn’t going out to the database as we’re able to fix it by changing the Data Set definition. /rant

Figure 12: Changing Timestamp to be Writable
Run the website in debug mode again, and editing should now work perfectly. To prove that our new concurrency detection methods are functioning, select a row for editing and then open up a new browser window of the same page, editing the same row. Make a change to the row on the first page, and save it, then switch to your second page, make a change and save it. Exception! Concurrency violation. This time the exception is the desired behavior as we shouldn’t be able to update the row without getting the newest version. To handle the exception a bit more gracefully than the current error page, we’ll implement the error messages from the tutorial on Optimistic Concurrency. Follow the steps outlining how to add a label that will handle concurrency exceptions.
Copy and paste both the Exception labels and event methods directly into your Default.aspx.cs
file from Step 6 of the Optimistic Concurrency tutorial. Rename the deleted method to ProductsDataSource_Deleted
and add it to the OnDeleted
action in your ObjectDataSource1
control. Set the OnRowUpdated
of your GridView1
control to point at the ProductsGrid_Rowupdated
function. Attempting to delete or update a previously updated row will now result in a much nicer error message.
Step 7: Add a DetailsView control for Inserting Data
Drag a DetailsView
control onto your page and select your ObjectDataSource1
control from the Choose Data Source dropdown in the Smart Tag. In the properties sheet for the Details View set the DefaultMode
to Insert
. This should cause your DetailsView
to be a list of blank fields. If you’d like, you can refer to the other tutorials on how to clean up the SupplierID
and CategoryID
fields so that they have a DropDown full of items instead of requiring an ID.

Figure 13: Configure your Details View’s Data Source, and Enable Inserting
You should be able to add rows to the database with this insert control, leaving the SupplierID
and the CategoryID
blank.
Summary
Concurrency violations can be a massive headache to track down and fix, causing frustration for software users and developers. The best practice approach is to avoid these types of problems up front with a good concurrency strategy such as the one outlined above. These slight modifications to the Typed DataSet will allow for easy use of a timestamp field in multiple tables, table adapters, queries, and pages with minimal editing. The timestamp field is an excellent addition to your SQL arsenal, and when used properly can provide major speed gains for your page.
For each table there are really only a few things that need attention: add a timestamp
field, generate the queries with optimistic concurrency turned on, remove the original_
from the OldValuesParementerFormatString
, enable editing on the timestamp
field and add it to the DataKeyNames
parameter.
Good Luck