Closing Database Connection In Dot NET 2.0.

Closing Database Connection In Dot NET 2.0.
===========================================

Its recommended to close connection after you utilize the resource. This was not done before (especially during the age of VB and other old application). Previously opening and closing the connection was more resource consuming hence during application startup connection was opened (As in visual basic sub main function) and it was only closed during application closure. Hence whenever application starts connection remains open even if use isn't doing anything. The major problem in keeping the connection open is...since database allows limited no of connections hence keeping the connection open without performing any database operations/actions would just waste resource. In addition when the no of connections reaches the limit it won't allow other connection to open....

Eg: This is just like storing gold ornaments without wearing it.

Hence computer scientist have come up with a new idea which is called connection pooling. Connection pooling is just like a shared connection, where database connections are created and held in a pool. A pool is created when certain connections remain open for utilization by various sessions. Whenever any application requires a connection, the provider extracts the next available connection from the pool. (Whenever a open method is called directly available connection is taken from the pool. Hence there is no resource over head at all)

Also as soon as the application closes the connection, it is returned to the pool and made available for the next application that requires connectivity. Hence opening and closing connection is just like taking charge of the resource that's it!!..hence its recommended to open the connection whenever you need it and close it as soon as you don't require it..This facilitates optimal utilization of connection resource.

Eg: This is just like sharing your gold ornaments with other relatives.


Sample connection string in dot net:
connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Integrated Securing=SSPI;Min Pool Size=10";

Some of the connection string settings include:

Max Pool Size : Max No of connection allowed in the pool (default is 100)
Min Pool Size : Min No of connection retained in the pool (default is 0). The no of connections will be created when the first connection is opened, leading to a minor delay for the first request.
Pooling: When true(default), the connection is drawn from the approiate pool or if necessary is created and added to the appropriate pool
Connection Lifetime: Specifies a time interval in seconds. If a connection is returned to the pool and its creation time is older than the specified lifetime, it will be destroyed. The default is 0.(disable). This feature is useful when you want to recycle a large no of connections at once.

Also programatically you can clear pool by using methods like:
ClearPool()
ClearAllPools()

The above may be necessary when pool is full with unnecessary connections.