150

First question:
Say I have

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string storedProc = "GetData";
    SqlCommand command = new SqlCommand(storedProc, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));

    return (byte[])command.ExecuteScalar();
}

Does the connection get closed? Because technically we never get to the last } as we return before it.

Second question:
This time I have:

try
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        int employeeID = findEmployeeID();

        connection.Open();
        SqlCommand command = new SqlCommand("UpdateEmployeeTable", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));
        command.CommandTimeout = 5;

        command.ExecuteNonQuery();
    }
}
catch (Exception) { /*Handle error*/ }

Now, say somewhere in the try we get an error and it gets caught. Does the connection still get closed? Because again, we skip the rest of the code in the try and go directly to the catch statement.

Am I thinking too linearly in how using works? ie Does Dispose() simply get called when we leave the using scope?

jball
  • 24,791
  • 9
  • 70
  • 92
Marcus
  • 5,407
  • 3
  • 31
  • 54

8 Answers8

190
  1. Yes
  2. Yes.

Either way, when the using block is exited (either by successful completion or by error) it is closed.

Although I think it would be better to organize like this because it's a lot easier to see what is going to happen, even for the new maintenance programmer who will support it later:

using (SqlConnection connection = new SqlConnection(connectionString)) 
{    
    int employeeID = findEmployeeID();    
    try    
    {
        connection.Open();
        SqlCommand command = new SqlCommand("UpdateEmployeeTable", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));
        command.CommandTimeout = 5;

        command.ExecuteNonQuery();    
    } 
    catch (Exception) 
    { 
        /*Handle error*/ 
    }
}
radbyx
  • 9,352
  • 21
  • 84
  • 127
David
  • 72,686
  • 18
  • 132
  • 173
  • 12
    Question: Do I need to even OPEN a connection when using the Using statement? – Fandango68 Nov 27 '14 at 23:54
  • 3
    Also if you're using transactions, by having the `try catch` within the `using` you can explicitly `.Commit` or `.Rollback` transactions in the `catch`. This is both more readable and explicit, and permits you to commit if that makes sense given the type of the exception. (Transactions implicitly roll back on `conn.Close` if not committed.). – Chris May 02 '15 at 11:18
  • 10
    @Fernando68 Yes, you still have to `Open` the connection. `using` only guarantees that the object's `Dispose` method is called. – juharr Aug 24 '15 at 13:50
  • I have the return ExecuteScalar inside using blocks. And when I run the method second time it is very quick, like the connection was open. WHy is it so quick the second time? – positive perspective Jan 22 '19 at 19:24
  • 1
    @positiveperspective - Look up [Connection Pooling](https://www.progress.com/tutorials/ado-net/net-connection-pooling) – David Jan 23 '19 at 03:57
  • @David Will connection be closed even if we re-throw exception within the catch block ? I mean if we re-throw exception, will the closing of "using" be ever reached? – Nina Mar 08 '20 at 09:01
  • What about SqlCommand? Shouldn't we also use "using (SqlCommand command = new SqlCommand("UpdateEmployeeTable", connection)? – ehtio Aug 03 '23 at 10:26
50

Yes to both questions. The using statement gets compiled into a try/finally block

using (SqlConnection connection = new SqlConnection(connectionString))
{
}

is the same as

SqlConnection connection = null;
try
{
    connection = new SqlConnection(connectionString);
}
finally
{
   if(connection != null)
        ((IDisposable)connection).Dispose();
}

Edit: Fixing the cast to Disposable http://msdn.microsoft.com/en-us/library/yh598w02.aspx

Ryan Pedersen
  • 3,177
  • 27
  • 39
  • it isn't exactly that, but it is close enough. the exact difference isn't important. – Bryan Jan 17 '11 at 21:15
  • @Bryan didn't get it, can you please mention the exact difference, can help us lean more :-) – mohits00691 Jul 14 '12 at 14:16
  • Wow, that was a comment made a long time ago :) It looks as if there was an edit the day after I made that comment. I think that is the difference I was thinking of. – Bryan Jul 17 '12 at 21:57
  • @Bryan Yes, I fixed made the adjustment after your comment. – Ryan Pedersen Jul 22 '12 at 09:17
  • Do we always use SqlConnection connection = new SqlConnection(connectionString) everytime we do other operations? say CRUD operations? everytime we insert data we do SqlConnection connection = new SqlConnection(connectionString)? – paraJdox1 Jan 12 '21 at 05:24
19

Here is my Template. Everything you need to select data from an SQL server. Connection is closed and disposed and errors in connection and execution are caught.

string connString = System.Configuration.ConfigurationManager.ConnectionStrings["CompanyServer"].ConnectionString;
string selectStatement = @"
    SELECT TOP 1 Person
    FROM CorporateOffice
    WHERE HeadUpAss = 1 AND Title LIKE 'C-Level%'
    ORDER BY IntelligenceQuotient DESC
";
using (SqlConnection conn = new SqlConnection(connString))
{
    using (SqlCommand comm = new SqlCommand(selectStatement, conn))
    {
        try
        {
            conn.Open();
            using (SqlDataReader dr = comm.ExecuteReader())
            {
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Console.WriteLine(dr["Person"].ToString());
                    }
                }
                else Console.WriteLine("No C-Level with Head Up Ass Found!? (Very Odd)");
            }
        }
        catch (Exception e) { Console.WriteLine("Error: " + e.Message); }
        if (conn.State == System.Data.ConnectionState.Open) conn.Close();
    }
}

* Revised: 2015-11-09 *
As suggested by NickG; If too many braces are annoying you, format like this...

using (SqlConnection conn = new SqlConnection(connString))
   using (SqlCommand comm = new SqlCommand(selectStatement, conn))
   {
      try
      {
         conn.Open();
         using (SqlDataReader dr = comm.ExecuteReader())
            if (dr.HasRows)
               while (dr.Read()) Console.WriteLine(dr["Person"].ToString());
            else Console.WriteLine("No C-Level with Head Up Ass Found!? (Very Odd)");
      }
      catch (Exception e) { Console.WriteLine("Error: " + e.Message); }
      if (conn.State == System.Data.ConnectionState.Open) conn.Close();
   }

Then again, if you work for EA or DayBreak games, you can just forgo any line-breaks as well because those are just for people who have to come back and look at your code later and who really cares? Am I right? I mean 1 line instead of 23 means I'm a better programmer, right?

using (SqlConnection conn = new SqlConnection(connString)) using (SqlCommand comm = new SqlCommand(selectStatement, conn)) { try { conn.Open(); using (SqlDataReader dr = comm.ExecuteReader()) if (dr.HasRows) while (dr.Read()) Console.WriteLine(dr["Person"].ToString()); else Console.WriteLine("No C-Level with Head Up Ass Found!? (Very Odd)"); } catch (Exception e) { Console.WriteLine("Error: " + e.Message); } if (conn.State == System.Data.ConnectionState.Open) conn.Close(); }

Phew... OK. I got that out of my system and am done amusing myself for a while. Carry on.

ShaneLS
  • 466
  • 6
  • 14
  • 6
    Did you know you can stack using statements without additional braces? Delete the last brace, then place the using statements next to each other :) – NickG Aug 28 '14 at 14:42
  • Yes Sir. Thank you. I am aware but wanted my code to show exactly what was happening without using too many other short cuts. Good note to add to the end readers though. – ShaneLS Nov 09 '15 at 15:32
  • Why do you use `conn.Close();` at the end? Doesn't `using` statement do that for you via disposing? – Fredrick Gauss Mar 11 '16 at 16:37
  • I believe it does now (since .net 3.5). It was unclear to me early on with .net 2.0 so I just made it a habit to check and close. – ShaneLS Apr 06 '16 at 14:14
  • c# supports multiple parameters now so you can remove the nesting and double using statement. – Neil Walker Jun 08 '18 at 08:30
5

Dispose simply gets called when you leave the scope of using. The intention of "using" is to give developers a guaranteed way to make sure that resources get disposed.

From MSDN:

A using statement can be exited either when the end of the using statement is reached or if an exception is thrown and control leaves the statement block before the end of the statement.

overstood
  • 985
  • 6
  • 7
5

Using generates a try / finally around the object being allocated and calls Dispose() for you.

It saves you the hassle of manually creating the try / finally block and calling Dispose()

VoodooChild
  • 9,776
  • 8
  • 66
  • 99
3

In your first example, the C# compiler will actually translate the using statement to the following:

SqlConnection connection = new SqlConnection(connectionString));

try
{
    connection.Open();

    string storedProc = "GetData";
    SqlCommand command = new SqlCommand(storedProc, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));

    return (byte[])command.ExecuteScalar();
}
finally
{
    connection.Dispose();
}

Finally statements will always get called before a function returns and so the connection will be always closed/disposed.

So, in your second example the code will be compiled to the following:

try
{
    try
    {
        connection.Open();

        string storedProc = "GetData";
        SqlCommand command = new SqlCommand(storedProc, connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));

        return (byte[])command.ExecuteScalar();
    }
    finally
    {
        connection.Dispose();
    }
}
catch (Exception)
{
}

The exception will be caught in the finally statement and the connection closed. The exception will not be seen by the outer catch clause.

Kerri Brown
  • 1,157
  • 1
  • 8
  • 18
  • 1
    very good examples man, but I have to disagree on your last comment, if an exception occurs within a using block it will be caught without problems on any outer catch, in fact I tested it by writing 2 using blocks inside a try/catch block, and to my surprise, I got my exception error message shown that came from the inner second using block. – WhySoSerious Feb 21 '14 at 05:56
1

I wrote two using statements inside a try/catch block and I could see the exception was being caught the same way if it's placed within the inner using statement just as ShaneLS example.

     try
     {
       using (var con = new SqlConnection(@"Data Source=..."))
       {
         var cad = "INSERT INTO table VALUES (@r1,@r2,@r3)";

         using (var insertCommand = new SqlCommand(cad, con))
         {
           insertCommand.Parameters.AddWithValue("@r1", atxt);
           insertCommand.Parameters.AddWithValue("@r2", btxt);
           insertCommand.Parameters.AddWithValue("@r3", ctxt);
           con.Open();
           insertCommand.ExecuteNonQuery();
         }
       }
     }
     catch (Exception ex)
     {
       MessageBox.Show("Error: " + ex.Message, "UsingTest", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }

No matter where's the try/catch placed, the exception will be caught without issues.

Community
  • 1
  • 1
WhySoSerious
  • 1,930
  • 18
  • 18
-1

Old thread but still relevant. I arrived here looking for a way out of having a using statement inside of a using statement. I am happy with this, notwithstanding any future insightful comments that change my mind. ;) Conversations here helped. Thanks. Simplified for readability -

public DataTable GetExchangeRates()
    {
        DataTable dt = new DataTable();

        try
        {
            logger.LogInformation($"Log a message.");

            string conStr = _config.GetConnectionString("conStr");

            using (SqlCommand cmd = new SqlCommand("someProc", new SqlConnection(conStr)))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection.Open();
                dt.Load(cmd.ExecuteReader());
            }

            return dt;

        }
        catch (Exception ex)
        {
            logger.LogError(ex, ex.Message);
        }
    }
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 22 '23 at 03:16