In Dapper, what should be injected? IDbConnection versus IDbConnectionFactory

asp.net-mvc autofac c# dapper

Question

TLDR: What are the benefits of using a connection factory rather using the IDbConnection directly?

In order to leverage IDbConnection with Dapper, I'm presently utilizing Autofac in.net MVC to inject an instance into my repository classes as seen below:

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();
}

I've had no issues with it so far, however I'm a bit concerned about connections being open and not being closed.

Without really explaining why my present arrangement is "wrong," every article I see on the subject concludes with someone advocating passing in a connection factory and invoking it in a using statement.

According to what I understand, each request should have its own IDbConnection, with Dapper handling connection opening and closure and Autofac handling disposal.

Isn't that the case? Do I have anything missing?

1
4
4/15/2019 1:31:25 PM

Popular Answer

I'm injecting the connection string via the repository constructor to do this on an ASP.NET Core project (stick with me for a second, I realize it's not what you're using but the idea still applies).

You'll see that I really administer theIConfiguration I object because I need different configuration file values for various reasons. Simply act as if it were the connection string.

Then, my repository appears as follows (rough example, written off the top of my head so pardon any errors):

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; typically, one is opened when required and closed afterwards. Withusing Even if an exception is raised, you must ensure that the connections are immediately closed, disposed of, and returned to the pool.

Naturally, you could wish to extract this common functionality to an abstract superclass so that you won't have to duplicate the connection string's name in each repository or re-implement theConnection property.

Additionally, as I indicated in my response, Dapper is not responsible for creating or securing connections; in fact, it fully anticipates that the connection will already be established before any of its methods can be called.Sorry, this is no longer true..

3
4/16/2019 8:57:10 AM


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow