Creating and opening a connection to a database is very expensive operation (In time & resource). To handle that problem we should use connection pooling that holds a number of existing connections. Each connection pooling has a minimum pool size and maximum pool size. The minimum pool size is use to determine the minimum number of connection that the pool must hold, and the maximum pool size define the maximum connection that the pool can hold at the same time. If the pool reaches to maximum connection, the next request will be added to a waiting queue until another connection is return to the pool and become available.
Base rules to implement connection pooling:
- The connection string must be the same one in all connections.
- The user ID must be the same one in all requests.
- You can't share connections across processes (always use the same process ID).
The connection pooling is being created only in the first access to the pooling.
When a connection is return to the connection pool, it has an idle lifetime of 4 – 8 minutes before its being released (it is happening only if the minimum connection number isn't lower than the minimum pool size).
How to identify connection leaks?
You can use the performance monitoring to define the NumberOfPooledConnection counter. If the number of pooled connection rises uncontrollably, you should check if you closed the connection properly.
You have some ways to close the connection like:
- Implementing try-catch-finally block(and close the connection in the finally section)
- Implementing using block (This is the elegant way, it will call the dispose method automatically).
When a connection to the server fall down or the server become unavailable, the connections in the pool becomes corrupted. To recover the connections you can use the ClearPool() or ClearAllPools() Methods(a static methods in connection classes).