Optimistic Concurrency Control using RowVersion in Sql 2005 and C# at 1:36 PM

To use optimistic concurrency control, one option is to add a RowVersion (kind of timeStamp) column in Sql 2005 table. Its value will be modified automatically by Sql server whenever the row is updated.

alter table dbo.authors add LastRowVersion RowVersion

The type of the newly added column is "TimeStamp" in Sql 2005, but its value has no relationship with time at all. The RowVersion column only uses the same structure of TimeStamp to save version number (8 bytes).

The process of optimistic concurrency control using RowVersion column is like below: When user A gets a record and tried to edit, the RowVersion value is also got to A's program. Another user B also gets the same record to edit, but B saves his/her content before A saves. Now the record's LastRowVersion is changed. When A tries to save content into the record, the save process should compare the previously fetched LastRowVersion with the latest value in the database. If the two values do not match, user A should get error message like "Another user has saved the content in front of you. Your content cannot be saved", blah blah.

Now, let's see how to deal with the RowVersion column in ASP.NET/C# code. In data access layer, we still use SqlDbType.Timestamp for RowVersion to transfer value between our code and Sql Server. But in other layers, we should use byte[] array.

When the ASP.NET page gets the data from business layer, the RowVersion value can be saved in ViewState:

ViewState["RowVersion"] =
Convert.ToBase64String(bo.LastRowVersion);

When user clicks "Save" button in edit page to save changes, we can get the old RowVersion from ViewState in postback:

byte[] rowVersion =
Convert.FromBase64String(ViewState["RowVersion"] as string);

Then we can pass the rowVersion variable with the new content to data access layer for concurrency check as mentioned above.

In some applications, ViewState may be disabled. In this case, we can save the value in ControlState, which needs several more lines of code than using ViewState.