How to ADO.Net Connection Pooling

Fundamental of connection pooling

The creation of a new database connection is a relatively hefty and high resource consuming process. Database developers look for each and every opportunity to fine tune their applications for performance. Applications frequently establish the database connection and close them as soon as they are done. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize this cost of opening connections on each request, ADO.NET uses an optimization technique called connection pooling.

Connection pooling reuses existing active connections with the same connection string instead of creating new connections when a request is made to the database. Actually what happened is , on first request to database, it serves the database call. Once it is done and when the client application requests for closing the connection object, ADO.NET does not destroy the connection object, rather it creates a connection pool and puts the released connection object in the pool and holds the reference to it. Whenever a new Connection becomes opened it asks the connection pool for an existing, and currently not used, internal connection. If an existing, free connection is available ADO.NET wont create a new database connection object but reuse the existing one. As soon as the connection is not needed any more it becomes sent back into the connection pool

A sample connection string

connectionString="Data Source=localhost;Initial Catalog=MyDatabase; Integrated security=SSPI;Connection Timeout=30; Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;";

Every connection pool is associated with a distinct connection string and that too, it is specific to that application. Pooling connections can significantly enhance the performance and scalability of your application.