Managing Database Connections

Database connections represent a critical, expensive, and limited resource, particularly in a multitier Web application. It is imperative that you manage your connections correctly because your approach can significantly affect the overall scalability of your application. Also, think carefully about where to store connection strings. You need a configurable and secure location.
When managing database connections and connection strings, you should strive to:

Help realize application scalability by multiplexing a pool of database connections across multiple clients.
Adopt a configurable and high performance connection pooling strategy.
Use Windows authentication when accessing SQL Server.
Avoid impersonation in the middle tier.
Store connection strings securely.
Open database connections late and close them early.

This section discusses connection pooling and will help you choose an appropriate connection pooling strategy. This section also considers how you should manage, store, and administer your database connection strings. Finally, this section presents two coding patterns that you can use to help ensure that connections are closed reliably and returned to the connection pool.

Shashi Ray