When to call dispose when using SqlConnection and SqlCommand?

When to call dispose when using SqlConnection and SqlCommand?

• oehp

  Bare with me here as I am asking these details specifically because I am going to implement this in a code generator which is currently being used by many applications running thousands of times per day; and I haven't introduced transactions yet..however I am factoring these questions with the shared connection in a transaction...
  
  
  Well, since you also suggest the nested using (despite it's "tediousness" in my example) I would have a failing situation as follows:
  
  

  using( SqlConnection conn = new SqlConnection() ) {     using ( SqlCommand cmd = conn.CreateCommand() )     {         //use cmd as normal        using (Obj1 obj1 = new Object1(conn))         {          //use obj1 as necessary         }                 cmd.Execute...();  //would fail since obj1 closed the shared connection in the dispose      } }

  
  
  would you still recommend nested using statements in this scenario and just be careful of the exectuion path...or would you suggest otherwise   Ideally, if C# had something like this I think the problem would be solved:
  
  

  using(     SqlConnection conn = new SqlConnection(),     SqlCommand cmd = conn.CreateCommand(),     Obj1 obj1 = new Object1(conn) ) {      //...do code using all the necessary objects     // dispose would be called in reverse order in the finally clause. }

  
  
  

• Brandano

  My 2 cents on the slightly off topic matter (though interesting anyhow for this thread):
  
  The connection pool is managed separately from your application (e.g. a connection pool factory perhaps)  infact you can controll the number of connections in the pool via the connection string itself.  The decompiled CLR code above indicates Dispose() simply calls Close(), so the connection should be closed and returned to the pool. At this point the pool's factory takes charge in making sure the pool count remains available for any future request (I think the default is 100 although off the top of my head, that number sounds pretty large).  This entire reply is based on recollection of reading material, etc. simply to provide some additional feedback on the matter.
  
  
  
  
  

• Glo

  I'm not sure this is true.  I don't see any evidence of this when looking at the Dispose method using Reflector.  Looks to me like it basically just calls the Close() method:
  
  

  protected override void Dispose(bool disposing) {  if (disposing)  {  switch (this._objectState)  {  case ConnectionState.Open:  {  this.Close();  break;  }  }  this._constr = null;  }  base.Dispose(disposing); }

  
  
  
  
  

• gleblanc

  Hi Mike,
  
  In your example, the connection should actually stay alive through the 2nd object's using block as long as you don't explicitly dispose of it inside of obj1, and as long as any command objects you use inside obj1 don't use a CommandBehavior.CloseConnection to automatically close the connection when done.
  
  Hope that helps!
  
  Robin
  
  

• Winterchill

  It may be a bit late, but the last section of this blog entry explains how to call the Dispose method, conveniently using SqlConnection and SqlCommand as examples: http://gregbeech.com/blogs/tech/archive/2007/03/07/implementing-and-using-the-idisposable-interface.aspx
  
  

• rpcservererrorguy

  you are correct that the following code snippets do virtually the same thing:
  

  using ( SomethingDisposable obj = new... ) { }

  
  and
  

  SomethingDisposable = new... try {   ..do something with SomethingDisposable } finally {   SomethingDisposable.Dispose(); }

  
  
  However, simply the fact that the object is disposable does not get you this behavior automatically.  You'd need to write the try/finally wrapper around your objects and call the Dispose explicitly to guarantee proper disposal.
  
  So, for your nested scenario, you'd need to write code like this:
  
  

  SqlConnection conn = null; SqlCommand cmd = null; DataLayerObj1 obj1 = null; DataLayerObj2 obj2 = null; try {   conn = new SqlConnection(...);   cmd = conn.CreateCommand();   obj1 = new DataLayerObj1(conn);   obj2 = new DataLayerObj2(conn);   ....   obj2.dispose();  ...   obj1.dispose(); ...   cmd.dispose(); ...   conn.dispose(); } finally {   if ( conn != null ){ conn.Dispose(); }   if ( cmd != null ){ cmd.Dispose(); }   if ( obj1 != null ){ obj1.Dispose(); }   if ( obj2 != null ){ obj2.Dispose(); } }

  
  
  This essentially will accomplish the same thing.  However, note that you need to manually close/dispose each when you are finished with it also (or you can do nested try/finally blocks) or the objects will live until it reaches the finally.
  
  My personal preference is to use the nested using blocks, since you don't have to worry about missing a dispose statement anywhere and you don't have to worry about the object lifetime as much.
  
  Anyway, good luck.
  
  

• Conchúr

  From what I understood, the finally clause contains the dispose()...so it will be called irregardless of an exception being thrown or not.
  
  Correct me if I am wrong, but I believe
  using( IDisposable obj = new ... ) { }
  
  is shorthand and is equivalent to:
  try
  {
     IDisposable obj = new ...;
  }
  catch
  {
  
  }
  finally
  {
      obj.Dispose();
  }
  
  and finally is called irregardless of exception being thrown or not; however I am not too comfortable with the double nested using statements.  This would mean I would have a nested using for each object that is disposable and is related to a connection, if I happen to have my own IDisposable datalayer objects, each of these would have to have their own using statements wrapping them.. as in:
  
  
  using ( SqlConnection conn = new SqlConnection(...) )
  {
      using ( SqlCommand cmd = conn.CreateCommand() )
      {
         using ( DataLayerObj1 obj1 = new DataLayerObj1( conn ) )
         {
            using ( DataLayerObj2 obj2 = new DataLayerObj2(conn) )
            {
                  //as you can see, this can get rather redundant when working with a
                  //datalayer objects that happen to be IDisposable
  
            }
         }
  
  
      }
  
  
  }
  
  Any additional feedback
  
  

• Renacer

  the Major Difference between close() and Dispose() on the sqlconnection is how each of these methods controls connection pooling .
  
  when calling : Sqlconnection1.Dispose();  // you are actually removing the connection object from the pool
  
  but when calling : Sqlconnection.Close(); //you are leaving the connection object in the pool for later use .
  
  of course putting the connection back in the pool after using it dramatically increase the performance ...
  
  
  Kind Regards ,
  Hussein Ahmad
  http://HusseindotNET.blogSpot.com
  

• Richard J.

  hi robincurry  ,
  
  it is very easy to see this visually by using the perf counter .NET CLR Data .
  
  try to call Dispose() method on a connection obj and it is going to be removed from the pool , but on the contrary if u invoked the close() method it is still in the pool . and BTW it is well documented .
  
  
  Regards ,
  Hussein Ahmad
  Http://HusseindotNET.BlogSpot.com
  

• suedueno

  Recommended practice is to use a using block, which will automatically manage the lifetime of your connection and command and close/dispose when it goes out of scope.  So it would look something like this:
  
  

  using ( SqlConnection conn = new SqlConnection("xxxx") ) {   using ( SqlCommand cmd = conn.CreateCommand() )   {     // ...do the necessary processing.   } }

  
  
  This will also ensure that the connection/command are properly disposed if an exception occurs.  To guarantee this otherwise, you'd need to wrap your code in a try/catch/finally block.
  
  

• k_jedsada

  Hi Mike,
  
  C# actually does have a very similar construct to what you're saying.  If all objects are of the same type, you can use commas.  However, if they're of different types, you can do something along these lines:
  
  

  using(SqlConnection conn = new SqlConnection())using(SqlCommand cmd = conn.CreateCommand())using(Obj1 obj1 = new Object1(conn) {   YourCodeHere();}

  
  
  Of coures Obj1 will have to implement IDisposable for that to work.
  
  -Shawn
  
  

• Pabs

  Maybe someone else can give a more definitive answer on this, but this is what I found from a google search.
  
  It would appear that, perhaps, the original documentation made this claim, but may have been found to be incorrect.
  
  Also see the following:
  http://ryanfarley.com/blog/archive/2004/03/17/444.aspx
  http://www.yoda.arachsys.com/csharp/faq/#sqlconnection.dispose
  http://www.dotnetmonster.com/Uwe/Forum.aspx/dotnet-ado-net/8729/Should-Dispose-be-invoked
  http://groups-beta.google.com/group/microsoft.public.dotnet.framework.aspnet/msg/72d78f28c9e218df rnum=1
  
  
  
  

• Mark Snape

  Mike,
  
  Dispose actually calls Close so in a sense it is redundant.  However, at the same time it will not hurt to close the connection/command prior to disposing.  Personally I use a using clause with a close at the end of the block. 
  
  They key here is that my technique will always close the connection.  whenever the scope is broken from the using clause dispose will be called on the object.  With your technique an exception could cause the connection to remain open.
  
  So basically I do...
  

  class="txt4">using (SqlConnection conn = new SqlConnection("xxxx")) {  //...do stuff   conn.Close(); }

  
  
  
  

• Gerhard Zehetbauer

  I dont think finally automaticlaly calls Dispose() on the connection object. we need to explicitly call Dispose() for the connection to be removed form the Pool.

  
  
  
• When to call dispose when using SqlConnection and SqlCommand?