Using the Singleton Connection Pool with Dapper

Here’s a detailed, developer-focused post on using the Singleton Connection Pool with Dapper, covering what it is, why it matters, and how to implement it safely and efficiently.

Using the Singleton Connection Pool with Dapper

Getting Started

Dapper is a lightweight ORM for .NET that’s prized for its speed and simplicity. It works directly with ADO.NET’s IDbConnection interface, executing SQL queries and mapping results to strongly typed objects with minimal overhead.

However, when using Dapper or any data-access library, it’s important to handle database connections correctly. Mismanaging connections can lead to poor performance, connection leaks, or even application crashes under load.

Dapper Meaning

Dapper is a micro-ORM (Object-Relational Mapper) — a lightweight library that helps developers work with databases in C#. It allows you to write raw SQL queries and automatically maps the results to your C# objects, combining simplicity, speed, and control.

Example:

using (var connection = new SqlConnection("YourConnectionString"))
{
  var users = connection.Query<User>("SELECT * FROM Users").ToList();
}

What Is a Singleton Connection Pool?

A Singleton Connection Pool means we create a single shared instance (or factory) responsible for managing database connections throughout the application’s lifetime.

Rather than keeping one physical connection open (which is not thread-safe and should be avoided), the Singleton object provides access to the connection pool(a pool of reusable connections managed by ADO.NET).

This approach ensures:

Minimal connection creation overhead.
Safe concurrent access across multiple threads.
Consistent management of connection strings and configurations.

Implementing a Singleton Connection Pool

Understanding the Problem: A common mistake developers make when using Dapper is to open a new database connection for every query, without leveraging ADO.NET’s built-in connection pooling properly.

For example:

public IEnumerable<User> GetUsers()
{
  using (var connection = new SqlConnection(_connectionString))
  {
    return connection.Query<User>("SELECT * FROM Users");
  }
}

This code works, but it creates and disposes a new SqlConnection instance each time. While ADO.NET does have connection pooling under the hood, repeatedly creating connections like this can still introduce overhead, especially under high traffic. To optimize performance, we can centralize connection management using a Singleton Connection Pool pattern.

Implementing a Singleton Connection Factory

Let’s build a connection factory that encapsulates the logic for creating and managing connections.
Create the Connection Factory

public sealed class DbConnectionFactory
{
  private static readonly Lazy<DbConnectionFactory> _instance =
  new Lazy<DbConnectionFactory>(() => new DbConnectionFactory());
  private readonly string _connectionString;
  private DbConnectionFactory()
  {
    // Load from configuration or environment variable
    _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
  }
  public static DbConnectionFactory Instance => _instance.Value;
  public IDbConnection CreateConnection()
  {
    // Return a new connection from the pool
    var connection = new SqlConnection(_connectionString);
    connection.Open(); // Optional — Dapper opens implicitly if not
    return connection;
  }
}

Here, we use the Singleton pattern(Lazy<T>) to ensure that only one factory instance exists for the entire application. Each call to CreateConnection() gets a new pooled connection from ADO.NET’s connection pool, not a brand new TCP connection.

Use the Factory with Dapper

public class UserRepository
{
  public IEnumerable<User> GetAllUsers()
  {
    using (var connection = DbConnectionFactory.Instance.CreateConnection())
    {
      const string sql = "SELECT * FROM Users";
      return connection.Query<User>(sql);
    }
  }
  public User? GetUserById(int id)
  {
    using (var connection = DbConnectionFactory.Instance.CreateConnection())
    {
      const string sql = "SELECT * FROM Users WHERE Id = @Id";
      return connection.QueryFirstOrDefault<User>(sql, new { Id = id });
    }
  }
}

Each repository method requests a connection from the singleton factory, which ensures consistent configuration and automatic pooling under the hood.

Why This Works

ADO.NET Connection Pooling: ADO.NET automatically pools connections based on the connection string. When you call new SqlConnection() with the same connection string, you’re actually borrowing from the pool, not creating a brand-new physical connection.
Singleton Factory: Ensures a single place to manage your connection string, settings, and future logic (like retry policies or logging).
Thread Safety: Because we’re not sharing the actual SqlConnection() instance (only the factory), this approach is thread-safe.

Common Pitfalls to Avoid

❌ Don’t keep a single open SqlConnection for the entire app. Connections are not thread-safe and should not be shared across requests.
⚠️ Don’t disable connection pooling (it’s enabled by default). Avoid adding Pooling=false in your connection string unless absolutely necessary.
✅ Always Dispose() your connection (e.g., using using blocks). This returns the connection to the pool for reuse.

Summary

Using a Singleton Connection Pool with Dapper is not about keeping one connection open. It’s about creating a single, centralized way to access the efficient, built-in ADO.NET connection pool.

Thanks

Using the Singleton Connection Pool with Dapper