Saturday, November 19, 2005

ADO.NET Transactions, DataAdapter.Update() And DataRow.RowState

I guess that almost every ADO.NET developer stumbles upon this sooner or later... as we all know, DataAdapter.Update() invokes DataRow.AcceptChanges() after a successful DB-update/-insert/-delete. But what if that whole operation happened within a transaction, and a rollback occurs later on during that process? Our DataRow's RowState has already changed from Modified/Added/Deleted to Unchanged (resp. Detached in case of a delete), our DataRowVersions are lost, hence our DataSet does not reflect the rollback that happened on the database.

What we are doing so far in our projects is to work on a copy of the original DataSet, so we can perform a "rollback" in memory as well. In detail, we merge back DataRow by DataRow, which allows us to keep not only its original RowState, but also different DataRowVersions (Original, Current, Proposed and the like). This is essential for subsequent updates to work properly.

But merging thousands of DataRows has a huge performance penalty (esp. if you got Guids as primary keys - this might be caused by a huge number of primary key comparisons, but I am not really sure about that), which sometimes forces us to disable that whole feature, and do a complete reload from the DB after an update instead (but then again, this will wipe pending updates which were rolled back).

So right now I am looking for a faster alternative. ADO.NET 2.0 provides a new DataAdapter property, namely DataAdapter.AcceptChangesDuringUpdate. This is exactly what we need, but we are still running under .NET 1.1.

Microsoft ADO.NET guru David Sceppa came up with another approach:

If you want to keep the DataAdapter from implicitly calling AcceptChanges after submitting the pending changes in a row, handle the DataAdapter's RowUpdated event and set RowUpdatedEventArgs to UpdateStatus.SkipCurrentRow.

That is actually what I will try to do next.