Connection pooling question

[Kirsten]

Suppose I open and close the connection like this:

SqlConnection conn = new SqlConnection(myConnectionString);
try
{
conn.Open();
...doSomething;
}
finally
{
conn.Close();
}

where connection pooling is enabled by default (I mean: I specify nothing in
the connection string like pool size, etc).

- Is the connection to SQL Server actually closed in "conn.Close()" or conn
stays in ConnectionState.Closed and the connection is added to the pool as
available?
- Should I call "conn.Close()" or let .NET close the connection as needed?

Thanks!

 

[William Vaughn \(MVP\)]

Ah, when you don't disable connection pooling in the connectionstring, when
you call Close, the connection to the backend (SQL Server in this case) is
left open for 4-8 minutes. When another instance of the same application (in
the ASP.NET app domain) uses the same connection string, the connection
handle (of the open connection) is passed to the application and at that
point the connection is flushed of its previous state and made available.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Cowboy \(Gregory A. Beamer\)]

Ah, when you don't disable connection pooling in the connectionstring,
when you call Close, the connection to the backend (SQL Server in this
case) is left open for 4-8 minutes. When another instance of the same
application (in the ASP.NET app domain) uses the same connection string,
the connection handle (of the open connection) is passed to the
application and at that point the connection is flushed of its previous
state and made available.

This is my understanding, as well, although I thought the timeout period
from the pool was much shorter than 4-8 minutes. Where is that documented?

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

********************************************************
| Think outside the box! |
********************************************************

 

[William Vaughn \(MVP\)]

I got this figure from Pablo Castro at Microsoft some time ago. I have
verified it several times.
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Paul]

Or if you cannot close the connection immediately implement the dispose
pattern and use
CommandBehavior.CloseConnection on datareaders.