473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Closing connection

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?


Nov 19 '05 #1
7 1779
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.

"Tumurbaatar S." <sp********@magicnet.mn> wrote in message
news:OS**************@TK2MSFTNGP12.phx.gbl...
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?

Nov 19 '05 #2
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.

"Peter Rilling" <pe***@nospam.rilling.net> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
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.

"Tumurbaatar S." <sp********@magicnet.mn> wrote in message
news:OS**************@TK2MSFTNGP12.phx.gbl...
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?


Nov 19 '05 #3
The common consensus is that database connections, unlike other objects,
should be closed explicitly as soon as possible.

Eliyahu

"Tumurbaatar S." <sp********@magicnet.mn> wrote in message
news:OS**************@TK2MSFTNGP12.phx.gbl...
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?

Nov 19 '05 #4
Rob R. Ainscough wrote:
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.

--
http://www.joergjooss.de
mailto:ne********@joergjooss.de
Nov 19 '05 #5
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.
"Joerg Jooss" <ne********@joergjooss.de> wrote in message
news:xn****************@msnews.microsoft.com...
Rob R. Ainscough wrote:
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.

--
http://www.joergjooss.de
mailto:ne********@joergjooss.de

Nov 19 '05 #6
Rob R. Ainscough wrote:
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,
--
http://www.joergjooss.de
mailto:ne********@joergjooss.de
Nov 19 '05 #7
On Tue, 19 Jul 2005 22:59:17 -0700, "Rob R. Ainscough"
<ro*****@pacbell.net> wrote:

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".

--
Scott
http://www.OdeToCode.com/blogs/scott/
Nov 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: flupke | last post by:
Hi, I have a gui (made in wxPython) that enables a user to connect to a server and issue some commands. The problem occurs when i try to disconnect the client. It exits but it doesn't return to...
6
by: Steven Blair | last post by:
Hi, I am writing an application using a 3 tier model (Client, Business Layer and DB) The DB layer creates a OdbcDataReader object back up to the Client where the data is read and displayed on...
0
by: Brent | last post by:
I wrote a simple class to supply a datareader object (code at bottom of post) back to a page. I access it on the APSX page like this: Dim myDR as New getDataReader(strSQL) If myDR.ResultFailed...
13
by: Simon Harvey | last post by:
Hi All, I have a colleague that I wprk with that develops using ASP. I develop using ASP.net. He seems to make sites much faster than me and I am wondering if its because of the two different...
3
by: Paolo Pignatelli | last post by:
I have a Function in a Class File that uses ApplicationBlocks: Public Function GetProductsByCategory(ByVal CategoryID As Integer) Dim myConnection As SqlConnection = New...
7
by: Arsalan | last post by:
I have a function which return datareader Public Shared Function ReturnDReader(ByVal query As String) As OleDbDataReader Dim Connection_String As String =...
7
by: Martien van Wanrooij | last post by:
I have been faced a couple of times with the situation that I wanted to write a script and was worried about a too frequent opening and closing mysql connections. To give some examples: 1)I...
5
by: zacks | last post by:
If I close and dispose an ODBCConnection object, shouldn't the connection actually close? I have found that even after closing and disposing an ODBCConnection, the database it was connected to...
0
by: Vishal Sethia | last post by:
I am trying to write a multi-threaded program and use pexpect along with it. All works fine until I try to close the connection handle. That thread keeps waiting until the connection handle closes....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.