14

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();

        // *** do I need to put this in using as well? ***
        SqlDataReader dr = cmd.ExecuteReader() 
        {
            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 a 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

davmos
  • 9,324
  • 4
  • 40
  • 43
xeshu
  • 788
  • 2
  • 11
  • 24

2 Answers2

30

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 
        } 
   } 
}
Adam Houldsworth
  • 63,413
  • 11
  • 150
  • 187
  • yes i understand that the using simply translates into a try..finally block and I could just as well call the dispose my self. But I think its better to make a habit of wrapping the block inside the using as I could tend to forget putting the chunk of code in try..finnally. Thanks for the reply. :) – xeshu Aug 02 '10 at 10:22
  • That's OK :-) I'll mention it as well that the SqlDataReader is a new object, or is at least a reference to an object returned by the method call, it is not a reference to the method. Just mentioning because you made that point in capital letters and its not correct. The only difference between the reader and the connection/command is that you initialized the connection/command, but you did not initialize the reader. At the end you still have references to all objects - whether you initialized them or not. – Adam Houldsworth Aug 02 '10 at 10:25
  • Maybe this goes without saying, but `conn.Close();` is needed after the `using(SqlDataReader dr=cmd.ExecuteReader()) { ... }` section, correct? – Trevor Nestman Aug 24 '16 at 17:49
  • @TrevorNestman Close is called on dispose, but you can call it explicitly too if you wish. – Adam Houldsworth Aug 24 '16 at 20:10
  • @AdamHouldsworth Thanks for the response/info! I'm pretty new to SqlConnection, so I'm trying to use it properly. – Trevor Nestman Aug 24 '16 at 20:13
2

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.

Michael Arnell
  • 1,008
  • 1
  • 9
  • 16