Dispose and close or close without dispose

9 Comments

• Here is the nuts-n-bolts of the issue. Calling .Close() on a SqlConnection will close that connection and put it back in the pool. There are still possible unmanaged resources underneath the covers. Calling .Dispose() on a SqlConnection is suppossed to call .Close(), clean up the unmanaged resources, and do a few other things. I do not like to have cleanup routines done auto-magically for me. As a result, I will always do my best to call .Close() and .Dispose() on a SqlConnection object (or any other database object that supports them). As a result, I have never, ever, never had any problem with doing database operations in any code that I have done. I had a customer and a friend of mine that did only called .Dispose() in the their code and did not call .Close(). As a result, both of them continually built up connections over time until the connection pool ran out of connections. This problem occurred under .NET 1.0 and 1.1 versions of the framework. Replacing their single call to .Dispose() to .Close() and .Dispose() resolved all problems. The situation was so bad with a customer that they were building up 1500 database connections in a matter of minutes. By changing this to call .Close() and .Dispose(), the number of connections dropped to 10 to 15 connections in the pool. I have performed the same type of testing in my office under a tremendous load (several million database operations) and am convinced that calling .Close() and .Dispose() is a better way to go than calling .Dispose() on its own.
  
  
  
  Wally
  
  

• Agree with you Wally, except that if you read Rory's post and comments, nobody seems to agree on the subject.
  
  Some said that you Dispose() call also Close().
  
  Other like you (and de facto me) said it's better to call Close() before Dispose().
  
  So my point is about the way that nobody seems to compromise easily on something fairly basic.
  

• Totally disagree with Wally; I just don't believe that changing .Dispose to .Close/.Dispose has that effect. Ergo, something else must have been changed.
  
  
  
  As a general rule, I always call Dispose() in a finally block (in C# usually implicitly as the result of a using statement).
  
  
  
  I.e. I might do any of the following:
  
  
  
  using (SqlConnection c = ...)
  
  {
  
  DoSomethingWithDB(c);
  
  c.Close();
  
  c.Open(...other connection string);
  
  c.Close();
  
  ... do something after last close
  
  }
  
  or:
  
  try
  
  {
  
  DoSomethingWithDB(c);
  
  c.Close();
  
  c.Open(...other connection string);
  
  c.Close();
  
  ... do something after last close
  
  }
  
  finally
  
  {
  
  c.Dispose();
  
  }
  
  
  
  The last Close() is redundant provided the code in "... do something after last close" is not time consuming and can be omitted.
  
  
  
  

• I agree with Joe,
  
  
  
  Something else must have been changed as well.
  
  
  
  We have a high traffic site that uses at this moment only 8 connections while 4000+ users are surfing our site.
  
  
  
  And guess what, we call .Dispose()
  
  (indirectly that is; we use using in our code)
  
  
  
  

• Joe and Ryan,
  
  
  
  yeah, I would think that something else had changed, but I spoke with both the customer and my friend at length regarding the issue. Both of them stated that the change from calling only .Dispose() to calling .Close() and .Dispose() was the only item that they changed. Perhaps the difference is in the fact that you using the "using" statement and I know that my customer was not.
  
  
  
  Wally
  

• Sorry Wally, but I've heard that mantra "all I changed was..." too often before. Your customer & friend are wrong: they should try to create a simple repro of the behaviour they think they're seeing and I bet it will all become clear to them...
  

• You see exactly what I was saying in this post ;-) Nobody agrees so the question is : what's going on ?
  

• I don't find it confusing.
  
  
  
  A good rule of thumb is: if you're in a "finally" block (explicitly or implicitly with the C# "using" statement) call Dispose, else call Close.
  
  
  
  If you develop server apps with large numbers of concurrent connections, you should always dispose objects that implement IDisposable. It's important to do so in the presence of an exception, e.e. you should call Dispose or Close in a "finally" block. Dispose (or the using statement) is more natural than Close as it's a consistent pattern for all disposable objects.
  
  
  
  In a rich client app, it's less important to release objects quickly (e.g. many Microsoft WinForms samples do not call Dispose on disposable GDI+ objects). In this case, you don't need to learn about the disposable pattern and it's more natural to use Close. Objects are likely to be garbage collected quickly enough for this not to be a problem.
  
  
  
  If you do both server and rich client apps, you'll probably get into the habit of always disposing, which is best practice and means your code can be used in both server and rich client apps. As an aside I think the developers of the "ASP.NET Reports" starter kit must come from a Winforms background as the GDI+ objects are not disposed despite this being server code.
  
  
  
  

• No hope -:)
  
  
  
  well and good. Everything seems fine.
  
  But finally the crips difference between them (.dispose and .close) is vague.
  
  
  
  TIA,
  
  Linga Reddy