Connection Pooling in ADO.NET

Why go for Connection Pooling?
Establishing connection to a database is cumbersome and resource burning, most important job in the applications, sometimes it might be slow. Most of the applications need to execute any query on the database server, a connection need to be established. Knowing about the Connection Pooling concepts and tips will definitely improves the performance while connecting to the database and the same time solves the requirement.
Connection Pooling at a glance? Connection Pooling is the ability to reuse the database connection for more than one user. That connection might be SQL, OLEDB, ORACLE or whatever. This way of organizing connections in a smarter manner improves performance as the applications do not need to open and close the connection multiple times.
All of us know that we declare the connection string in web.config as follows in .NET Application and make use of this in code behind while connecting to databases.

1.<connectionStrings>

2.<add

3.name="sqlConnectionString"

4.connectionString="Data Source=vhimabindu;Initial Catalog=DNN;password=himavejella;Integrated Security=True; Pooling=true; Max pool size=200; Min poolsize=0" />

5.connectionStrings>

When the connection is closed or disposed in ADO.NET, it is returned to the connection pool and remains idle for until a request for a new connection comes in. If none comes in within this period, the connection to the backend is closed and the connection instance is removed from the Connection Pool. If all the connections are in the pool are busy then new connection waits in the queue for the existing connections to be released .We can set max connections, connection Pool size, Connection Time out etc settings in the web.config.
The following key value pairs explain the connection pooling settings.


• Connection Lifetime - The time of Connection Creation will be compared to the current time. If this period exceeds the Connection Lifetime value that is set, then object pooler destroys the connection. The default value is 0; this will give the maximum timeout for connection.
• Connection Reset - To reset the connection after it was take out from the Connection pool. The default value is true.
• Max pool size - Maximum number of connections allowed within the Connection pool. The value is 100 by default.
• Min pool size - Minimum number of connections allowed within the Connection pool. The value is 0 by default.
• Pooling - To set the connection Pooling if it is true, the connection is drawn from the pool or created if no connection available from the pool. The value is true by default.
• Connection Timeout - Maximum Time (in secs) to wait for a free connection from the pool. The value is 15 by default.
• Incr Pool Size - Controls the number of connections which are established, when all the connections are used. The value is 5 by default
• Decr Pool Size - Controls the number of connections which are closed when most of the established connections are unused. The value is 1 by default

Points to Ponder

• Disabling the connection pooling, infers that the connection object that is created by the user will not be re-used to any other user.
• A Connection pool will be created when Open() method of connection object is called. If the same application is using multiple Connection Strings (say more than one) then multiple connection pools will be created corresponding to each connection string.
• Connection Pooling is applied in ADO.Net by default.
• Connections that are not explicitly closed are not added or returned to the pool. Hence The Close() or Dispose() method of Connection need to be called to close the connection.
• A Connection Pool is freed from the memory when the last connection to the database is closed.
• Connection Pool’s life time is ended as soon as the application domain is unloaded. For example at any time when IISRESET runs the Connection Pool gets destroyed
• Connection Pools cannot be shared across application domains.

The best practice Code The below code is the best practice that is recommended to use.



01.Try

02.{

03.SqlConnection _sqlConnection = new SqlConnection();

04._sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();

05. 

06.if (_sqlConnection.State = ConnectionState.Closed)

07._sqlConnection.Open();

08. 

09./*Do some Operations … */

10.}

11.catch (Exception ex)

12.{

13./*Do something */

14.}

15.finally

16.{

17.if (_sqlConnection.State != ConnectionState.Closed)      

18._sqlConnection.Close();

19.}

Connection Pooling improves the performance of the data driven B2B applications as we save some time in reusing the existing active database connections. I will be explaining it in more detail in my next article.
Happy BeyondRelationaling !!!