Timo
Sat Mar 12 07:49:44 CST 2005
Sahil,
The discussion on the codebetter.com page referred to below would is
somehwat ambiguous. Can you please clarify? You wrote:
"Either close, or dispose (by virtue of calling close)..."
The words: "dispose (by virtue of calling close)" -- suggest that the
connection instance will be disposed as an after-effect of a close. Then
you write:
" All it does is - as stated above - releases resources - which is simply
calling close and a bit else as shown below in
SqlConnection.Dispose(Boolean) code ---
The extra work other than simply closing that dispose did above was..."
This suggests an opposite approach, that the dispose first does a close and
then some additional cleanup. So, do I understand you correctly when I
restate the choices as follows?
To close a connection, pooled or unpooled, the programmer has two choices:
a) invoke the connection's close() method. This will close the connection
and the GC will eventually get around to cleaning up the resources held by
the internal sealed class; or
b) invoke only the dispose() method. This method will close the connection
for you and will have the added benefit of freeing up the internal sealed
class's resources somewhat sooner.
Thanks
Timo
"Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
news:#pVXvNlJFHA.4092@tk2msftngp13.phx.gbl...
> Timo,
>
> I confused the Finally block in a try-catch-finally .. with the Finalize
..
> method. My bad !! :-(
>
> Anyway, reading your question again - this time not on drugs -
>
> The connection pool is maintained I think on another thread. There is a
good
> description of how it works over here -
>
http://codebetter.com/blogs/sahil.malik/archive/2004/11/12/31798.aspx . If
> there is a service you could restart to flush the connection pool - well I
> haven't heard of it, but a softie might be able to comment better on that.
>
> Now in your scenario - needless to say, you are leaving too many
connections
> open - which is why the pool size is going through the roof. You have to
> look at your database layer design and general application design to
ensure
> that connections are being closed.
>
> Now, in your description, you mention that you are using Finally blocks to
> close connections. For simplistic cases, that is a good approach, but for
> larger application architectures, you should abstract that out using a
> DataLayer - thus the Finally that encloses a SqlConnection.Dispose, occurs
> inside that one single class, and you always rely on that class to do this
> job for you.
>
> There might be situations where this might not work - like transactional
> scenarios, or datareader scenarios - in those cases too, you should rely
on
> a class that sits on top of that class, to close out an open connection -
in
> other words, your business layer never calls SqlConnection.Close
>
> You might already be doing all this - but the only way to hit the max pool
> size is - connections are not being closed as you had expected them to be.
> That is the only explanation.
>
> - Sahil Malik
>
http://codebetter.com/blogs/sahil.malik/
>
> "Timo" <timo@noneofyer.biz> wrote in message
> news:#l$C07kJFHA.3064@TK2MSFTNGP12.phx.gbl...
> > Sahil,
> > This is not the "best practices" I've seen offered elsewhere. If
there's
> an
> > error in the code after opening the connection, execution will jump over
> the
> > lines where the code closes the connection, to the catch() block. The
> only
> > guaranteed place to close the connection (at runtime, not in debugging
> mode)
> > is in the Finally() block, right?
> > Timo
> >
> >
> > "Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
> > news:eTcXJakJFHA.2752@TK2MSFTNGP10.phx.gbl...
> > > You need to be calling Dispose on SqlConnection objects yourself soon
as
> > you
> > > are done using them. Don't rely on Finally for cleanup.
> > >
> > > - Sahil Malik
> > >
http://codebetter.com/blogs/sahil.malik/
> > >
> > >
> > > "Timo" <timo@noneofyer.biz> wrote in message
> > > news:eNaWHmjJFHA.3336@TK2MSFTNGP10.phx.gbl...
> > > > In the course of debugging applications on my development
workstation
> > > > (ASP.NET 1.1.4322, SQL Server 2000, ADO.NET) the app will sometimes
be
> > > > unable to establish a database connection when I choose
Debug->Start.
> > > > (When
> > > > stopped during debugging, the code does not always reach the Finally
> {}
> > > > block where the connections typically get closed.) I get a timeout
> > error
> > > > indicating that max pool size may have been reached. Stopping and
> > > > restarting
> > > > SQL Server does not clear this problem, and so I have been
rebooting.
> > Is
> > > > there another service that maintains the connection pool, which can
be
> > > > bounced to free the stuck connections?
> > > > Thanks
> > > > Timo
> > > >
> > > >
> > >
> > >
> >
> >
>
>