SqlDataReader: In this scenario, will the reader get closed?

Question

I am cleaning up the DataReaders in an old .NET 1.1 project that I inherited.

The previous developer coded the data-access-layer in such a way that most of the DAL methods returned SqlDataReaders (thus leaving it up to the caller to properly call the .Close() or .Dispose() methods).

I have come across a situation, though, where a caller is not catching the returned SqlDataReader (and therefore is not disposing of it properly). See the code below:

Data Access Method:

Public Shared Function UpdateData() As SqlDataReader
   ...

   drSQL = cmdSQL.ExecuteReader(CommandBehavior.CloseConnection)

   Return drSQL

End Function

Calling code:

...
DataAccessLayer.UpdateData()
...

As you can see, the calling method does not receive/catch the returned SqlDataReader. So what happens? Is that SqlDataReader still out there and open? Or does it automatically get garbage collected since nothing is addressing it?

I couldn't think of a way to debug and test this. If anybody has any ideas or suggestions that would be great.

Supersensitive

Answer 1

i believe that it will get closed but not until the garbage-collector gets 'round to it, which may not be for a very long time...

Firewood

I think you're correct on this one. Since, we have added the Command Behavior to close the connection when the SqlDataReader object gets closed/disposed, the Garbage Collection should take care of everything (whenever it happens). – Supersensitive

@[Jeffaxe]: i would not leave this to the mercy of the GC. If this kind of operation happens frequently you may consume the connection pool and other resources. Plus (reading the method literally) if only an update is being done you don't need a datareader in the first place. – Firewood