Nested Database transactions in C#

Question

I have a base class that declares a private non-static reference to the DataBase Handler instance (DBH).

DBH is a class we use to simplify database operations of derived classes. It contains the usual methods, like ExecuteScalar, StartTransaction among others; and it provides additional benefits in the application context, like caching and zero configuration.

Instances of derived classes use DBH to read/save its state in the database, and since their operations are not atomic, all derived classes use this transaction. Everything is going on in one place: a virtual method called InsertUpdate() declared in the base class.

Next, I have a collection (called Book) of instances of derived classes. I want to take collection updates as transaction.

I want to achieve something similar to this:

DatabaseHandler dbh = new DatabaseHandler()

t = dbh.StartTrasaction();
foreach( Type o in Book<Type> ) 
{
    o.prop1 = ..
    o.prop2 = ...
    o.method1() ...

    o.InsertUpdate(t); // uses its own instance of DatabaseHandler and starts its own transaction
}
dbh.EndTransaction(t);

Currently the InsertUpdate method is parameter-less. I guess I'll have to introduce an overloaded version which accepts a transaction object.

Besides solving my current issue, are there any design issues I need to know about? How can I improve this design or institute a better design?

Answer 1

If the updates are all happening on the same database connectino, then the nested transactions will work as expected. Each InsertUpdate() will run its own transaction, with the overall transaction on dbh being able to roll back the entire thing.

Answer 2

Have you looked at the System.Transactions namespace? Unless you have already discounted it for some reason you may be able to leverage the built in nested transaction support provided there - e.g:

  using (var scope = new TransactionScope())
  {
    // call a method here that uses a nested transaction
    someObject.SomeMethodThatAlsoUsesATransactionScope();

    scope.Complete();
  }

Answer 3

Make sure you read this question

Personally, I usually go with "my own" implementation of a TrasactionScope like object that wacks data on to TLS with the added benefit of having a factory that allows for easy profiling and logging.

To me your current design sound fairly complex. By decoupling your raw database access code from your classes it will reduce duplication (and avoid requiring all your data access classes inherit off a base class). Defining an object as opposed to a set of static methods for DB access will ease testing (you can substitute a mock class)