Closing connection

[Tumurbaatar S.]

Is it so important to close database connections?
As I understand, after processing a request and sending a response,
page object destroyed. So all used connections also destroyed.
Yes?

 

[Peter Rilling]

Theoretically objects should clean themselves and release any resources when
they are disposed of. There are a couple problems with this practice.

1) Finalization is non-deterministic. Meaning that you do not know when
the server destroys the objects.
2) Do you really want to trust the implementation of some black-box
component to clean itself. If you do it yourself, then you know that it is
done.

 

[Rob R. Ainscough]

Peter,

But the problem with ADO.NET is that closing a connection (even explicitly)
may not immediately close the connection (you can see this in real time at
the server side). But then again, a more serious problem IMHO is that
developers have to deal with connection state at all -- cause as you said it
is now a black box that we "think" we have control over, but in reality we
"partially" have some control but not the final control.

I wish MS would offer those of us that are NOT interested in connection
management at least an alternative -- a nice property on the Connection
object -- perhaps "AutoManage" = True or False. For those that enjoy and
feel it necessary to manage connections can, and for those that really have
better things to do than connection management, don't have to. Both worlds
are now happy -- of course that means MS would need to more work to make
this a reality -- I think they can afford it

Rob.

 

[Eliyahu Goldin]

The common consensus is that database connections, unlike other objects,
should be closed explicitly as soon as possible.

Eliyahu

 

[Joerg Jooss]

Peter,

But the problem with ADO.NET is that closing a connection (even
explicitly) may not immediately close the connection (you can see
this in real time at the server side). But then again, a more
serious problem IMHO is that developers have to deal with connection
state at all -- cause as you said it is now a black box that we
"think" we have control over, but in reality we "partially" have some
control but not the final control.

I wish MS would offer those of us that are NOT interested in
connection management at least an alternative

[...]

That basically translates to sloppy programming that produces resource
leaks.

Sorry.

 

[Rob R. Ainscough]

Having MS manage the connection = resource leak?

I don't follow you -- how is closing a connection (and it not being really
closed on the server end) sloppy programming? Unless you're refering the MS
programming?

Forcing developers to perform connection management does OPEN the door for
sloppy programming (a door in which we should have the option to keep
closed) -- yes I agree with that and exactly why MS should provide an option
to deal with it automatically and efficiently -- rather than the
inconsistant approach they use now.

Rather than the current method to try to close a connection:
close the datareader
close the connection
loop thru connectionstate to ensure it gets closed or timesout
dispose of the connection
and for good measure set it to Nothing
and if you're really thorough, then initial GS (garbage collection)

Then and only then, can you be pretty sure your connection is closed.
That's a LOT of work to ensure a connection gets closed -- and since it is
recommended to close connections ASAP, the developer's are encouraged to
open/close many many many times -- opening a closing connections A LOT is
also not efficient.

Rob.

Peter,

But the problem with ADO.NET is that closing a connection (even
explicitly) may not immediately close the connection (you can see
this in real time at the server side). But then again, a more
serious problem IMHO is that developers have to deal with connection
state at all -- cause as you said it is now a black box that we
"think" we have control over, but in reality we "partially" have some
control but not the final control.

I wish MS would offer those of us that are NOT interested in
connection management at least an alternative

[...]

That basically translates to sloppy programming that produces resource
leaks.

Sorry.

 

[Joerg Jooss]

Having MS manage the connection = resource leak?

I don't follow you -- how is closing a connection (and it not being
really closed on the server end) sloppy programming? Unless you're
refering the MS programming?

No, *not* closing a connection (and relying on finalization) is sloppy
programming.

Forcing developers to perform connection management does OPEN the
door for sloppy programming (a door in which we should have the
option to keep closed) -- yes I agree with that and exactly why MS
should provide an option to deal with it automatically and
efficiently -- rather than the inconsistant approach they use now.

[What inconsistent approach?]

There is simply no way of automagically closing (or rather releasing,
see below) connections efficiently in .NET Or Java as of today, due to
the unmanaged nature of these resouces. You can do it either explicitly
or rely on (read: hope for) finalization. The latter option is just
plain bad.

Rather than the current method to try to close a connection:
close the datareader
close the connection
loop thru connectionstate to ensure it gets closed or timesout
dispose of the connection
and for good measure set it to Nothing
and if you're really thorough, then initial GS (garbage collection)

Note that this is all boilerplate code that can be easily abstracted
away. It's just a matter of application architecture.

Then and only then, can you be pretty sure your connection is closed.
That's a LOT of work to ensure a connection gets closed -- and since
it is recommended to close connections ASAP, the developer's are
encouraged to open/close many many many times -- opening a closing
connections A LOT is also not efficient.

Welcome to the world of connection pooling. A proper ADO.NET data
provider will not simply close and reestablish physical connections,
but pool them. In this context, "close" means actually "release and
return to the pool", whereas "open" means "obtain from the pool".

Cheers,

 

[Scott Allen]

I wish MS would offer those of us that are NOT interested in connection
management at least an alternative -- a nice property on the Connection
object -- perhaps "AutoManage" = True or False. For those that enjoy and
feel it necessary to manage connections can, and for those that really have
better things to do than connection management, don't have to. Both worlds
are now happy -- of course that means MS would need to more work to make
this a reality -- I think they can afford it

Most providers provide a way to turn off connection pooling, i.e.
'Pooling=false' in the connection string for SQLConnection.

I would never do this unless I had a really, really good reason. I'd
need a much better reason than "I enjoy managing connections".