connection pooling

this term has been passed around our development team, we have heard that connection pooling is the route we should take for our site application.
The site app will have multiple users hitting the our SQL server simultaneously, how does connection pooling work and should i use it?
Who is Participating?
jay_eireConnect With a Mentor Commented:
Hi takingstandup,
If you plane to have hundreds of users hitting your Web site simultanesusly, and your opening a database connection for each user this can have a BIG impact on the your servers performance.
So you have whats called connection pooling, basically When database connections are pooled, a set of connections is kept open so that they can be shared among multiple users. When you request a new connection, an active connection is removed from the pool. When
you close the connection the connection is placed back in the pool.

Connection stings
For you to use connection pooling, you must ensure that your ASP.NET pages use the same exact connection string whenever you open a database connection, only connections opened with the same connection sting can be placed in the same connection pool.
Connections are pooled only when they are opended with connection strings that exatly match character by charcter. So its a good idea for you to create your connect string in one place and use this same connection within all your ASP.NET pages.
So put your connection string in the web.config file and retrieve it from this file whenever you need to open a connection.

closeing your connection
you have to explicitly close whatever connection you open as quickly as possible, if you dont explicity close a connection the connection is never added back into the connection pool so use the Close() method.

theres a few options you can add into a SQL server connection string that will determine how it will work............
Connection Lifetime - you can destroy a connection after a certain amount of time
Connection Reset - this indicated wheater connections shoulbe rest when the are returned to the pool
Max Pool Size - lets you state the maximum number of connections allowed in a single connection pool, by default its set to 100
Min Pool Size -lets you set the minimum number of connections allowed in a single connection pool, by default its set to 0
Pooling - this is the important one, its determines wheater connection pooling is enabled or disabled.

hope this helps
Jay Eire
ihenryConnect With a Mentor Commented:
William Vaughn has two great articles about this SQL Server connection pooling

Swimming in the .NET Connection Pool

The .NET Connection Pool Lifeguard

And more from MSDN

Connection Pooling for the .NET Framework Data Provider for SQL Server

Connecting to an OLE DB Data Source Using ADO.NET

Connecting to an ODBC Data Source Using ADO.NET
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.