Probable leak while using SqlDataReader in ADO.NET

I was doing a bit of exploration on Connections with ado.net.While doing this exercise i realized that when using CloseConnection along with the reader i was under the impression that it closes by itself but actually it wont close the connection.

So the tip is either create the sqlconnection object by using - using syntax (which ensures that it Disposes connection Object) or after the execution before leaving make a call to explicitly close the connection.

Share

About the Author

Motivated achiever who guides organizations in applying technology to business settings, provides added value, and creates project deliverables in a timely manner. An experienced Technical Consultant, have successfully led large project teams of more than 20 people from requirements gathering to implementation and support using C#, .NET ,ADO.NET, ADO.NET Entity Framework,ASP.NET,ASP.NET MVC, WCF and SQL Server.

I have a confusion in code sample attahced. You declare a SqlDataReader 'reader' using 'CommandBehavior.CloseConnection' and started using it within 'while' loop using 'Read()'. But for closing connection you first have to close SqlDataReader which is not mentioned / included in sample. Have you tried that or missed it?

Closing a DataReader will close your connection as well (this is what 'CommandBehavior.CloseConnection' does).

Correct me if I'm wrong somewhere but as per MSDN "CommandBehavior.CloseConnection" takes care of following task:

When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

Yes this also means that if you do not close DataReader then too your connection will be closed but this action will be taken by GarbageCollector and one can never guarantee when GC will deallocate things. So you cannot instantly predict whether your connection is automatically closed or not.

Thats the reason closing of DataReader is considered the best practice.

For ensuring connection is closed obviously as you mentioned 'using' construct is helpful but using 'finally' will also work.