B
Big D
Does calling the sqlConnection.dispose method close any open connections or
must you explicitly close them first?
-MCD
must you explicitly close them first?
-MCD
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Mickey Williams
Dispose implicitly closes your connection.
K
Kevin Yu [MSFT]
Thanks for Mickey's quick response.
Hi MCD,
Thank you for posting in the community!
First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know if
SqlConnection.Dispose method closes any open connections. If there is any
misunderstanding, please feel free to let me know.
As far as I know, the SqlConnection.Dispose method will close the
connection before disposing the object. However, this method supports the
.NET Framework infrastructure and is not intended to be used directly from
your code. When the reference to the object is out of scope, the object
will be disposed automatically next time the GC is performed.
HTH. If anything is unclear, please feel free to reply to the post.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
Hi MCD,
Thank you for posting in the community!
First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know if
SqlConnection.Dispose method closes any open connections. If there is any
misunderstanding, please feel free to let me know.
As far as I know, the SqlConnection.Dispose method will close the
connection before disposing the object. However, this method supports the
.NET Framework infrastructure and is not intended to be used directly from
your code. When the reference to the object is out of scope, the object
will be disposed automatically next time the GC is performed.
HTH. If anything is unclear, please feel free to reply to the post.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
B
Big D
So you should never call .Dispose in your code???
I thought that unmanaged object should be closed explicitly in code to
insure that GC picks them up in a timely manner. If .Dispose isn't called,
then should you still call .Close???
I thought that unmanaged object should be closed explicitly in code to
insure that GC picks them up in a timely manner. If .Dispose isn't called,
then should you still call .Close???
T
TT \(Tom Tempelaere\)
Hi,
The fact is that the garbage collector in c# is a generational collector. If
your object is in a high generation, it could take long because the
collector will first try to clean up objects in a low generation (currently,
the generations are 0, 1 and 2). Your object would go to a higher generation
if it survives a collect-cycle. So although 5 hours is a lot of time, it is
not impossible at all.
The using keyword is easy for disposing an object you allocated in a
particular method. But a connection could be kept for longer use of course;
then you would call dispose explicitely. Luckily, the connections are pooled
so making a new connection usually does not take long (if the pool still has
open connections of course). The final remark of Sami is very good:
exception safety. Very important.
The fact is that the garbage collector in c# is a generational collector. If
your object is in a high generation, it could take long because the
collector will first try to clean up objects in a low generation (currently,
the generations are 0, 1 and 2). Your object would go to a higher generation
if it survives a collect-cycle. So although 5 hours is a lot of time, it is
not impossible at all.
The using keyword is easy for disposing an object you allocated in a
particular method. But a connection could be kept for longer use of course;
then you would call dispose explicitely. Luckily, the connections are pooled
so making a new connection usually does not take long (if the pool still has
open connections of course). The final remark of Sami is very good:
exception safety. Very important.
S
Sami Vaaraniemi
It makes no big difference whether you call SqlConnection.Close or
SqlConnection.Dispose. Dispose calls Close anyway.
When you are done with the SqlConnection, you must call either Close or
Dispose. By doing so you ensure that the unmanaged resource (i.e., the
physical SQL connection) is released in a timely manner. If you fail to call
Close/Dispose, then GC will pick up the object and the resource will be
released *eventually*. Problem is, that eventually could be 5 hours from now
which is probably way too late.
Note also that if you have connection pooling on, then SqlConnection.Close
does not actually close the physical connection. Instead, it just returns it
to the connection pool so it's available for reuse.
Finally, the best way to use and close SqlConnections (and other objects
that wrap unmanaged resources) is with the 'using' keyword:
using (SqlConnection conn = new SqlConnection(...))
{
// use conn here
}
This is good because it guarantees that Dispose (and hence Close) will be
called when the object goes out of scope, even if there is an exception.
Regards,
Sami
www.capehill.net
SqlConnection.Dispose. Dispose calls Close anyway.
When you are done with the SqlConnection, you must call either Close or
Dispose. By doing so you ensure that the unmanaged resource (i.e., the
physical SQL connection) is released in a timely manner. If you fail to call
Close/Dispose, then GC will pick up the object and the resource will be
released *eventually*. Problem is, that eventually could be 5 hours from now
which is probably way too late.
Note also that if you have connection pooling on, then SqlConnection.Close
does not actually close the physical connection. Instead, it just returns it
to the connection pool so it's available for reuse.
Finally, the best way to use and close SqlConnections (and other objects
that wrap unmanaged resources) is with the 'using' keyword:
using (SqlConnection conn = new SqlConnection(...))
{
// use conn here
}
This is good because it guarantees that Dispose (and hence Close) will be
called when the object goes out of scope, even if there is an exception.
Regards,
Sami
www.capehill.net
K
Kevin Yu [MSFT]
Hi MCD,
I didn't mean that we should never call .Dispose in our code. I meant that
we have to avoid calling it explicitly in our code, and leave the .Dispose
to the GC.
Sami has provided us with a good suggestion with "using" statement. So that
we ensure the object is disposed.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
I didn't mean that we should never call .Dispose in our code. I meant that
we have to avoid calling it explicitly in our code, and leave the .Dispose
to the GC.
Sami has provided us with a good suggestion with "using" statement. So that
we ensure the object is disposed.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
J
Jon Skeet [C# MVP]
Kevin Yu said:I didn't mean that we should never call .Dispose in our code. I meant that
we have to avoid calling it explicitly in our code, and leave the .Dispose
to the GC.
No no no! Leaving it to the GC means it'll hang around until the GC
gets round to finalizing it, which is a really bad idea.
Sami has provided us with a good suggestion with "using" statement. So that
we ensure the object is disposed.
And that's fine - but that's *not* the same as leaving it to the GC.
D
Dmitriy Lapshin [C# / .NET MVP]
By the way - I've heard somewhere that calling Dispose() on an SqlConnection
instance removes this connection from the connection pool, whereas Close()
just returns the connection to the pool.
--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://www.x-unity.net/teststudio.aspx
Bring the power of unit testing to VS .NET IDE
instance removes this connection from the connection pool, whereas Close()
just returns the connection to the pool.
--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://www.x-unity.net/teststudio.aspx
Bring the power of unit testing to VS .NET IDE
J
Jon Skeet [C# MVP]
Dmitriy Lapshin said:By the way - I've heard somewhere that calling Dispose() on an SqlConnection
instance removes this connection from the connection pool, whereas Close()
just returns the connection to the pool.
I had heard the same thing, but I haven't seen any particular reason to
believe it, and it would be a very odd decision to make, IMO.
W
Willy Denoyette [MVP]
Dmitriy Lapshin said:By the way - I've heard somewhere that calling Dispose() on an
SqlConnection
instance removes this connection from the connection pool, whereas Close()
just returns the connection to the pool.
Dmitriy,
This is definitely wrong, you can check this by reading the CLR performance
counters for Sqlclient.
Willy.
S
Sami Vaaraniemi
I think what you've heard is simply wrong.
MSDN documentation about ADO.NET connection pooling says e.g., "Connections
are released back into the pool when you call Close or Dispose on the
Connection."
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/ht
ml/cpconconnectionpoolingforsqlservernetdataprovider.asp)
Further, if you look at the implementation of SqlConnection.Dispose (with
Anakrino or some other decompiler), you'll see that SqlConnection.Dispose
calls SqlConnection.Close. So there is no practical difference in whether
you call Close or Dispose.
Regards,
Sami
www.capehill.net
MSDN documentation about ADO.NET connection pooling says e.g., "Connections
are released back into the pool when you call Close or Dispose on the
Connection."
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/ht
ml/cpconconnectionpoolingforsqlservernetdataprovider.asp)
Further, if you look at the implementation of SqlConnection.Dispose (with
Anakrino or some other decompiler), you'll see that SqlConnection.Dispose
calls SqlConnection.Close. So there is no practical difference in whether
you call Close or Dispose.
Regards,
Sami
www.capehill.net
K
Kevin Yu [MSFT]
Hi MCD,
I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
R
Ravichandran J.V.
Calling the Dispose method will close the connection implicitly.
with regards,
J.V.Ravichandran
- http://www.geocities.com/
jvravichandran
- http://www.411asp.net/func/search?
qry=Ravichandran+J.V.&cob=aspnetpro
- http://www.southasianoutlook.com
- http://www.MSDNAA.Net
- http://www.csharphelp.com
- http://www.poetry.com/Publications/
display.asp?ID=P3966388&BN=999&PN=2
- Or, just search on "J.V.Ravichandran"
at http://www.Google.com
with regards,
J.V.Ravichandran
- http://www.geocities.com/
jvravichandran
- http://www.411asp.net/func/search?
qry=Ravichandran+J.V.&cob=aspnetpro
- http://www.southasianoutlook.com
- http://www.MSDNAA.Net
- http://www.csharphelp.com
- http://www.poetry.com/Publications/
display.asp?ID=P3966388&BN=999&PN=2
- Or, just search on "J.V.Ravichandran"
at http://www.Google.com
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.