Repository Design: Sharing a transaction

Question

I am implementing a Rest service using ServiceStack. We use the repository pattern and auto-wire repositories into services via IOC.

Currently, we have a naive approach where one db model is paired with one repository. This means that whenever more than one entity is manipulated in one service, no transactional boundaries are used. Repositories are invoked sequentially: if one or more steps along the way fail, one has to "rollback" the db to its initial state, manually. Worst case scenario, if the request thread dies, or if an unchecked exception occurs (e.g., OutOfMemoryException) the database will be left in an inconsistent state.

I have a set of hypothetical solutions, but i regard none as adequate:

1. Open a connection and start a transaction at the Service Level. Invoke repositories, passing them the connection. This is obviously wrong as it goes against all the ddd design guidelines. The whole point is for the upper layers to be completely ignorant about concrete persistence. Moreover, it would mess up unit testing.
2. Have the first repository starting a transaction. Other dependent repositories would be invoked, but passing the already opened connection. This also sounds like bad design.
3. Defining aggregates. I'm not a great fan of this one as I'm not a domain modelling expert, and I feel that by introducing aggregates, I am liable to introduce designs errors. One advantage of the current model is that it is simple.

Any one has suggestions for this problem? Thanks in advance

Answer 1

You can use a pass through class usually called UnitOfWork, where you will open and close the "connection". Search for "Unit of work" you will find many examples. You can customize the below snippet to include transactions.

public class UnitOfWork : IUnitOfWork
{
    readonly CompanyDbContext _context;

    public UnitOfWork()
    {
        _context = new CompanyDbContext ();
    }

    private bool _disposed;
    protected virtual void Dispose(bool disposing)
    {
        if (!_disposed)
        {
            if (disposing)
            {
                _context.Dispose();
            }
        }
        _disposed = true;
    }
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    public void Save()
    {
        _context.SaveChanges();
    }

    public IProductRepository ProductRepository
    {
        get { return new ProductRepository(_context); }
    }

    public ICartRepository CartRepository
    {
        get { return new CartRepository(_context); }
    }
}

then you can do multiple transactions like below

 using (_unitOfWork)
 {
     var p = _unitOfWork.ProductRepository.SingleOrDefault(a => a.id ==1);
     _unitOfWork.CartRepository.Add(p);
     _unitOfWork.Save();  
 }

Answer 2

In order to use Transactions effectively with Ormlite, you need to create custom DBManager class which can hold the connection object for each thread (use a ThreadStatic). Then you can use this custom DBManager in your repository to call different ormlite function.

Part of the code that I use is (you need modify the code to work properly):

public class ThreadSpecificDBManager : IDisposable, IDBManager
{
    [ThreadStatic]
    private static int connectionCount = 0;

    [ThreadStatic]
    private static int transactionCount = 0;

    [ThreadStatic]
    private static IDbConnection connection = null;

    public string ConnectionString { get; set; }

    public IDbConnection Connection { get { EnsureOpenConnection();  return connection; } }

    static ThreadSpecificDBManager()
    {
    }

    private void EnsureOpenConnection()
    {
        if ((connection == null) || (connection.State == ConnectionState.Closed))
        {
            OrmLiteConfig.TSTransaction = null;
            transactionCount = 0;
            connectionCount = 0;

            connection = (DbConnection)ConnectionString.OpenDbConnection();

            //if (ConfigBase.EnableWebProfiler == true)
            //    connection = new ProfiledDbConnection((DbConnection)connection, MiniProfiler.Current);
        }
    }

    public ThreadSpecificDBManager(string connectionString)
    {
        ConnectionString = connectionString;
        connectionCount++;
        EnsureOpenConnection();
    }

    public void Dispose()
    {
        if (transactionCount > 0)
        {
            //Log.Error("Uncommitted Transaction is left");
        }

        connectionCount--;
        if (connectionCount < 1)
        {
            if ((connection != null) && (connection.State == ConnectionState.Open))
                connection.Close();

            if (connection != null)
                connection.Dispose();

            connection = null;
        }
    }

    public void BeginTransaction()
    {
        if (transactionCount == 0)
        {
            //Log.SqlBeginTransaction(0, true);
            OrmLiteConfig.TSTransaction = Connection.BeginTransaction();
        }
        else
        {
            //Log.SqlBeginTransaction(transactionCount, false);
        }
        transactionCount = transactionCount + 1;
    }

    public void RollbackTransaction()
    {
        try
        {
            if (transactionCount == 0)
            {
                //Log.SqlError("Transaction Rollback called without a begin transaction call.");
                return;
            }

            if (OrmLiteConfig.TSTransaction == null)
            {
                //Log.SqlError("Transaction is not saved in the Thread Safe variable- so it cannot be rollbacked.");
                throw new Exception("Transaction is not saved in the Thread Safe variable- so it cannot be rollbacked.");
            }

            if (transactionCount == 1)
            {
                transactionCount = 0;
                try
                {
                    //Log.SqlRollbackTransaction(transactionCount, true);
                    OrmLiteConfig.TSTransaction.Rollback();
                }
                catch (Exception ex1)
                {
                    //Log.SqlError(ex1,"Error when rolling back the transaction");
                }

                OrmLiteConfig.TSTransaction.Dispose();
                OrmLiteConfig.TSTransaction = null;
            }
            else
            {
                //Log.SqlRollbackTransaction(transactionCount, false);
                transactionCount = transactionCount - 1;
            }
        }
        finally
        {

        }
    }

    public void CommitTransaction()
    {
        try
        {
            if (transactionCount == 0)
            {
                //Log.SqlError("Transaction Rollback called without a begin transaction call.");
                return;
            }

            if (OrmLiteConfig.TSTransaction == null)
            {
                //Log.SqlError("Transaction is not saved in the Thread Safe variable- so it cannot be rollbacked.");
                throw new Exception("Transaction is not saved in the Thread Safe variable- so it cannot be rollbacked.");
            }

            if (transactionCount == 1)
            {
                //Log.SqlCommitTransaction(transactionCount,true);
                transactionCount = 0;
                OrmLiteConfig.TSTransaction.Commit();
                OrmLiteConfig.TSTransaction.Dispose();
                OrmLiteConfig.TSTransaction = null;
            }
            else
            {
                //Log.SqlCommitTransaction(transactionCount, false);
                transactionCount = transactionCount - 1 ;
            }
        }
        finally
        {
        }
    }
}