SQL Connection Pooling

Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. Connection pool manager maintains a pool of open database connections. When a new connection is requested , pool manager checks if the pool contains any unused connections and returns connection if available.


If all connections in the pool are busy and the maximum pool size has not been reached, then new connection is created and added to the pool. When the pool reaches its maximum size all new connection requests are being queued up until a connection in the pool becomes available or the connection attempt times out.

These are four parameters that control most of the connection pooling behavior

Max Pool Size - the maximum size of your connection pool. Default is 100

Min Pool Size - initial number of connections which will be added to the pool upon its creation. Default is zero

Connect Timeout - controls the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown. Default is 15 seconds.

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.

eg.

connstring="server=myserver;database;abcdefg;Min pool size=5;Max pool size=100;connection timeout=15;pooling=yes"

Most of the Connection problems are because of Connection Leaks

SqlConnection conn=new SqlConnection(constring);
conn.Open();
//do some thing
conn.Close();
while executing the functionality if Exception occurences, then for sure connection wont be closed , to close connection explicitly .. the simple way is ..

SqlConnection conn=new SqlConnection(constring);

try{
conn.Open();
//do some thing
}
finally()
{
conn.Close();
}