Using the Singleton pattern for a database connection pool in C# is a common and effective way to ensure that your application maintains a single shared instance of the connection pool throughout its lifetime. This post covers why and how to use the Singleton Pattern for database connection pools in C#, including practical examples and best practices.
Using the Singleton Pattern for Database Connection Pools in C#
Getting Started
Efficient database access is a cornerstone of any high-performance application. Every time an application opens a new database connection, it consumes resources both on the client and the database server. To minimize this overhead, developers use connection pooling( A technique where multiple database connections are maintained and reused instead of being created from scratch each time).
In C#, connection pooling is usually handled by ADO.NET automatically. However, when you build your own custom data access layer or manage specific resources like ORM contexts or connection factories, you may want tighter control over how connections are shared.
That’s where the Singleton Design Pattern becomes useful. Here I am not going to discuss about the Singleton Pattern, if you want to know about Singleton pattern and the Software Design patterns the visit this post "Software Design Patterns for C# Developers".
Why Use Singleton for Database Connection Pools?
- Resource Efficiency: Maintaining a single shared pool avoids redundant connection objects.
- Thread Safety: A properly implemented Singleton ensures safe access to shared resources from multiple threads.
- Centralized Management: The Singleton instance provides a unified way to manage connections, handle pooling, and monitor usage.
- Performance: Reduces connection creation overhead and improves response time.
Singleton for a Database Connection Pool (ADO.NET)
Basic Singleton Implementation in C# using System;
using System.Data;
using System.Data.SqlClient;
public sealed class DatabaseConnectionPool
{
// The single instance (lazy initialization)
private static readonly Lazy<DatabaseConnectionPool> _instance =
new Lazy<DatabaseConnectionPool>(() => new DatabaseConnectionPool());
// Connection string (ideally loaded from config)
private readonly string _connectionString;
// Private constructor to prevent external instantiation
private DatabaseConnectionPool()
{
// Example: load from configuration or environment variable
_connectionString = "Server=localhost;Database=MyAppDB;User Id=myUser;Password=myPassword;Pooling=true;Max Pool Size=100;";
}
// Public property to access the single instance
public static DatabaseConnectionPool Instance => _instance.Value;
// Method to get a connection from the pool
public SqlConnection GetConnection()
{
var connection = new SqlConnection(_connectionString);
connection.Open();
return connection;
}
}
class Program
{
static void Main()
{
// Get a connection from the singleton pool
using (var conn = DatabaseConnectionPool.Instance.GetConnection())
{
using (var cmd = new SqlCommand("SELECT COUNT(*) FROM Users", conn))
{
int count = (int)cmd.ExecuteScalar();
Console.WriteLine($"User count: {count}");
}
}
}
}
- Lazy<T> ensures thread-safe, lazy initialization — the instance is created only when first accessed.
- The ADO.NET connection string parameter Pooling=true automatically manages a pool internally.
- You still get connection reuse via ADO.NET, but the Singleton ensures consistent configuration and access.
using System;
using System.Collections.Concurrent;
using System.Data.SqlClient;
using System.Threading;
public sealed class CustomConnectionPool
{
private static readonly Lazy<CustomConnectionPool> _instance =
new Lazy<CustomConnectionPool>(() => new CustomConnectionPool());
private readonly ConcurrentBag<SqlConnection> _connections;
private readonly string _connectionString;
private readonly int _maxPoolSize;
private int _currentCount;
private readonly SemaphoreSlim _semaphore;
private CustomConnectionPool()
{
_connectionString = "Server=localhost;Database=MyAppDB;User Id=myUser;Password=myPassword;";
_connections = new ConcurrentBag<SqlConnection>();
_maxPoolSize = 10; // You can configure this as needed
_currentCount = 0;
_semaphore = new SemaphoreSlim(_maxPoolSize, _maxPoolSize);
}
public static CustomConnectionPool Instance => _instance.Value;
public async Task<SqlConnection> GetConnectionAsync()
{
await _semaphore.WaitAsync();
if (_connections.TryTake(out var connection))
{
if (connection.State == System.Data.ConnectionState.Closed)
await connection.OpenAsync();
return connection;
}
Interlocked.Increment(ref _currentCount);
var newConnection = new SqlConnection(_connectionString);
await newConnection.OpenAsync();
return newConnection;
}
public void ReturnConnection(SqlConnection connection)
{
if (connection == null)
{
_semaphore.Release();
return;
}
if (connection.State == System.Data.ConnectionState.Open)
{
_connections.Add(connection);
}
else
{
connection.Dispose();
Interlocked.Decrement(ref _currentCount);
}
_semaphore.Release();
}
public void DisposeAll()
{
while (_connections.TryTake(out var conn))
{
conn.Dispose();
}
_currentCount = 0;
}
}
| Component | Description |
|---|---|
ConcurrentBag<SqlConnection> |
Thread-safe storage for idle connections. |
SemaphoreSlim |
Limits how many total connections can exist. |
Lazy<CustomConnectionPool> |
Ensures a single, lazily-initialized instance (the Singleton). |
Interlocked |
Atomically tracks the count of active connections. |
class Program
{
static async Task Main()
{
var pool = CustomConnectionPool.Instance;
// Acquire a connection from the pool
var connection = await pool.GetConnectionAsync();
try
{
using (var cmd = new SqlCommand("SELECT GETDATE()", connection))
{
var result = await cmd.ExecuteScalarAsync();
Console.WriteLine($"Server time: {result}");
}
}
finally
{
// Return connection to the pool
pool.ReturnConnection(connection);
}
}
}
GetConnectionAsync()- Waits for an available slot (
SemaphoreSlim.WaitAsync()). - Tries to reuse a connection from the bag.
- Creates a new one if none are available and the pool limit isn’t reached.
- Waits for an available slot (
ReturnConnection()- Returns the connection to the bag for reuse.
- If it’s broken, it’s disposed instead.
DisposeAll()- Used for graceful shutdown (e.g., app exit or testing teardown).
In most production systems using SQL Server or other ADO.NET providers, you should rely on the built-in connection pooling (Pooling=true in the connection string) — it’s highly optimized.
A custom pool is best suited for:- Custom data sources,
- Rate-limiting scenarios,
- Or educational purposes.
Singleton for a Database Connection Pool (Dapper or EF Core)
public interface IDbConnectionFactory
{
IDbConnection CreateConnection();
}
public sealed class SqlConnectionFactory : IDbConnectionFactory
{
private static readonly Lazy<SqlConnectionFactory> _instance =
new Lazy<SqlConnectionFactory>(() => new SqlConnectionFactory());
private readonly string _connectionString;
private SqlConnectionFactory()
{
_connectionString = "Server=localhost;Database=MyAppDB;User Id=myUser;Password=myPassword;Pooling=true;";
}
public static SqlConnectionFactory Instance => _instance.Value;
public IDbConnection CreateConnection()
{
var connection = new SqlConnection(_connectionString);
connection.Open();
return connection;
}
}
Summary
Using the Singleton Pattern to manage database connection pools in C# provides a clean, efficient, and thread-safe way to handle database connections in a multi-threaded environment.
While ADO.NET already provides connection pooling, a Singleton is useful when building custom connection managers, microservices, or ORM frameworks that need explicit control over resource sharing.
By combining Singleton with best practices like proper disposal, thread safety, and DI compatibility, you can ensure both performance and stability in your data access layer.
Thanks