The SQL Server timestamp data type is great for optimistic locking. A column with a data type of timstamp is automatically updated by SQL Server every time a row is added or updated, and the timestamp will be unique in the database. So, on an update all you need do is compare the timestamp in the object with that of the timestamp in the db row and if they're the same you can do the update and if not you can throw an optimistic locking error.
So far so good. When I use a data reader to fill my model object pool I can set the timestamp field in the object no problem using the the following code...
dm.OLToken = (Byte[])dr.GetSqlBinary(5);
But when I update the object I use an output parameter in the stored procedure to return the new timestamp value and assign it to the object, using the following code...
_OLToken = (byte[])cmd.Parameters[0].Value;
But the value of _OLToken is always zero after the assignment, and it's not the cast to byte[] that's doing it either as in the debugger cmd.Parameters[0].Value has a value of zero before the cast. Grrrr! So until I fix this problem, it looks like the users will be able to create an object and edit it once after that they will get optimistic locking errors as 0 != the timestamp value on the db. Jeez, do you think that will be a problem? :)
Update: It turns out that if I use an output parameter it always returns zero, but if I mark the parameter I use to send the original optimistic locking token to the update sp as being InputOutput, the exact same code works fine. So now I have my fix, though I'd be a lot happier if I understood why the output parameter doesn't work, so if anyone knows, put me out of my misery will you?