Repository Pattern with Dapper + Unity

Question

I just finished the Pluralsight courses about Inversion of Control and Mirco-ORMs, and I am struggling with the implementation aspect of some of the concepts.

This is my (simplified) repository implementation using Dapper:

public class AppRepository : IAppRepository
{

    public string TableName { get; } = "apps";
    private IDbConnection _db;

    public AppRepository(IDbConnection db = null)
    {
        if (db == null)
            _db = Bootstrap.container.Resolve<IDbConnection>(); // Default: connection from the simple injector container
        else
            _db = db;
    }

    public List<App> GetAll() => _db.Query<App>(QueryBuilder.BuildGetAll(TableName)).ToList();

}

QueryBuilder is a static class, shared among all repositories, which creates the different sql strings.

The Bootstrap class exposes a UnityContainer:

public static class Bootstrap
{
    public static UnityContainer container;

    private static IDbConnection _db;
    public static void Start(string connectionStringId)
    {
        container = new UnityContainer();

        _db = GetDbConnection(connectionStringId);

        // Register your types:
        container.RegisterInstance<IDbConnection>(_db);
    }

    public static IDbConnection GetDbConnection(string connectionStringId)
    {
        var connectionString = ConfigurationManager.ConnectionStrings[connectionStringId].ConnectionString;
        IDbConnection db = new MySqlConnection(connectionString);
        return db;
    }
}

Then I read a post about NOT to use this kind of pattern:

by far the most common IoC mistake is to wrap up the container in a public static or singleton class that is referenced throughout the code base. It is important to realize that this is not dependency injection, it is service location which is widely regarded as an anti-pattern. I cannot over-emphasize how important it is to move away from this design and to inject your dependencies from the root of your application. In fact, virtually all other IoC mistakes come about as a direct result of this misunderstanding.

I've used IDbConnection db = null because 99% of the time I will be using a single thread with a single connection, but the other 1% I will be transfering data from one server to another.

I have ~60 models (60 tables), so constantly injecting the IDbConnection connection seems like a hassle, specially if further down the path I have to change it somehow. At the same time, Unity is doing nothing useful at this point in time. I could just let the connection be a public property of the static Bootstrap class. I've read some opinions about injectors being more more annoying than helpful.

For the moment I am building a console application, and in the Main function there is a Bootstrap.Start() that lets me forget about IDbConnection for the rest of the code.

I've been trying to find some examples with good patters using Dapper + dependency injection, but I haven't found anything yet that clarifies my doubts.

Answer 1

I don't think you get much benefit from using the IDbConnection. What I do is just use connection in my repository and use IoC just for the IRepository. The other problem with your code is that there is nothing disposing your connection. Since it's a console app it might not be a big deal, but typically you want to open a connection, do a bunch of work and then close it. If this was a web app for instance you'd run out of pools in your connection pool. Typically you want your IoC being the one to new up instances of classes, not pass in one you already created.

Answer 2

I think you can make it nice and clean(er) like this:

Define an IConnection interface that you pass to the repository:

public interface IConnection : IDisposable
{
    IDbConnection Connection { get; }
}

From this interface you can derive all your connections. There you can have a default constructor to create default connections or a parameterized if you need a different connection string (for example when testing):

public class UnityConnection : IConnection
{
    private readonly UnityContainer _unityContainer;

    public UnityConnection() : this("abc") { }

    public UnityConnection(string connectionStringName)
    {
        var connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        Connection = new MySqlConnection(connectionString);
        Connection.Open();

        _unityContainer = new UnityContainer();
        _unityContainer.RegisterInstance<IDbConnection>(Connection);
    }

    public IDbConnection Connection { get; private set; }

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

You modify your repository to require an IConnection:

public class FooRepository<T> where T : IDisposable, IConnection, new()
{
    private readonly IConnection _connection;

    public FooRepository() : this(new T()) { }

    public FooRepository(IConnection connection)
    {
        _connection = connection;
    }

    public List<string> GetBar()
    {
        // ...
    }

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

This repository can create default connections from the default constructor thus the new() constraint, or you can pass it a custom one:

var repo1 = new FooRepository<UnityConnection>();
var repo2 = new FooRepository<UnityConnection>(new UnityConnection("xyz"));

(don't forget about the using)

Nothing is static, nothing is global. It can work with defaults or with custom values. It's testable and easity to use (with default values).

Here the simple DI works with generics and the full DI by passing it a custom connection.

---

EDIT

You can move the IDbConnection implementation into an abstract class so that you don't have to repeat yourself if you have another connection:

public abstract class ConnectionBase : IDbConnection
{

    protected ConnectionBase(IDbConnection connection)
    {
        Connection = connection;
    }

    protected IDbConnection Connection { get; private set; }

    // Verbose but necessary implementation of IDbConnection:
    #region "IDbConnection implementation"        

    public string ConnectionString
    {
        get
        {
            return Connection.ConnectionString;
        }

        set
        {
            Connection.ConnectionString = value;
        }
    }

    public int ConnectionTimeout
    {
        get
        {
            return Connection.ConnectionTimeout;
        }
    }

    public string Database
    {
        get
        {
            return Connection.Database;
        }
    }

    public ConnectionState State
    {
        get
        {
            return Connection.State;
        }
    }

    public IDbTransaction BeginTransaction()
    {
        return Connection.BeginTransaction();
    }

    public IDbTransaction BeginTransaction(IsolationLevel isolationLevel)
    {
        return Connection.BeginTransaction(isolationLevel);
    }

    public void ChangeDatabase(string databaseName)
    {
        Connection.ChangeDatabase(databaseName);
    }

    public void Close()
    {
        Connection.Close();
    }

    public IDbCommand CreateCommand()
    {
        return Connection.CreateCommand();
    }

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

    public void Open()
    {
        Connection.Open();
    }

    #endregion
}

I modified your UnityConneciton so that it uses the base class constructor and passes it the actual connection.

The unity documentation states that:

Resolve() - Returns a concrete instance of the type that is registered for the generic type T.

so I don't think it's necessary to get the connection you've just created with Resolve<T> because it gives you exacly the same connection back. I removed it from the constructor:

I have also made an adjustment so that you can use either the defautl MySqlConnection for unity or you can pass it any other connection (if you have any):

public class UnityConnection : ConnectionBase
{
    private readonly UnityContainer _unityContainer;
    private readonly string DefaultConnectionStringId = "DefaultConnectionString";

    public UnityConnection() : this((string)null) { }

    public UnityConnection(string connectionStringId) 
    : this(new MySqlConnection(GetConnectionString(connectionStringId)))
    {       
    }

    public UnityConnection(IDbConnection connection)
    : base(connection)
    {
        Open();

        // Register connection in UnityContainer:
        _unityContainer = new UnityContainer();
        _unityContainer.RegisterInstance<IDbConnection>(Connection);
    }

    private static string GetConnectionString(string connectionStringId)
    {
        return ConfigurationManager.ConnectionStrings[connectionStringId ?? DefaultConnectionStringId].ConnectionString;
    }
}