Using dapper Unit of Work in 3-tier architecture

Question

The Unit of Work:

public class UnitOfWork : IUnitOfWork
{
    private IDbConnection _connection;
    private IDbTransaction _transaction;

    private bool _disposed;
    private _repository;
    private UnitOfWork(string connectionString)
    {            
        _connection = new SqlConnection(connectionString);
        _connection.Open();
        _transaction = _connection.BeginTransaction();
    }

    public static IUnitOfWork Create(string connectionString)
    {
        return new UnitOfWork(connectionString);
    }
   public IRepository Repository
    {
        get { return _repository ?? (_repository = new Repository(_transaction)); }
    }

    public void Commit()
    {
        try
        {
            _transaction.Commit();
        }
        catch
        {
            _transaction.Rollback();
            throw;
        }
        finally
        {
            _transaction.Dispose();
            _transaction = _connection.BeginTransaction();
            resetRepositories();
        }
    }

    private void resetRepositories()
    {
        _repository = null;            
    }

    public void Dispose()
    {
        dispose(true);
        GC.SuppressFinalize(this);
    }

    private void dispose(bool disposing)
    {
        if (!_disposed)
        {
            if (disposing)
            {
                if (_transaction != null)
                {
                    _transaction.Dispose();
                    _transaction = null;
                }
                if (_connection != null)
                {
                    _connection.Dispose();
                    _connection = null;
                }
            }
            _disposed = true;
        }
    }

    ~UnitOfWork()
    {
        dispose(false);
    }
}

It's not my code. I took it from Tim Schreiber repository. I need transactions for add/update operations in my project. And I use the Unit of Work in business logic assembly like this:

public class DealService : Service, IDealService
{
    public DealService(string connectionString) 
        : base(connectionString)
    {

    }

    public async Task<User> GetUserAsync()
    {
        var user = null;
        using (var uow = UnitOfWork.Create(_connectionString))
        {
            user = await uow._repository.GetFirstAsync();
        }
        throw new NotImplementedException();
        //business logic here.
    }
}

Service.cs is an abstract class that has protected field _connectionString. And DI composition root I have in ASP.NET Core layer:

public IConfigurationRoot Configuration { get; }


    public void ConfigureServices(IServiceCollection services)
    {            
        services.AddMvc();
        string connectionString = Configuration.GetConnectionString("default");

        services.AddTransient<IDealService, DealService>(service => new DealService(connectionString));

    }

I'm feeling that I do something wrong so I have several questions:

1. As you can see in current realization I have to pass connection string to each Service and then pass to UnitOfWork constructor. Is it right way or exists solution where I can set connection once?
2. I use Unit Of Work without injection and don't like it. But how can I do injection if I need to use Dispose()?

I will be very glad to any help and advice.

Answer 1

You're correct in point #1 that passing the connection string to each service to get your UoW instance is bad form. I'm not sure if that code was designed for a DI first world as is the case Asp.Net Core.

My recommendation (for #2) is keep it simple. Remove the Unit of Work shell, but keep the repository contained inside. Repository methods are usually transactional, and according to your question, you're using Dapper, so maintaining transactional consistency can be done per method call (not based on state as with the UoW).

SQL transactions are part of the natural semantics of Dapper:

public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

So your repository methods have the rough form of:

(assuming an open connection)

using(var tran = connection.BeginTransaction()) {
    try {
        // multiple operations involving connection and tran here

        tran.Commit();
    } catch {
        tran.Rollback();
        throw;
    }
}

The Dispose() is implicit to the using and you've neutralized your issue.

See: using Statement

If you absolutely need UoW style, you may consider moving to something like Entity Framework (or a heavier weight ORM in general), as the DBContext is essentially a much better featured and maintainable unit of work design, if not overkill for your project.