.NET Core  

High-Velocity Connection Pooling with Dapper and ASP.NET Core

In a high-traffic ASP.NET Core Web API, the database is often the bottleneck. While Dapper is famous for its speed, it is a "Micro-ORM," meaning it handles the mapping but leaves the connection management to you. If you don't handle connections correctly, your application will suffer from "Connection Pool Exhaustion," leading to site-wide timeouts and crashes.

1. How Connection Pooling Works in .NET

When you request a connection (e.g., new SqlConnection()), the ADO.NET driver doesn't immediately build a physical pipe to the database. Instead, it looks into a Pool.

  • If an idle connection exists: It is "leased" to your code.

  • If no idle connection exists: A new one is created (up to the Max Pool Size).

  • When you Dispose: The connection isn't closed; it’s cleaned and returned to the pool for the next request.

2. The Implementation: Step-by-Step

Step 1: Configure the Connection String

In your appsettings.json, define your pool limits. For production, it's wise to set a Min Pool Size to avoid "cold start" latency.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=myServer;Database=myDb;User Id=myUser;Password=myPassword;Min Pool Size=10;Max Pool Size=200;Connect Timeout=20;"
  }
}

Step 2: Create a Connection Factory

We avoid injecting IDbConnection directly to prevent lifecycle issues. Instead, we inject a Factory that creates a connection exactly when we need it.

using Microsoft.Data.SqlClient;
using System.Data;

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

Step 3: Register in Dependency Injection

In Program.cs, register the factory as a Singleton.

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

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

Step 4: Building the Repository (The "Safe" Way)

This is the most critical part. We use the using var statement to ensure the connection is returned to the pool the moment the method finishes, even if an error occurs.

public class ProductRepository
{
    private readonly IDbConnectionFactory _connectionFactory;

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

    public async Task<IEnumerable<Product>> GetAllAsync()
    {
        // 1. Connection is pulled from the pool
        using var connection = _connectionFactory.CreateConnection();
        
        const string sql = "SELECT * FROM Products";

        // 2. Dapper handles the .Open() and .Close() internally 
        // if the connection state is closed.
        return await connection.QueryAsync<Product>(sql);
        
        // 3. At the '}', Dispose() is called, and the connection 
        // returns to the pool immediately.
    }
}

3. Advanced Strategy: Resiliency & Retries

In cloud environments, database connections can drop momentarily. Using a library like Polly allows you to retry the connection attempt before failing.

public async Task<Product> GetByIdAsync(int id)
{
    var retryPolicy = Policy
        .Handle<SqlException>()
        .WaitAndRetryAsync(3, i => TimeSpan.FromMilliseconds(500));

    return await retryPolicy.ExecuteAsync(async () => 
    {
        using var connection = _connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<Product>(
            "SELECT * FROM Products WHERE Id = @Id", new { Id = id });
    });
}

4. Common Pitfalls & Anti-Patterns

Anti-PatternWhy it's DangerousThe Fix
Static ConnectionsNot thread-safe. Multiple users will share one pipe, causing data corruption.Always create a new connection per method call.
Long-Running LogicKeeping a connection open while doing 3rd party API calls or heavy processing.Close the connection immediately after the query; process data afterward.
Forgetting usingConnections "leak" and stay busy until the Garbage Collector runs.Always use using or using var.
Sync over AsyncCalling connection.Query() instead of QueryAsync().Use Async to prevent Thread Pool starvation.

5. Monitoring the Pool

To see if your pooling strategy is working, run this SQL query on your database to monitor active vs. idle connections:

SELECT 
    s.host_name, 
    s.program_name, 
    count(c.session_id) AS [Connections] 
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
GROUP BY s.host_name, s.program_name

Summary of Best Practices

  1. Scope: Shortest possible duration. Open late, close early.

  2. Factory: Use a Singleton Factory to generate Scoped/Transient connections.

  3. Disposal: Trust the using block; it is the gatekeeper of the pool.

  4. Async: Always use QueryAsync or ExecuteAsync to keep the Web API responsive.

Membership not found