using(SqlConnection = ... ) ?

[Guest]

I've noticed that if I use a statement lik
using(StreamWriter sw = new StreamWriter(@"c:\filetowriteto.txt")
{...

then the file gets released at the end of the using statement block
However if I use this on a database connection
using(SqlConnection cndb = new SqlConnection(@"....")
{...
then unless I explicitly call Close, it doesn't close it. Certainly not with SQ
server connections anyway, they're still visible in Enterprise manager unti
you call close
What is it with IDisposable - what does it actually DO? Is there any goo
description of what resources it frees and what it doesn't
Can you derive from it to define exactly what resources you want to free

 

[Jon Skeet [C# MVP]]

I've noticed that if I use a statement like
using(StreamWriter sw = new StreamWriter(@"c:\filetowriteto.txt"))
{...}

then the file gets released at the end of the using statement block.
Yup.

However if I use this on a database connection,
using(SqlConnection cndb = new SqlConnection(@"...."))
{...}
then unless I explicitly call Close, it doesn't close it. Certainly not with SQL
server connections anyway, they're still visible in Enterprise manager until
you call close.

It will still be available if you call Close, too - and quite
deliberately. Connections to SQL Server are pooled so that you can
create new SqlConnection objects and open and close them very often
without taking up much in the way of actual resources. A SqlConnection
doesn't represent the *real* connection to the database - it just wraps
it, effectively.

What is it with IDisposable - what does it actually DO? Is there any good
description of what resources it frees and what it doesn't?

It releases unmanaged resources. In this case, the unmanaged resource
is the fact that the connection is in use - it only returns it to the
pool.

 

[Guest]

If you call Close() on an SqlConnection, then that makes it disappear from sp_who
or Enterprise Manager. i.e. you're actually telling SQL server that you don't want to us
that connection any more
If you let a using block expire, it doesn't
Damn.

 

[Jon Skeet [C# MVP]]

If you call Close() on an SqlConnection, then that makes it disappear from sp_who2
or Enterprise Manager. i.e. you're actually telling SQL server that
you don't want to use that connection any more.
If you let a using block expire, it doesn't.
Damn.

It certainly doesn't log out the connection - or at least, it didn't
when I last tried it, looking at SQL Server Profiler. I suggest you use
the profiler to see what happens - I've got a test program in the
..adonet newsgroup from about a week ago asking about the same question.

In fact, I've just tried executing sp_who2 myself with my test program,
after opening and closing a connection 3 times - and I see two sleeping
connections. It's only after the process exits that the connections
drop.