What is Connection Pooling?

Connection pooling in ADO.NET allows reusing database connections instead of creating new ones for every request. This improves performance by reducing the overhead of establishing a new connection.

 

How Connection Pooling Works?

1. First request: A new connection is created and stored in the pool.
2. Subsequent requests: Instead of opening a new connection, ADO.NET reuses an available connection from the pool.
3. Idle timeout: If a connection is idle beyond a threshold, it may be removed from the pool.
4. Connection closing: Calling conn.Close() returns the connection to the pool instead of destroying it.

 

Connection Pooling in ADO.NET Example

using System;
using System.Data.SqlClient;
using System.Threading;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;Pooling=true;Min Pool Size=2;Max Pool Size=5;";

        for (int i = 0; i < 5; i++)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                Console.WriteLine($"Connection {i + 1} Opened.");
                Thread.Sleep(500);  // Simulate database work
            } // Connection is returned to the pool when disposed
        }
    }
}

 

Key Parameters in Connection String:

• Pooling=true → Enables connection pooling (default).
• Min Pool Size=2 → Minimum number of connections in the pool.
• Max Pool Size=5 → Maximum number of connections in the pool.

 

How to Verify Connection Pooling is Used in ADO.NET?

1. Using SQL Server DMV (Dynamic Management Views)

Run this query in SQL Server while your application is running:

-- All Connection
SELECT * FROM sys.dm_exec_sessions
-- Select one Connection
SELECT * FROM sys.dm_exec_sessions WHERE host_process_id = @@SPID;

 

2. Using Performance Counters in Windows

1. Open Performance Monitor (perfmon.exe).
2. Add the counter .NET Data Provider for SqlServer → NumberOfPooledConnections.
3. Run your application and observe if connections are being reused.

 

3. Checking Connection Pooling Behavior in C#

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;Pooling=true;";

        for (int i = 3; i > 0; i--)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                Console.WriteLine($"Active Pool Connections: {SqlConnection.ClearAllPools()}");
            }
        }
    }
}

This code checks active pool connections and demonstrates if the pool is reusing them.

 

When to Disable Connection Pooling?

You might disable pooling (Pooling=false) in cases such as:

1. Short-lived applications (e.g., console apps running one-time queries).
2. Security concerns (e.g., when frequent authentication changes are needed).
3. Troubleshooting connection leaks (to rule out pooling-related issues).

 

Summary

Feature	Description
Performance Boost	Reuses connections instead of creating new ones.
Default Behavior	Enabled in ADO.NET unless explicitly disabled (Pooling=false).
Verification	Check SQL Server DMV, Performance Monitor, or use C# debug logs.
Key Parameters	Pooling, Min Pool Size, Max Pool Size.

Connection pooling is essential for optimizing database performance in ADO.NET applications.