Unmanaged Resources, IDisposable and Custom Types

Question

yet another topic on the subject as I got tired of reading countless topics to find an answer to my questions :)

Lets say we have the following class:

public class MyClass
{
   private const string conString = "connection string";

   private int Operation()
   {
      int count = 0;

      using(var con = SqlConnection(conString))
      {
         string select_cmd = "SELECT * FROM TABLE";

         using(var cmd = new SqlCommand(select_cmd, con))
         {
            using(var reader = cmd.ExecuteReader())
            {
               while(reader != null && reader.Read())
                  count++;
            }
         }
      }

      return count;
   }

}

Since the connection to the database is instantiated inside a using statement thus the con.close() and eventually con.dispose() methods will be called, is there a need to implement IDisposable for MyClass? Will MyClass get garbage-collected when it goes out of scope?

EDIT:

Thank you for your replies, thats what I thought but I needed to make it clear. One more question.

If my class has several Operations() that do some work on a database, is it a better practice from a resources consumption point of view to have a SqlConnection member, instanciate and open it on the class constructor and implement IDisposable to close it instead of using "using" statements in each operation (opening and closing the database on each operation) ? Ofcourse that way I should only instantiate and use the MyClass object in using statements.

Answer 1

No, you only need to implement IDisposable if your class is holding onto the SqlConnection instance outside of the Operation() method, such that it is being kept alive with the class itself (for example if you were assiging it to a class member field or property).

The SqlConnection instance will be marked as eligible for cleanup before your class goes out of scope since that instance went out of scope in the using block. The most important thing is that the unmanaged database connection (encapsulated by the SqlConnection) has been freed with the Dispose call. The managed part of SqlConnection will be freed when the GC feels enough pressure to justify doing a GC pass.

As for your class instance, it will be freed at the GC's discretion as well, and that is something you don't need to be concerned about unless you were creating and destroying large numbers of instances of your class across the lifetime of your app (millions I suppose).

EDIT

For your second question, it is better to use and dispose SqlConnection instances per method because this particular database client class internally implements connection pooling (ie: It doesn't really close the connection when you Dispose it for a while, so that when a new SqlConnection is created it reuses one from the pool of open connections).

See (old but valid): http://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.71%29.aspx

If you use a database client API that doesn't implement its own pooling, it maybe better to manage this manually as you suggest. However you must be careful with synchronizing access to the connection (ie: don't let 2 threads use it at the same time) and manage lifetime issues yourself (eg: if your class remains alive all of the duration of the app, you'll hold a database resource open indefinitely...etc.)

Most of the modern ones (MySql Connector.NET, SQL Server) implement pooling.