Injecting IDbConnection vs IDbConnectionFactory

Question

TLDR: What are the reasons for injecting a connection factory vs the IDbConnection itself.

I'm currently using Autofac in .net MVC to inject an instance of IDbConnection into my repository classes to use with Dapper like so:

Autofac setup:

builder.Register<IDbConnection>(ctx => new
    SqlConnection(conSettings.ConnectionString)).InstancePerRequest();

Repo:

public ClientRepository(IDbConnection connection)
{
    _connection = connection;
}

public async Task<IEnumerable<Client>> GetAsync()
{
    string query = "SELECT * FROM Clients";
    return (await _connection.QueryAsync<Client>(query)).ToList();
}

This has been working perfectly fine for me so far, but I'm a little worried about connections staying open and not being disposed of.

Every post I find on the topic ends in someone suggesting passing in a connection factory and calling it in a using statement, without really mentioning why my current setup is "bad".

As far as I can tell every request should get it's own IDbConnection where Dapper takes care of opening and closing the connection and Autofac takes care of the disposing.

Is this not the case? Am I missing something?

Answer 1

They way I'm doing this on an ASP.NET Core project (bear with me for a second, I know it's not what you're using but the concept still applies) is injecting the connection string through the repository constructor.

As you will see, I actually inject the IConfiguration object because I need other settings from the configuration file because of other requirements. Just pretend it's the connection string.

Then my repository looks like this (rough example, written off the top of my head so forgive any mistakes I might have made):

public class FooRepository
{
    private readonly IConfiguration _configuration;

    public FooRepository(IConfiguration configuration)
    {
        _configuration = configuration
    }

    private IDbConnection Connection => new SqlConnection(_configuration.GetConnectionString("myConnectionString"));

    public Foo GetById(int id)
    {
        using (var connection = Connection)
        {
            return connection.QueryFirstOrDefault<Foo>("select * from ...", new {id});
        }
    }
}

ADO.NET connections are pooled, opening one as needed and then closing it is the way it's usually done. With using you make sure the connections gets closed and disposed - returned to the pool - as soon as you're done, even if an exception gets thrown.

Of course you might want to extract this common code to an abstract superclass, so that you won't need to repeat the name of the connection string in every repository, nor re-implement the Connection property.

Also, as I mentioned in my comment, Dapper is not in charge of opening or closing connections, in fact it fully expects the connection to be open before you can call any of its methods. This is no longer true, sorry.