SQL Server Timestamps and ADO.NET Concurrency

June 7, 2006
No Comments.


I was talking with a bright guy inside the ADO.NET team today when he told me that the DbCommandBuilder supports a new option called ConflictOption. This option alerts the DbCommandBuilder to use one of three methods for detecting concurrency conflicts:

  • CompareAllSearchableValues: The 1.x behavior of comparing all values to make sure the row hasn’t changed.
  • CompareRowVersion: This compares the primary key and a timestamp field (or rowversion).  This only works if the table has both a primary key and a timestamp (or rowversion) defined.
  • OverwriteChanges: This only compares the primary key and does not provide any concurrency, but simply allows for overriting of data.  *I DO NOT SUGGEST YOU USE THIS*.

Unfortunately, the TableAdaptersin Typed DataSets (v 2.0) don’t seem to be using this when they create their concurrency (the TableAdatper code generator uses a DbCommandBuilder to create the Update/Delete statements).

Additionally, the ASP.NET 2.0 DataSources do not use DbCommandBuilders so they won’t use timestamps to do currency at all.  But my rant about DataSources is for another day.