SQL Server Timestamp – Implementing Optimistic Concurrency

A .Net hair puller.

Question – how do you convert a TimeStamp column rom SQL Server to a .Net application and back again?

Answer – hold it in a byte array ie byte[], for example

<code>    public bool UpdateProduct(string productName, int? supplierID, int? categoryID, string quantityPerUnit,<br />                              decimal? unitPrice, short? unitsInStock, short? unitsOnOrder, short? reorderLevel,<br />                              bool discontinued, int productID, <span class="highlight">byte [] timestamp</span>)</code>

Got it from this excellent and detailed article with screenshots to prove its working.

Details (a bit only) –

  • add a TimeStamp column to your table. The timestamp column is guaranteed to be unique and it auto-updates on every INSERT and UPDATE.
  • include reading the timestamp value in SELECT statements
  • create stored procedures for INSERTs and UPDATEs accepting the timestamp as an input parameter. Use the timestamp in a WHERE clause within the stored procedures to implement optimistic concurrency

I’ve tried this before, using the same thing (byte array) but it did not work. My situation makes it a bit more complex – the DataTime value has to be persisted across postbacks, so I convert the byte array to a string and back again during postback. The sequence goes like this –

  1. Extract String from byte array
  2. Store extracted string in ViewState
  3. Postback occurs
  4. Restore ViewState
  5. Recreate byte array from String

Guess I have to check some details there.

Powered by ScribeFire.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s