SQL Server Timestamps and ADO.NET Concurrency
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.