The Ultimate Guide to Mastering Connection Pooling with Dapper in ASP.NET Core

In high-performance ASP.NET Core development, Dapper is often favored for its "close to the metal" execution speeds. However, Dapper is a "Micro-ORM," which means it handles the mapping of data but leaves the management of database connections entirely in your hands.

If you don't master connection pooling, your high-performance API will eventually hit a wall, resulting in the dreaded TimeoutException: Connection pool exhausted.

1. Understanding the Mechanics

Connection pooling is a service provided by the underlying ADO.NET driver (like Microsoft.Data.SqlClient). Instead of destroying a connection when you are done, the driver "recycles" it into a pool.

The Lifecycle of a Pooled Connection:

Request: You call connection.Open(). The driver looks for an idle connection in the pool.
Execution: Dapper executes your SQL using that connection.
Release: You call connection.Dispose(). The driver resets the connection state and puts it back in the pool.

The Golden Rule: You do not manage the pool; you manage the individual connection's lifecycle. If you fail to dispose of a connection, it cannot return to the pool, creating a "leak."

2. Setting Up the Infrastructure

The modern standard for managing Dapper connections in ASP.NET Core is using a connection factory combined with dependency injection.

The Connection Factory Pattern

This pattern abstracts the creation of the connection, ensuring your business logic doesn't need to know about connection strings or specific providers.

public interface IDbConnectionFactory
{
    Task<IDbConnection> CreateConnectionAsync();
}

public class SqlServerConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;

    public SqlServerConnectionFactory(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<IDbConnection> CreateConnectionAsync()
    {
        var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        return connection;
    }
}

Dependency Injection (Program.cs)

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddSingleton<IDbConnectionFactory>(_ => 
    new SqlServerConnectionFactory(connectionString!));

3. Implementing the Repository Pattern

When using Dapper, the repository is where the "Open Late, Close Early" principle is enforced. Using the using var syntax ensures the connection is returned to the pool immediately after the method completes.

public class UserRepository : IUserRepository
{
    private readonly IDbConnectionFactory _connectionFactory;

    public UserRepository(IDbConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    public async Task<User?> GetByIdAsync(int id)
    {
        // 1. Get and Open the connection
        using var connection = await _connectionFactory.CreateConnectionAsync();

        // 2. Execute with Dapper
        const string sql = "SELECT Id, Username, Email FROM Users WHERE Id = @Id";
        return await connection.QueryFirstOrDefaultAsync<User>(sql, new { Id = id });
        
        // 3. 'using' block ends here, connection is returned to the pool
    }
}

4. Tuning for High Performance

Your connection string is your primary tool for tuning pool behavior. Here are the parameters that matter:

Configuration Best Practices

Max Pool Size (Default 100): If your API expects massive bursts of traffic, increase this to 200 or 500.
Min Pool Size (Default 0): Set this to 10 or 20 for production. This keeps a small number of connections "warm," preventing the latency hit of creating new connections during a cold start.
Connection Timeout: How long the application waits for a connection from a full pool before crashing. Default is 15s; 5–10s is often better for failing fast.

Recommended Production String:

Plaintext

"Server=myServer; Database=myDB; Max Pool Size=200; Min Pool Size=20; Connect Timeout=10;"

5. Troubleshooting Pool Exhaustion

If your logs show System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool, check for these three culprits:

Missing using statements: You are opening connections but never disposing of them.
Synchronous Blocking: Using .Result or .Wait() on async tasks can stall the thread pool, which in turn holds connections open longer than necessary.
Long-Running Queries: If a query takes 30 seconds to run, that connection is occupied for 30 seconds. Optimize your SQL indexes!

6. Summary Checklist

[ ] Never use a static or Singleton connection object.
[ ] Always use using "or" using var to ensure disposal.
[ ] Inject a connection factory to keep code clean and testable.
[ ] Use async Dapper methods to maximize throughput.
[ ] Monitor your database metrics to find the "sweet spot" for your Max Pool Size.