Introduction

ASP.Net database connection pooling allows an application to reuse the existing connection collected in the database connection pooling. It makes the web application to reuse the stored the database connection rather than establishing a new connection with database to transfer the data between web application and database. This database connection approach improves the scalability of web application by managing the limited number of connections that can handle the multiple client requests.

Further we will learn the SQL Server 2005 connectivity with ASP.Net applications with appropriate examples. We will design ADO.Net Data Access Layer to provide get and set functionality to perform various actions on database. Now what’s next? We will go for a brief study of connection pooling in SQL Server .Net Data Provider.

Connection Pooling in SQL Server .Net Data Provider

SQL Server .Net Data provider supports connection pooling. It is considered as the most efficient mechanism implemented by the provider internally within the managed code. Pools are created on per application domain basis and remain live until the application is running and destroyed when the application is unloaded.

You can use connection pooling technique to fine tune your ASP.Net web application but you must have knowledge about how pools are managed and various configuration options of connection pooling.

In number of ASP.Net application default connection pooling settings for the SQL Server .Net Data Provider may be sufficient but sometime in large we applications it becomes necessary to test the traffic size patterns for the application to verify if the modifications to the connection pooling is required.

While developing the ASP.Net web application developer should keep in mind that open the database connections as late as possible and close the database connection immediately after completing the data retrieval. When connection is closed it is collected back into the connection pool keeping the actual database connection open. But if the connection pooling is disabled then it closes the actual database connection also.

Quick tip for managed code and managed connection pooling is you should not rely on garbage collector to free connections because it does not automatically close the connection and causes in connection pooling leakage and results in database connection errors and exceptions when new connection is requested.