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();
    }
}

You should read this article. It explains it quite well; http://msdn.microsoft.com/en-GB/library/yh598w02.aspx. In particular, take a look at the code snippet which shows what a "using" statement actually gets turned into by the compiler. — Chris McAtackney, Mar 26 '13 at 14:17
try/catch/finally is performed inside the object with iDisposable, so it's not needed to perform a try/catch/finally in this situation. if you have some other logic that needs the catch/finally for some other purpose, you might still want it. but in this case all you are doing is basically replicating what the disposal will do for you anyway. with the exception that you are swallowing any error in question in the 2nd code example. =) — Roy Ashbrook, Mar 26 '13 at 14:18

score 10 · Accepted Answer · answered Mar 26 '13 at 14:15

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.

but both conn and cmd are disposable? right? which one do i wrap in the using statement? :) — Madam Zu Zu, Mar 26 '13 at 14:17
thank you! so in this case, i still use the conn.Open(), but it is automatically closed when it gets to the end of the using block? correct? — Madam Zu Zu, Mar 26 '13 at 14:22
Although you aren't technically opening a new physical connection, I think there is some kind of reset procedure (sp_reset_connection) that's called which will ensure any local temporary tables are dropped, etc. See: http://stackoverflow.com/a/2924456/88409 — Triynko, Feb 25 '15 at 21:55
@DarinDimitrov so if we use a using block, it will be redundant in code right? Like doing CRUD operations, we always do "using conn" and "using cmd" right? Or is there a way to avoid such redundancy? — paraJdox1, Jan 11 '21 at 07:54

score 0 · Answer 2 · answered Mar 26 '13 at 14:16

You wouldn't have to close the connection if you put it into using blocks. Using blocks are used for objects that implement IDisposable. IDisposable allows an object to clear unmanaged resources before being collected by the GC. This frees up memory and allows the GC to collect that object.

score 0 · Answer 3 · answered Mar 26 '13 at 14:16

A using block is just a try/finally clause with automatic closing and disposing of disposable objects. Adding an internal try/catch serves only if you plan to handle in some way the exception thrown. In your example you do nothing in the catch block, so it is unnecessary.

Both the SqlCommand and SqlConnection are disposable so your code should be changed to

using(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));        
    conn.Open();
    cmd.ExecuteNonQuery();
}

score 0 · Answer 4 · answered Mar 26 '13 at 14:16

You should have a couple of using blocks here, actually, since SqlConnection and SqlCommand both implement IDisposable. And the using also takes care of closing the connection at the end, so the explicit conn.Close(); becomes unnecessary.

    using (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)
        {
            // Log error, etc.
        }
    }

score 0 · Answer 5 · answered Mar 26 '13 at 14:17

It will go this way:

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

        cmd.ExecuteNonQuery();
    }
}

As SqlConnection and SqlCommand implements the IDisposable interface, the using block will deal with the Close and Dispose methods.

Using Block with Stored Procedures

5 Answers5