using on SQLDataReader

Question

Hi guys,

I know I asked a related question earlier. I just had another thought.

using (SqlConnection conn = new SqlConnection('blah blah'))
{
    using(SqlCommand cmd = new SqlCommand(sqlStatement, conn))
    {
       conn.open();
       SqlDataReader dr = cmd.ExecuteReader() //do I need to put this in using as well?
       {
            While(dr.Read())
            {
               //read here
            }
       }
    }
}

The argument is that: Since the SqlDataReader 'dr' object is NOT A NEW OBJECT LIKE THE connection or command objects, its simply a reference pointing to the cmd.ExecuteReader() method, do I need to put the reader inside a Using. (Now based on my previous post, it is my understanding that any object that uses IDisposable needs to be put in using, and SQLDataReader inherits from IDisposable, so I need to put it. Am I correct in my judgement?) I am just confused since its not a new object, would it cause any problems in disposing an object that simply is a reference pointer to the command?

Many thanks

Answer 1

You should wrap the data reader in a using statement as the ExecuteReader method is creating a new data reader instance that should also be disposed of.

Answer 2

I think you are mistaken. The dr is a reference to the object returned by cmd.ExecuteReader, which is going to be a new object. In your example, nothing will dispose dr, so yes it needs to be in a using, or manually disposed.

Your judgement about IDisposable implementors needing to be in a using is not correct. They will function fine outside. A using statement is just syntactic sugar for a try ... finally. Things implementing IDisposable should have Dispose called, because they are signalling that they need to dispose certain state in a deterministic way.

Note that if you do not call Dispose, its not always a problem. Some objects also implement the finalizer, which will be triggered by the garbage collector. If they do not implement the finalizer, they might leave unmanaged memory unreclaimed. This will remain unreclaimed until your application closes. All managed memory is eventually reclaimed, unless it is not elligible for garbage collection.

Re-written:

using (SqlConnection conn = new SqlConnection('blah blah')) 
using(SqlCommand cmd = new SqlCommand(sqlStatement, conn)) 
{
   conn.open(); 
   using (SqlDataReader dr = cmd.ExecuteReader())
   { 
        while (dr.Read()) 
        { 
           //read here 
        } 
   } 
}