What is connection pooling in C# and how to achieve it?

Connection pooling in C# is a technique that improves database application performance by reusing database connections rather than creating new ones for each request. When a connection is closed, it's returned to a pool for future use instead of being destroyed.

The .NET Framework automatically manages connection pooling for ADO.NET connections. When you use the using statement with database connections, it ensures proper disposal and automatic participation in connection pooling.

How Connection Pooling Works

The connection pool maintains a set of physical database connections. When your application requests a connection with the same connection string, the pool returns an available connection rather than creating a new one.

Using Connection Pooling with 'using' Statement

The using statement ensures proper disposal of database connections, which automatically returns them to the connection pool −

Example

using System;
using System.Data.SqlClient;

class Program {
   static void Main() {
      string connString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;";
      
      using (SqlConnection conn = new SqlConnection(connString)) {
         SqlCommand cmd = conn.CreateCommand();
         cmd.CommandText = "SELECT CustomerId, CompanyName FROM Customers";
         conn.Open();

         using (SqlDataReader dr = cmd.ExecuteReader()) {
            while (dr.Read()) {
               Console.WriteLine("{0}\t{1}", dr.GetString(0), dr.GetString(1));
            }
         }
      } // Connection automatically returned to pool here
      
      Console.WriteLine("Connection disposed and returned to pool");
   }
}

The output of the above code is −

ALFKI   Alfreds Futterkiste
ANATR   Ana Trujillo Emparedados y helados
ANTON   Antonio Moreno Taquería
Connection disposed and returned to pool

Configuring Connection Pool Settings

You can control connection pooling behavior through connection string parameters −

Example

using System;
using System.Data.SqlClient;

class Program {
   static void Main() {
      string connString = "Data Source=localhost;" +
                         "Integrated Security=SSPI;" +
                         "Initial Catalog=TestDB;" +
                         "Pooling=true;" +
                         "Min Pool Size=5;" +
                         "Max Pool Size=100;" +
                         "Connection Timeout=30;";
      
      using (SqlConnection conn = new SqlConnection(connString)) {
         conn.Open();
         Console.WriteLine("Connection State: " + conn.State);
         Console.WriteLine("Database: " + conn.Database);
      }
      
      Console.WriteLine("Connection returned to pool with custom settings");
   }
}

The output of the above code is −

Connection State: Open
Database: TestDB
Connection returned to pool with custom settings

Connection Pool Parameters

Benefits of Connection Pooling

• Performance improvement − Eliminates the overhead of creating new connections

• Resource management − Limits the number of concurrent database connections

• Automatic management − .NET handles pool creation and cleanup automatically

• Scalability − Supports multiple concurrent requests efficiently

Conclusion

Connection pooling in C# automatically manages database connections for better performance and resource utilization. By using the using statement with ADO.NET connections, you ensure proper disposal and automatic participation in connection pooling without additional code complexity.