A Suggestion: ModTime

[JamesCurran]

I'd like to suggest that, instead of the oblique VERSION, require each table to have a ModTime column. This would be handled in almost exactly the same way, except instead of new value being \"version+1\" it would be \"DateTime.Now\". (Actually, you would probably want it to be \"GetDate()\" hard-coded into the SQL instead of a parameter, so the data server's clock is used, and all clients are based on the same time. )

The advantage of this is that instead of a meaningless value, the field holds some useful information.

Similarly, you may wish to add a ModUser field, which could be filled either in the SQL, using user_name() or host_name(); or in code ( using HttpContext.User.Identity.Name, or Thread.CurrentPrincipal.Identity.Name depending on the environment)
This should also be used in the Concurrency check, so we have a two-prong validation (time & location). Again this automatically provides useful information as well as concurrency.

[uschumacher]

Hi James,
thanks for your suggestions!

I have thought about how to solve the concurrency problem when I wrote the templates. The two alternatives were an integer Version or a DateTime value. I have chosen the first one because it is a clear and simple solution that works fine with all databases.

To compare date values is not always that clear. It depends on how the ADO.NET provider converts it to a C# DateTime value. And what about a SqlSever 2005 which consists of a Principal, a Witness and a Mirror or a fail over cluster DB system or the summer-/winter time switching in europe? Is it always safe to rely on the system clocks?
So why not use the simple solution that works fine?

You are right, in many applications you need fields like Created, LastChange, ChangedBy, but I think its a better way to separate this from the concurrency problem and put it in the business logic of your application.

Ulrich

[mike.griffin]

As far as DATETIME in SQL server it's not granular enough, it's only down to a 1/3 of a second, several updates can happen in a 3rd of a second and you'll never notice the concurrency issue. So, my two cents is not to use a DateTime, use a TimeStamp or some kind of auto-incrementing int/bigint