Connection Pooling in ADO.NET

Connecting to any database from code is a time consuming process. Of course,may not be more than few milliseconds but when thousands of users are accessing your website it will affect the performance of the application.Similar database connections will be opened and closed to perform the client requests.

To optimize the creation and utilization of data base connections ADO.NET provided a concept called connection pooling.If connection polling is enabled and user calls connection Open, instead of directly creating the connection, the ADO.NET will first check if there are any active connections in the pool,If any active connections are available it will return the connection from the pool otherwise a new connection will be created.When the application Close the connection it will be returned to the pool.

Connection pools are created based on Connection strings.Two connection pools will be created if two connection strings differed even in the order of connection properties though their values are same.

By Default connection Pooling is Enabled in ADO.NET

Below are the important Connection Pooling attributes or properties.

Pooling:When its true,A connection will be created in the first request and will be returned to the pool after its usage.

Connection Lifetime (or Load Balance Timeout): Life time of the active connections in the pool.If a connection exceeds this value then connection will be destroyed. This time out is useful in load balance scenarios.

Default value is 0 seconds. Value 0 causes the maximum time out for connections.

Connection Timeout: Maximum time the connection waits for response to be returned by the server. Default value is 15 seconds.

Enlist: When Enlist is true, pooler enlists the connection in creation threads current transaction context.

Max Pool Size: Indicates the maximum number of connections allowed inside the connection pool. Default value is 100. Max Pool Size should always be greater than or equal to Min Pool Size.

Min Pool Size:Indicates the minimum number of connections allowed inside the pool.Default value is 0. Min Pool Size should always be less than or equal to Max Pool Size.

Sample connection string with connection pooling:

<add name="SampleConnectionString" connectionString="Data Source=localhost;Initial Catalog=SampleDataBase;Persist Security Info=True;User ID=sa;Password=Sky#1;Connection Timeout=30;Min Pool Size=5;Max Pool Size=20;Pooling=True;Connection Lifetime=0;Enlist=True;" providerName="System.Data.SqlClient" />