in a using block is a SqlConnection closed on return or exception

Question

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

User · Answer

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

User · Answer

Yes 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[c#] in a "using" block is a SqlConnection closed on return or exception?

Examples related to c#

Examples related to using

Examples related to sqlconnection