Connection pools are actually containers that contain open and
reusable connections. Multiple pools can exist in the same application domain
at the same point in time, but Connection Pools cannot be shared across
application domains. Note that one pool is created per unique connection
string. A Connection Pool is created the first time a request for a connection
to the database comes in with a unique connection string. Note that if another
request comes in with a different connection string for the database, another
Connection Pool would be created. Hence, we have one Connection Pool per
connection string and not per database. The following code listings below
illustrate this.
Listing 1
// A new pool is created.
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
"Server=localhost;Database=test;User ID=joydip;Password=joydip;Trusted_Connection=False";
sqlConnection.Open();
Listing 2
// A new pool is created as the connection strings differ.
SqlConnection conn = new SqlConnection();
sqlConnection.ConnectionString =
"Server=localhost;Database=test;User ID=test;Password=test;Trusted_Connection=False";
sqlConnection.Open();
Listing 3
// The connection string is the same as in Listing 1 so no new pool is created.
SqlConnection conn = new SqlConnection();
sqlConnection.ConnectionString =
"Server=localhost;Database=test;User ID=joydip;Password=joydip;Trusted_Connection=False";
sqlConnection.Open();
When a request for a new connection comes in the connection
is served from the Connection Pool without creating a new one, i.e., the
connections are re-used without creating new ones. Therefore, it improves the
performance and scalability of your applications. When your application closes
an open connection, it is returned to the pool where it waits until a reconnect
time out period expires. This is the period within which it waits to connect to
the same database using the same credentials. If none comes in within this
period, the connection to the database is closed and the connection instance is
removed from the pool.
When a new Connection Pool is created, the connection
objects are placed in the pool and the pool and the connections contained in it
are made available for immediate use. The pool can house connections up to the
maximum limit as specified in the connection string that was used to connect to
the database. Connections are removed from the pool when they remain inactive for
a long period of time or have outlived its specified life time or server
connectivity.
A Connection Pool is maintained internally by the Connection
Pool Manager. When a request for a subsequent connection comes in, the
Connection Pool Manager searches the pool for the availability of a free
connection and returns it to the application if one is available. The following
points elaborate how the Connection Pool Manager works- the operations that it performs
when a request for a new connection comes to it.
·
If any unused connection is available, it returns one.
·
If all connections are used up, a new connection is created and
added to the pool.
·
If the number of connections reaches the maximum number of
connections in the pool, the requests are queued until a connection becomes
free for reuse.
Connection Pooling is controlled and the parameters passed
to a connection string that basically comprises the following:
·
Connect Timeout
·
Min Pool Size
·
Max Pool Size
·
Pooling
In order to use Connection Pooling efficiently, remember to
close the connections once you are done with it so that it returns to the pool.