Editor's Note: Portions of this article are adapted from ADO.NET and ADO Examples and Best Practices for VB Programmers, 2nd Edition (Apress, 2002), by William Vaughn.
As an ADO.NET and Visual Basic (VB) instructor and consultant, I often get questions from clients, students, the newsgroups, and list servers about using ADO.NET's connection pools. People ask questions such as:
- How can I enable and disable the connection pool?
- How many connections are already in the pool?
- ADO.NET and ADO seem to lock up after about 100 connections; why can't they open more connections?
- How can I identify the user executing the code in the connection string without quickly running out of connections?
- How can I make sure only the right people have access to the database and still leverage the connection pool?
- How can I share a common connection between different parts of my application?
After reading this article, you'll know the answers to these and many other connection-pool questions. (No, I won't be discussing pH levels or how to super-chlorinate your connection pool when it gets dirty.) I discuss how to properly connect applications to and, more importantly, disconnect them from the server when the connection pool manages your connections. In an upcoming article, I'll follow up with how to monitor the activity of the connection-pooling mechanism (aka the pooler) and how to make sure your application is using the pooler correctly—preferably before it overflows and shorts out your system.
A Little History
More than 5 years ago, Microsoft introduced pooled connections for all ODBC drivers to address several problems that developers were solving on their own. Developers needed a way to reduce the cost of establishing or reestablishing a connection—it took too long, limited scalability, and consumed too many server-side connections. The basic idea behind pooling is that when an application closes a connection, the connection handle returns to a driver- or provider-managed pool, where it remains for a given length of time so that the application can reuse it. Although this approach isn't as important in client/server applications that use only one connection, it's crucial when you create high-performance middle-tier COM+ or Active Server Pages (ASP) applications that run multiple instances of components that could safely reuse the same connection handle.
Since the first version of connection pooling, every version of ODBC has supported connection pooling by default. SQL Server 2000 resolved many early problems with connection pooling, and Microsoft included the feature in OLE DB and the .NET Framework.
How Does the Connection Pool Work?
When connection pooling is enabled (which it is by default), the data provider draws each connection from a pool of pre-opened, idle connections or makes a new connection to the database and creates a new pool. This means that when you close your connection in code, you're really just releasing the connection back to a pool of used connections; the database connection to the data source remains open and continues to hold resources on the server until the pooler times out the connection and closes it. The timeout takes between 4 and 8 minutes. However, if the same process needs to reconnect, the connection pool simply plugs it back into an existing "hot" connection—assuming the connection string and other factors haven't changed. You might not get the same connection again, but in theory, the connection you get should be functionally equivalent to the original. This means that your application can save the time that it would otherwise use to establish a brand-new connection to the server.
As with any specialized functionality, connection pooling has rules for its use. Here are some common questions you might have about the rules that govern connection pools.
Prev. page
[1]
2
3
4
next page 
