.NET Core  

Mastering Connection Pooling with Dapper in ASP.NET Core Web API

In the world of high-performance .NET applications, Dapper is often the "Goldilocks" choice: it provides the raw speed of ADO.NET with the developer-friendly mapping of an ORM. However, while Dapper makes querying easy, it doesn't automate infrastructure.

The most critical piece of that infrastructure is connection pooling. If managed incorrectly, your API will suffer from latency spikes, "pool exhaustion" errors, and eventual crashes under load.

What is connection pooling?

Opening a physical connection to a database engine (like SQL Server or PostgreSQL) is an expensive operation involving network handshakes and authentication. To solve this, the .NET data provider maintains a Pool of connections.

When you call connection.Open(), the provider checks the pool for an idle connection. When you call Dispose() or Close(), the connection isn't killed; it’s simply wiped and returned to the pool for the next caller.

1. The Architecture of a Healthy Connection

In an ASP.NET Core environment, you must ensure that connections are short-lived. The lifecycle should follow this strict sequence:

  1. Instantiate the connection.

  2. Open the connection (Dapper does this automatically if you haven't).

  3. Execute the query.

  4. Dispose (Return to pool).

The Implementation Pattern

The using declaration is your best friend. It ensures that even if an exception occurs during a database call, the connection is safely returned to the pool.

public async Task<User?> GetUserByIdAsync(int id)
{
    using var connection = new SqlConnection(_connectionString);
    const string sql = "SELECT * FROM Users WHERE Id = @Id";
    
    // Dapper handles the Open/Close logic internally if the state is Closed
    return await connection.QueryFirstOrDefaultAsync<User>(sql, new { Id = id });
}

2. Managing Connections via Dependency Injection

Hardcoding connection strings inside your repositories is a recipe for maintenance nightmares. Instead, leverage ASP.NET Core’s Dependency Injection (DI).

Option A: The Scoped Connection (Simple)

This approach provides a single connection instance per HTTP request.

// Program.cs
builder.Services.AddScoped<IDbConnection>(sp => 
    new SqlConnection(builder.Configuration.GetConnectionString("DefaultConnection")));

Option B: The Connection Factory (Scalable)

For more complex scenarios—like connecting to multiple databases or handling transient faults—a Factory is superior.

public interface IDbConnectionFactory
{
    IDbConnection CreateConnection();
}

public class SqlServerConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;
    public SqlServerConnectionFactory(string connectionString) => _connectionString = connectionString;

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

// Program.cs
builder.Services.AddSingleton<IDbConnectionFactory>(new SqlServerConnectionFactory(connectionString));

3. Tuning the Pool in the Connection String

Connection pooling is configured via the connection string. Here are the levers you can pull to optimize performance:

ParameterDefaultStrategic Use
Max Pool Size100Increase if you have high concurrency (e.g., 200-500).
Min Pool Size0Set to 5 or 10 to keep connections "warm" and avoid cold-start latency.
Connect Timeout15sReduce to 5s if you want to fail fast during network congestion.
PoolingtrueNever set to false in a Web API environment.

Example optimized string

Server=sql_prod;Database=Orders;Max Pool Size=200;Min Pool Size=10;Connect Timeout=20;

4. The "Zombie Connection" Pitfall

The most common cause of InvalidOperationException: Timeout expired is connection leaking. This happens when a connection is opened but never disposed of.

The Danger Zone

public IEnumerable<User> GetUsers()
{
    var conn = new SqlConnection(_str);
    conn.Open();
    return conn.Query<User>("SELECT * FROM Users"); 
    // ERROR: Connection stays open until GC runs!
}

In a high-traffic API, these "zombie" connections will fill Max Pool Size in seconds, and your application will stop accepting new database requests.

5. Performance Best Practices

Use Async All the Way

In a Web API, thread starvation is just as dangerous as connection exhaustion. Always use Dapper’s async methods (QueryAsync, ExecuteAsync). This allows the web server thread to handle other requests while waiting for the database to respond.

Beware of Large Result Sets

If you query 100,000 rows into memory, the connection remains "busy" and tied to that request for much longer. Use SQL pagination (OFFSET/FETCH) to keep database interactions snappy.

Monitor with DMV

You can check how many connections are currently active on your SQL Server using this query:

SELECT count(*) as ActiveConnections, login_name, host_name
FROM sys.dm_exec_sessions
WHERE status = 'running' OR status = 'sleeping'GROUP BY login_name, host_name

Summary

To master Dapper connection pooling

  1. Never use static connections.

  2. Always wrap connections in a using block.

  3. Use async methods to keep the thread pool healthy.

  4. Tune your Max Pool Size based on your expected concurrent user load.

Membership not found