Registration IDbConnection to use Dapper ORM in DI container as Scoped or Transient

Question

Dapper use IDbConnection. I know the difference between Scoped and Transient but I wonder why some people register IDbConnection in DI container in Program.cs as Scoped and others as Transient. Is there any advantage one method over another?

services.AddScoped<IDbConnection>(sp =>
            new SqlConnection(sp.GetRequiredService<IConfiguration>().GetConnectionString("DefaultConnection")));

--------------

services.AddTransient<IDbConnection>(sp =>
            new SqlConnection(sp.GetRequiredService<IConfiguration>().GetConnectionString("DefaultConnection")));

Answer 1

I would recommend against injecting the connection via DI. If you do that the scope of the connection is greater than it should be. ADO.NET provides connection pooling, which means that it will re-use the same (physical) connection to the database, even if you create a new instance of IDbConnection.

For that reason you should ALWAYS use the using statement to ensure that the instance gets disposed and the connection gets closed when you are finished with it - for example in a method. If you call this method again the pooler ensures that the same connection will be used again. If you don't close the connection, it can't be used(it's kind of blocked), which leads to performance issues and exceptions("max connection pool reached").

So in my opinion you should inject classes that are using dapper but not the IDbConnection itself. For example this IUserRepository:

public interface IUserRepository
{
    Task<IEnumerable<User>> GetUsers();
}

and this class implementing it:

public class UserRepository(DapperContext context) : IUserRepository
{
    private readonly DapperContext _context = context;

    public async Task<IEnumerable<User>> GetUsers()
    {
        var query = "SELECT * FROM Users ORDER BY Name ASC";
        using var connection = _context.CreateConnection();
        var users = await connection.QueryAsync<User>(query);
        return users.ToList();
    }
}

As you can see i'm using the using-statement on the connection returned from CreateConnection to ensure that the pooler can do it's work efficiently and without subtle bugs.

The DapperContext contains the dapper logic, so the initialization of the connection:

public class DapperContext(IConfiguration configuration)
{
    private readonly string _connectionString = configuration.GetConnectionString("SqlConnection");

    public IDbConnection CreateConnection() => new SqlConnection(_connectionString);
}

If you ensure that you always use CreateConnection with the using statement, the UserRepository and the DapperContext can be singletons in your application:

public static class ServiceCollectionExtensions
{
    public static IServiceCollection AddServices(this IServiceCollection services)
    {
        services.AddSingleton<IUserRepository, UserRepository>();
        services.AddSingleton<DapperContext>();
        return services;
    }
}

Disclaimer: Code is not tested and was just created to give you an example.