Do I need to manually close and dispose of SqlDataReader?

Question

I work with legacy code here, and there are many instances of SqlDataReader that never close or get deleted. The connection is closed, but I'm not sure whether to manually control the reader.

Could this lead to a slowdown in performance?

Answer 1

Try to avoid using such readers:

 SqlConnection connection = new SqlConnection("connection string"); SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection); SqlDataReader reader = cmd.ExecuteReader(); connection.Open(); if (reader != null) { while (reader.Read()) { //do something } } reader.Close(); // <- too easy to forget reader.Dispose(); // <- too easy to forget connection.Close(); // <- too easy to forget 

Instead, collapse them using operators:

 using(SqlConnection connection = new SqlConnection("connection string")) { connection.Open(); using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection)) { using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader != null) { while (reader.Read()) { //do something } } } // reader closed and disposed up here } // command disposed here } //connection closed and disposed here 

The using statement ensures that the object is correctly deleted and resources are freed.

If you forget that you are moving away from cleaning to the garbage collector, this may take some time.

Answer 2

Note that deleting an instance of SqlDataReader using SqlCommand.ExecuteReader () will not close / delete the underlying connection.

There are two common patterns. Firstly, the reader opens and closes as part of the connection:

 using(SqlConnection connection = ...) { connection.Open(); ... using(SqlCommand command = ...) { using(SqlDataReader reader = command.ExecuteReader()) { ... do your stuff ... } // reader is closed/disposed here } // command is closed/disposed here } // connection is closed/disposed here 

Sometimes it’s convenient to have a way to access data, open a connection and return the reader. In this case, it is important that the returned reader is opened using CommandBehavior.CloseConnection, so closing / removing the reader will close the underlying connection. The template looks something like this:

 public SqlDataReader ExecuteReader(string commandText) { SqlConnection connection = new SqlConnection(...); try { connection.Open(); using(SqlCommand command = new SqlCommand(commandText, connection)) { return command.ExecuteReader(CommandBehavior.CloseConnection); } } catch { // Close connection before rethrowing connection.Close(); throw; } } 

and the calling code just needs to recycle the reader like this:

 using(SqlDataReader reader = ExecuteReader(...)) { ... do your stuff ... } // reader and connection are closed here. 

Answer 3

To be safe, wrap each SqlDataReader in with instructions .

Answer 4

Just wrap the SQLDataReader with the using statement. This should take care of most of your problems.