Connection Pooling

What is connection pooling?

Connection pooling is the process of taking a connection from a pool of connections; once the connection is created in the pool any application can re-use that connection.

Why connection pooling?

The main purpose of any application is to provide a fast & efficient solution. Mainly, the performance of the system/application depends on the database activity. Making a connection to the database is time-consuming (which depends on network speed & memory). When pooling is true, the request for database connection can be fulfilled from the pool instead of re-connecting to the database, which will increase database performance.

What if the connection in the pool finishes?

Connections are released back into the pool when you call close or dispose; when no connection is available to serve, the request will be queued & the connection is available in the pool, it will be served to the request.

When you do not close connections properly in your application, you will get the following error.

• Exception: System.InvalidOperationException.
• Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
• Source: System.Data.

using System.Data.SqlClient;
namespace YourNamespace
{
    public class YourClass
    {
        public void YourMethod()
        {
            SqlConnection connection = null;
            try
            {
                // Replace 'options' with your actual connection string
                string connectionString = "your_connection_string_here";
                // Replace 'isInTransaction' with your desired boolean variable
                bool isInTransaction = false;
                connection = new SqlConnection(connectionString);
                SqlConnectionPoolManager.GetPooledConnection(connection, ref isInTransaction);
                connection.Open();
                // Your code here
            }
            finally
            {
                connection?.Dispose();
            }
        }
    }
}

The solution to this is as follows.

using System.Data.SqlClient;
namespace YourNamespace
{
    public class YourClass
    {
        public void YourMethod()
        {
            string myConnectionString = "your_connection_string_here";
            using (SqlConnection sqlCon = new SqlConnection(myConnectionString))
            {
                try
                {
                    sqlCon.Open();
                    DbActivity(sqlCon);
                }
                finally
                {
                    sqlCon.Close();
                }
            }
        }
        private void DbActivity(SqlConnection connection)
        {
            // Your database-related activity code here
        }
    }
}

Where can we set connection pooling parameters?

conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=80; Connect Timeout=2;";

Notice in this ConnectionString; that we can set minimum & maximum pool size and also within what time your connection should be time out.

The following are four parameters that control most of the connection pooling behavior.

• Connect Timeout: controls the wait period in seconds when a new connection is requested; if this timeout expires, an exception will be thrown. The default is 15 seconds.
• Max Pool Size: Specifies the maximum size of your connection pool. The default is 100.
• Min Pool Size: Initial number of connections that will be added to the pool upon its creation. The default is zero; however, you may choose to set this to a small number, such as 5, if your application needs consistent response times even after it was idle for hours. In this case, the first user requests won't have to wait for those database connections to establish.
• Pooling: controls if your connection pooling on or off. Default, as you may've guessed, is true. Read on to see when you may use Pooling=false setting.

Here is how we can use it.

SqlConnection SqlCon = new SqlConnection();
try
{
    SqlCon.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER; DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=80;Connect Timeout=2;";    
    SqlCon.Open();
}
catch(Exception)
{
}
finally
{
    SqlCon.Close();
}

Tips and Tricks

• Open a connection only when you need it, not before.
• Close your connection as soon as you are done using it.
• Be sure to close any user-defined transactions before closing a connection.
• Do not close all your connections in the pool, keep at least one connection alive.