Using

[Peter Kirk]

Hi

I have some code in C# for accessing a database. For example:

using (SqlConnection conn = GetConnection() )
{
using (SqlCommand command = new SqlCommand( ...
{
...
}
}

(The function GetConnection just delivers the appropriate connection object,
and of course there is a lot of code snipped).

My question concerns the "using" statement. I just want to be sure that my
understanding is correct: it is not necessary for me to explicitly close
these database resources because the using statement ensures that these
objects have a Dispose function which is called, and these take care of
cleaning up? (Even if an exception is thrown?)

Thanks,
Peter

 

[Richard Blewett [DevelopMentor]]

Yes, that is correct

Regards

Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

Hi

I have some code in C# for accessing a database. For example:

using (SqlConnection conn = GetConnection() )
{
using (SqlCommand command = new SqlCommand( ...
{
...
}
}

(The function GetConnection just delivers the appropriate connection object,
and of course there is a lot of code snipped).

My question concerns the "using" statement. I just want to be sure that my
understanding is correct: it is not necessary for me to explicitly close
these database resources because the using statement ensures that these
objects have a Dispose function which is called, and these take care of
cleaning up? (Even if an exception is thrown?)

Thanks,
Peter



[microsoft.public.dotnet.languages.csharp]

 

[Michael S]

My question concerns the "using" statement. I just want to be sure that my
understanding is correct: it is not necessary for me to explicitly close
these database resources because the using statement ensures that these
objects have a Dispose function which is called, and these take care of
cleaning up? (Even if an exception is thrown?)

Thanks,
Peter

Yes, Peter. The using keyword is a shorthand for a try finally block which
calls Dispose. Dispose will call Close.

Hence, the connection will be closed even if your code throws an exception.

Better yet, you don't need nested using-statements for each sql-object. The
sql client classes are pretty smart.
If you create a connection in using block, create a command, hook it to your
connection and then perhaps call ExecuteReader you don't need to dispose all
those objects. The SqlConnection will not only dispose itself but the
command and the reader as well.

Hence, one using statement would be sufficient.

Happy Coding
- Michael S

 

[Guest]

Although it's not a bad idea to explicitly close the other objects - command,
reader, or whatever.

Also you might get better performance by only calling Close vs. Dispose,
especially if your app has a lot of interaction with the database -
SqlConnection may reuse the connection - calling Dispose definatly kills the
connection and destroy's the object, meaning that a new one must be created
each time.

 

[Richard Blewett [DevelopMentor]]

Why oh why oh why does this myth persist.

Here is the code for SqlConnection.Dispose reverse engineered via Reflector


protected override void Dispose(bool disposing)
{
if (disposing)
{
switch (this._objectState)
{
case ConnectionState.Open:
{
this.Close();
break;
}
}
this._constr = null;
}
base.Dispose(disposing);
}



In other words, if the connection is open it closes it, then it nulls out the connection string. All this means is you can't call
Open on that instance without reseting the connection string. The underlying connection just returns to the pool as with Close.



Whoever started the rumour that the connection is somehow removed from the pool / permanently closed or
whatever has alot to answer for.



Regards



Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk


Although it's not a bad idea to explicitly close the other objects - command,
reader, or whatever.

Also you might get better performance by only calling Close vs. Dispose,
especially if your app has a lot of interaction with the database -
SqlConnection may reuse the connection - calling Dispose definatly kills the
connection and destroy's the object, meaning that a new one must be created
each time.