C# UnitOWork implementation without EF with database transaction or TransactionScope

Question

I'm working on a Web API project (ninject, raw sql queries, repository pattern, UoW pattern) I have checked almost everywhere for an article that would describe implementation of UoW with simple database transactions w/o using entity framework (plain SQL requests and SqlConnection etc...), but couldn't find anything.

The problem I have is the following. I have a Web API which has controllers that work with repositories who, in turn, work with DB through DBManager class injected into them through UoW.

Let us imagine I have 2 methods in a repository, each of them update data in a database:

Method1 - update ticket (add a post from customer). Method2 - update ticket's state (only if post was successful).

These methods may be called one after another, or separately i.e. Method2 may be called from some other method, after ticket was closed, for example.

Method1, before updating the DB creates a transaction through the DBManager. It then updates DB and calls Method2 to do his thing. Method2, since it can also be called as a standalone method, also starts the transaction before updating the DB. When query is executed it commits the transaction and returns to Method1. Method1 at this stage also commits the transaction as there were no exceptions and it wants to persist changes it made to the DB. But, it can't because changes were already committed by the Method2.

So the graph of actions is something similar to one below:

Method1()
  DBManager.BeginTransaction() - begins new transaction

  update DB - adds post to the ticket

  Method2() - calls method 2 to update ticket status
    DBManager.BeginTransaction() - returns transaction started by Method1()

    update DB - updates ticket status

    DBManager.CommitTransaction() - commits transaction

    return

  DBManager.CommitTransaction() - commits transaction to save ALL changes but can't since transaction was already committed.

If I need to call some other method after ticket status was updated, then method would work with completely new set of data since changes were committed into the DB by Method2().

I started thinking on how to fix this, but couldn't find anything. I have read about TransactionScope and thought I can do something like this:

public class UnitOfWork : IUnitOfWork, IDisposable
{
    /// <summary>
    /// DB context.
    /// </summary>
    private IDBManager _dbManager;

    /// <summary>
    /// Repository provider class which can create repositories on demand.
    /// </summary>
    private IRepositoryProvider _repositoryProvider;

    private TransactionScope _transaction;

    public UnitOfWork(IDBManager dbManager, IRepositoryProvider repoProvider)
    {
        _dbManager = dbManager;
        _repositoryProvider = repoProvider;
    }

    public T GetRepository<T>()
    {
        if (_transaction == null)
            _transaction = new TransactionScope();

        return _repositoryProvider.Create<T>(_dbManager);
    }

    public void Save()
    {
        _transaction.Complete();
    }

    public void Dispose()
    {
        _transaction.Dispose();
    }
}

In this case the TransactionScope would begin when I create my first repository and then I could call save in my controller, like so:

    public TicketPost AddTicketPost(int tid, TicketPost update)
    {
        TicketPost post = Uow.GetRepository<ITicketsRepository>().AddPost(tid, update);
        Uow.Save();

        return post;
    }

But, this would mean that TransactionScope would be created for any operation - select/update/delete, and it would last from the moment of creation of first repository (even when I might not need it) till the moment when the transaction is either disposed or completed.

Another solution would be to work with DBManager's transaction and call BeginTransaction from the controller and either Commit or Rollback when I need it. Like so:

Uow.BeginTransaction();
try
{
    TicketPost post = Uow.GetRepository<ITicketsRepository>().AddPost(tid, update);
}
catch (Exception e)
{
    Uow.RollbacTransaction();
}
Uow.CommitTransaction();

But I don't really like this method. I would need to catch exceptions when in first case those would be bubbled up to my ExceptionsHandler which would create response message to a client. Also, I think of a controler as a middle man that gets the request and says "hey, repository, here is the data, I have checked it, do your thing and call me back.". When it gets a "call" back from the repository it may do something else not work-with-data related, like send out e-mails. I like when controller doesn't need to call methods one by one in the same repository and think of things it needs to do to complete the job like:

1. Update the ticket
2. Set status
3. Dome something else with the ticket
4. Send out e-mails

instead of this, controller asks repository to take care of ticket update and waits while it can send out e-mails:

1. Tell controller to do whatever he needs to do to update the ticket.
2. Wait and send out e-mails.

I may be wrong in how I tread controllers and repositories. Please correct me if I'm wrong.

I hope someone could point me to a resource or, may be someone has similar setup and already found a solution for this case (transactions problem).

Any help would be appreciated. Thank you very much in advance.

Answer 1

If I understood you right, you want a way to avoid unneeded transactions from being created?

For this, I suggest you create one BaseUnitOfWork type and two other types: ReadOnlyUnitOfWork and ReadWriteUnitOfWork.

Then, you would use only the ReadOnly, when selecting stuff and ReadWrite when you need to do both.

The skeleton in C# would be something like.

public class BaseUnitOfWork // YOUR INTERFACES HERE
{
    /// <summary>
    /// DB context.
    /// </summary>
    private IDBManager _dbManager;

    /// <summary>
    /// Repository provider class which can create repositories on demand.
    /// </summary>
    private IRepositoryProvider _repositoryProvider;

    public BaseUnitOfWork(IDBManager dbManager, IRepositoryProvider repoProvider)
    {
        _dbManager = dbManager;
        _repositoryProvider = repoProvider;
    }

    public T GetRepository<T>()
    {
        return _repositoryProvider.Create<T>(_dbManager);
    }
}

public class ReadOnlyUnitOfWork : BaseUnitOfWork
{
    public ReadOnlyUnitOfWork(IDBManager dbManager, IRepositoryProvider repoProvider) : base(dbManager,repoProvider)
    {
        _dbManager = dbManager;
        _repositoryProvider = repoProvider;
    }
}

public class ReadWriteUnitOfWork : BaseUnitOfWork// YOUR INTERFACES HERE
{
    private TransactionScope _transaction;

    public ReadWriteUnitOfWork(IDBManager dbManager, IRepositoryProvider repoProvider) : base(dbManager,repoProvider)
    {
        if (_transaction == null)
            _transaction = new TransactionScope();
    }

    public void Save()
    {
        _transaction.Complete();
    }

    public void Dispose()
    {
        _transaction.Dispose();
    }
}

I use this strategy already successfully in several projects.

The good part of this strategy, is that you comply with the S in SOLID (http://en.wikipedia.org/wiki/SOLID_(object-oriented_design)) design: Single responsibility.

One class has the responsibility to handle databases operations with transactions, while the other just handle transactionless operations.

Also, you must understand that the unit of work block should be quick (in execution) and (if possible) small in code (as a best practices).

Therefore, you could use it something like this:

using( IReadWriteUnitOfWork uow = InjectionFramework.ResolveDependencyOfType<IReadWriteUnitOfWork>() )
{
    //do your database stuff here, try to keep it simple.
    //after doing everything, you **commit** the transaction (in your case, you save)
    uow.Save();
}

The good part of using command is that after you have completed this piece of code it will automatically call your dispose method.