Using Block with Stored Procedures

Question

i am trying to get used to working with "using" blocks in C#, but i'm having a hard time understanding when i should be using them.

here is an example.

my original code, without the using block:

SqlConnection conn = new SqlConnection(cCon.getConn());
    SqlCommand cmd = new SqlCommand("sp_SaveSomething", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@x", xxx));
    cmd.Parameters.Add(new SqlParameter("@ORG", ORG));        
    try
    {
        conn.Open();
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    { }
    finally
    {
        conn.Close();
    }

but should i really be doing this? or should i be using(SqlConnection conn = new SqlConnection(cCon.getConn())) ? please help me understand this. is the way i'm originally doing it so wrong?

SqlConnection conn = new SqlConnection(cCon.getConn());
   using( SqlCommand cmd = new SqlCommand("sp_SaveSomething", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@x", xxx));
    cmd.Parameters.Add(new SqlParameter("@ORG", ORG));        
    try
    {
        conn.Open();
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    { }
    finally
    {
        conn.Close();
    }
}

Answer 1

but i'm having a hard time understanding when i should be using them.

It's easy. Everytime you are dealing with a class that implements the IDisposable interface you should use them. Just like this:

using (SqlConnection conn = new SqlConnection(cCon.getConn()))
using (SqlCommand cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "sp_SaveSomething";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@x", xxx));
    cmd.Parameters.Add(new SqlParameter("@ORG", ORG));        
    cmd.ExecuteNonQuery();
}

and if you wanna handle some exceptions you could wrap the code you wanna handle in a try/catch statement:

try
{
    using (SqlConnection conn = new SqlConnection(cCon.getConn()))
    using (SqlCommand cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "sp_SaveSomething";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@x", xxx));
        cmd.Parameters.Add(new SqlParameter("@ORG", ORG));        
        cmd.ExecuteNonQuery();
    }
}
catch (Exception ex)
{
    // do something here with the exception, don't just consume it,
    // otherwise it's meaningless to catch it
}

As you can see all IDisposable resources (SqlConnection and SqlCommand in this code snippet) are now properly wrapped in using statements which guarantees that they will be properly disposed even if an exception is thrown. As a consequence you no longer need to be using a finally statement and explicitly doing this.

Also remember that ADO.NET uses a connection pool meaning that when you are calling the .Open() method on a SqlConnection you are not opening a physical connection to the database. You are simply drawing one from the pool. And when you call the .Close (or .Dispose) method you are not closing the connection. You are simply returning it to the connection pool so that it can be reused.