SqlConnection and SqlCommand practices

[Jon Oskar]

Hi all,

although I'm a seasoned .NET developer I decided to cast my wanderings
to you all...

I'm building a ASP.NET solution and was coding a function in my BL
when this question struck me:
- When using SqlConnection and SqlCommand (which are both disposable)
which is better, to use:
try
{
using( SqlConnection conn = new SqlConnection(connString) )
{
using( SqlCommand comm = new SqlCommand() )
{
//...code lies here...
}
}
}
catch
{}

- OR -

having a try-catch-finally block surrounding my code and disposing the
objects in the finally clause?? or, should I dispose the objects
explicitly at all (just null 'em) ???

your thoughts appreciated on this...

regards,
Jon Oskar

 

[Jon Skeet [C# MVP]]

although I'm a seasoned .NET developer I decided to cast my wanderings
to you all...

I'm building a ASP.NET solution and was coding a function in my BL
when this question struck me:
- When using SqlConnection and SqlCommand (which are both disposable)
which is better, to use:
try
{
using( SqlConnection conn = new SqlConnection(connString) )
{
using( SqlCommand comm = new SqlCommand() )
{
//...code lies here...
}
}
}
catch
{}

- OR -

having a try-catch-finally block surrounding my code and disposing the
objects in the finally clause?? or, should I dispose the objects
explicitly at all (just null 'em) ???

Do you actually need to create a new command each time? I find that for
the vast majority of the time, I can reuse commands again and again.

Other than that, a using block is certainly the way to go, IMO. It's
easier to get right than a try/finally block. I don't think it will do
significant harm to not dispose of SqlCommands, but SqlConnections
really *should* be disposed or closed, otherwise they'll be taking up a
"real" SQL connection from the pool until the garbage collector runs
their finalizers.