By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,889 Members | 1,373 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,889 IT Pros & Developers. It's quick & easy.

open a connection to the pool

P: n/a
Hello!

How does this connection pool function actually ?
As I have been told is that when the connection is returned to the pool
the connection to the database is still open. What you do is that you just
return a still open connection to the pool.
When I open a connection I get an already open connection to the database.

Is this correct understood ?

//Tony
Jun 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Yes. If pooling is enabled (which it is by default on the SQL Server
provider), then calling Close() / Dispose() simply releases the real
connection to the pool. When you Open() a connection, it checks for
available pool connections with the same connection string, and uses
that. The pooled connection will be fully reset between uses, so you
don't need to worry about anything like orphaned temp tables, unusual
SET statements, etc.

Marc
Jun 27 '08 #2

P: n/a
Setting up the TCP connection between your Web application and SQL Server can
be an expensive operation. Connection pooling allows to reuse connections to
the database. Rather than setting up a new TCP connection on each request, a
new connection is set up only when one is not available in the connection
pool. When the connection is closed, it is returned to the pool where it
remains connected to the database, as opposed to completely tearing down that
TCP connection.

Of course you need to watch out for leaking connections. Always close your
connections when you're finished with them. Always call Close or Dispose
explicitly on your connection when you are finished with it. The CLR will
eventually destroy the class and force the connection closed, but you have no
guarantee when the garbage collection on the object will actually happen.

To use connection pooling optimally, there are a couple of rules to live by.
First, open the connection, do the work, and then close the connection. It's
okay to open and close the connection multiple times on each request if you
have to... rather than keeping the connection open and passing it around
through different methods. Second, use the same connection string (and the
same thread identity if you're using integrated authentication). If you don't
use the same connection string, for example customizing the connection string
based on the logged-in user, you won't get the same optimization value
provided by connection pooling. And if you use integrated authentication
while impersonating a large set of users, your pooling will also be much less
effective. The .NET CLR data performance counters can be very useful when
attempting to track down any performance issues that are related to
connection pooling.
--
Misbah Arefin
https://mcp.support.microsoft.com/profile/MISBAH.AREFIN
http://www.linkedin.com/in/misbaharefin
"Tony Johansson" wrote:
Hello!

How does this connection pool function actually ?
As I have been told is that when the connection is returned to the pool
the connection to the database is still open. What you do is that you just
return a still open connection to the pool.
When I open a connection I get an already open connection to the database.

Is this correct understood ?

//Tony
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.