SqlConnection.Dispose close connection?

  • Thread starter Thread starter Big D
  • Start date Start date
B

Big D

Does calling the sqlConnection.dispose method close any open connections or
must you explicitly close them first?

-MCD
 
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."
 
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???
 
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.
 
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
 
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."
 
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.
Click to expand...

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.
Click to expand...

And that's fine - but that's *not* the same as leaving it to the GC.
 
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
 
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.
Click to expand...

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.
 
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.
Click to expand...

Dmitriy,

This is definitely wrong, you can check this by reading the CLR performance
counters for Sqlclient.

Willy.
 
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
 
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."
 
Back
Top