Timestamps and Optimistic Concurrency

Feb 14, 2008 at 7:30 PM
The problem with the use of Timestamps and the current implementation is that once you execute an insert or update you have to execute a separate query to update the timestamp value on your entity and you don’t have a way to update your entity from output parameters either.

This is what I've done so far to implement Optimistic Concurrency using Timestamps and works fine for me:

1. Added an output @timestamp parameter to the Insert stored procedures:

@timeStamp timestamp OUT

SET @testTemplateID = SCOPE_IDENTITY()
SELECT @timeStamp = Timestamp FROM dbo.TestTemplate WHERE TestTemplateID = @testTemplateID


2. Changed the @timestamp parameter on Update SPs to be input/output:

@timestamp timestamp OUT

IF @@ROWCOUNT = 0
RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
ELSE
SELECT @timestamp = Timestamp FROM dbo.TestTemplate WHERE TestTemplateID = @testTemplateID


3. Added the following method to the IUpdateFactory<TDomainObject> interface:

void SetOutputParameters( Database db, DbCommand command, TDomainObject domainObj );


4. Added a call to the previous method from the Save method on the base Repository class:

using (DbCommand command = updateFactory.ConstructUpdateCommand(db, domainObj))
{
db.ExecuteNonQuery(command);
updateFactory.SetOutputParameters( db, command, domainObj );
}


5. Add the output parameter to the ConstructInsertCommand method on the Insert factory:

db.AddOutParameter(command, "timeStamp", DbType.Binary, 8 );


6. Change the timestamp parameter on the ConstructUpdateCommand on the Update factory as follows:

db.AddParameter( command, "timestamp", DbType.Binary, ParameterDirection.InputOutput, "Timestamp", DataRowVersion.Original, testTemplateStep.Timestamp );


7. Implemented the SetOutputParameters on the Update factory:

public void SetOutputParameters( Database db, DbCommand command, TestTemplate testTemplate )
{
System.Byte[] timeStamp = (System.Byte[])(db.GetParameterValue( command, "timestamp" ));
testTemplate.Timestamp = timeStamp;
}


8. Added the same logic to the Insert Factory:

public void SetNewID(Database db, DbCommand command, TestTemplate testTemplate)
{
System.Int32 id1 = (System.Int32)(db.GetParameterValue(command, "testTemplateID"));
testTemplate.TestTemplateID = id1;
System.Byte[] timestamp = (System.Byte[])(db.GetParameterValue(command, "timeStamp"));
testTemplate.Timestamp = timestamp;
}


--Santiago