Click here to Skip to main content
13,348,514 members (80,887 online)
Click here to Skip to main content
Add your own
alternative version


65 bookmarked
Posted 30 Oct 2006

Concurrent User Updates in ASP.NET and SQL Server

, 28 Nov 2006
Rate this:
Please Sign up or sign in to vote.
ASP.NET - Concurrent user updates using the Timestamp column in SQL Server.


Concurrency is one of the key issues that should be addressed in distributed applications. When multiple users attempt to update the same set of data at the same time, updates will be made on a first come first serve basis without knowing the changes made by the other users, e.g.:

  1. "User A" reads a data row to edit.
  2. While User A still works on editing the data, User B reads the same data, modifies a field, and updates it.
  3. User A finally updates the data without noticing the changes made by User B and User B's changes are lost.

Among several techniques available to address the concurrency issue, timestamp is one of the best options in terms of performance, reliability, and ease of implementation. A timestamp is a sequence of SQL Server activity on a row, represented as an increasing number in a binary format. The timestamp value is automatically updated every time a row containing a timestamp column is inserted or updated.


The strategy here is whenever data is fetched from a database to update, get the timestamp value along with the other data and store it in a view state or hidden variable in the front end. When an update is attempted, compare the timestamp value in the database to the original timestamp value that is stored temporarily in the front-end. If they match, the record has not been modified by any other user, so perform the update. If they do not match, the record had been modified by some other user and a concurrency violation has occurred. Notify the user that the data has been modified by another user. At this point, we can provide the user an option to either override his changes or to revise the changes made by the other user. Let's now dive right into the code..

Step 1: Add a timestamp column to the targeted table(s) that you want to handle concurrent updates

In this step, we can add the user name column as well for tracking who has updated the data.

USE pubs
CREATE TABLE [dbo].[Contact](
ContactID int IDENTITY(1,1),
ContactName nvarchar (100) NOT NULL , 
ChgUserID nvarchar (50) NOT NULL,
ChgTimeStamp timestamp) 

Step 2: Modify the SELECT statements to get the timestamps along with other data

Since the timestamp is a binary data field, in order to save it in an ASP.NET viewstate, we need to marshal the timestamp to a string. We have couple of choices on how to handle this marshalling. One option is, we can handle this in the .NET side by converting the timestamp to a string and vice versa, to store and retrieve in the viewstate or a hidden field (see 'Points of Interest'). The other option is to convert the timestamp to a bigint data type before it is returned to the front end to handle it easily in the .NET side (no binary to string conversion required in this case). I'm using the second option in this illustration.

SELECT ContactID, ContactName, 
CONVERT CONVERT(bigint, ChgTimeStamp) as 'TimeStamp'  
FROM Conact Where ContactID = @inContactID

Step 3: Modify the Save procedure accordingly

Add an additional timestamp parameter to the update procedure parameter list. Convert back the integer timestamp value to Timestamp type. Update the data if the record's current timestamp and the timestamp passed to the procedure are the same. In the case of a modified timestamp, the row will not be updated, i.e., the row count will be 0 and an error is raised.

CREATE PROC USP_UpdateContact(
     @inContactID nchar(10),
     @inContactName nvarchar(100),
     @inChgUserID nvarchar(50),
     @inChgTimeStamp bigint
    --Declare Temporary variables
    DECLARE @dbUserID NVARCHAR(50)          
    DECLARE @ErrorMsg VARCHAR(2000) --error strings 
    SET @ChgTimeStamp = _
        CONVERT(Timestamp,@inChgTimeStamp) --Convert Back
    SELECT @dbUserID = ChgUserID FROM Contact 
    WHERE ContactID = @inContactID 

    IF EXISTS (SELECT * FROM Contact  where ContactID = @inContactID)
        UPDATE [dbo].[Contact] 
            [ContactName] = @inContactName,
            [ChgUserID] = @inChgUserID
            WHERE ContactID = @inContactID 
            AND ChgTimeStamp = @ChgTimeStamp
        IF @@ROWCOUNT = 0 
            SET @ErrorMsg = _
            'The data you are about to save is modified by ' _
            + @dbUserID + _
            '. Please review the new data and save again.' 
            RAISERROR(@ErrorMsg,16,1, -999) 
            GOTO ERR_HANDLER 
        IF(@@ERROR <> 0) GOTO ERR_HANDLER 
    INSERT INTO [dbo].[Contact] 
    RETURN 0  
        IF @@TRANCOUNT > 0   
        SELECT @ERR = @@error  
        RETURN @ERR  

Step 4: .NET code to accommodate the timestamp fetched from the database

Get the timestamp into a view state variable. Treat this variable as a regular web control in ASP.NET, i.e., fill it whenever the data is fetched from the database to display along with other web controls. Pass this value back to the database when the data is saved (Step 3).

//View state declaration 
private string TimeStamp
        return (ViewState["TimeStamp"] != 
        null ? ViewState["TimeStamp"].ToString() : "");
    set{ ViewState["TimeStamp"] = value; }

//Fill Time stamp
void DisplayContactUI()
    //Contact Display code here
    TimeStamp = ds.Tables[0].Rows[0]["TimeStamp"].ToString();

void SaveContactDB(..)
    //Open Connection, Add parameters

    pm = cm.Parameters.Add("@inChgTimeStamp", SqlDbType.BigInt);
    pm.Value = decimal.Parse(TimeStamp); 
    //TO DO: check for empty string 
    int i = cm.ExecuteNonQuery();
catch (SqlException sqlex)

Points of Interest

Alternative: To handle timestamp marshalling in the .NET side, use the following viewstate property. Here, we can get the timestamp column value from the database without converting it to a bigint.

public object TimeStamp
        byte[] bt = new byte[8];
        for(int i = 0; i < 8; i++)
            bt[i] = 
                ViewState["TimeStamp"].ToString().Substring(i * 3,2),16);
        return bt;
        ViewState["TimeStamp"] = BitConverter.ToString((byte[])value);

The above code uses the BitConverter class to convert the array of bytes received from the database into a string. The Convert.ToByte method converts the string back to an array of bytes to send back the data to the database.

I would like to Thank Mr. Bruce J Mack and Luigi for their valuable suggestions and feedback. Please feel free to post your questions/ideas/suggestions. Thanks for stopping by. Mahalo!!!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Murali Pamidi
Web Developer
United States United States
Working as Development Consultant at MMA Realty Capital, NYC.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
ParthGMehta10-Sep-12 21:49
memberParthGMehta10-Sep-12 21:49 
GeneralI like it Pin
Kunal_analyst29-Apr-11 5:03
memberKunal_analyst29-Apr-11 5:03 
Generalconversion timestamp to bigint Pin
Erwin@ODS23-Aug-07 1:09
memberErwin@ODS23-Aug-07 1:09 
GeneralA couple of suggestions Pin
David Hay5-Dec-06 13:44
memberDavid Hay5-Dec-06 13:44 
GeneralRe: A couple of suggestions Pin
Murali Pamidi7-Dec-06 7:11
memberMurali Pamidi7-Dec-06 7:11 
Generalsome alternatives Pin
AceCalihan29-Nov-06 20:35
memberAceCalihan29-Nov-06 20:35 
GeneralRe: some alternatives Pin
Murali Pamidi30-Nov-06 11:32
memberMurali Pamidi30-Nov-06 11:32 
QuestionTimestamp Clientside? Pin
Mark Hancock29-Nov-06 1:13
memberMark Hancock29-Nov-06 1:13 
AnswerRe: Timestamp Clientside? Pin
Murali Pamidi29-Nov-06 7:04
memberMurali Pamidi29-Nov-06 7:04 
GeneralConversion of 'timestamp' datatype Pin
lvTH21-Nov-06 3:03
memberlvTH21-Nov-06 3:03 
QuestionRe: Conversion of 'timestamp' datatype Pin
Murali Pamidi21-Nov-06 6:27
memberMurali Pamidi21-Nov-06 6:27 
AnswerRe: Conversion of 'timestamp' datatype Pin
lvTH21-Nov-06 6:58
memberlvTH21-Nov-06 6:58 
GeneralRe: Conversion of 'timestamp' datatype Pin
Murali Pamidi21-Nov-06 10:19
memberMurali Pamidi21-Nov-06 10:19 
GeneralRe: Conversion of 'timestamp' datatype Pin
lvTH21-Nov-06 12:53
memberlvTH21-Nov-06 12:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180111.1 | Last Updated 28 Nov 2006
Article Copyright 2006 by Murali Pamidi
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid