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.

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.

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