Is it necessary to manually close and dispose of SqlDataReader?

C#Sql.Netado.net

C# Problem Overview


I'm working with legacy code here and there are many instances of SqlDataReader that are never closed or disposed. The connection is closed but, I am not sure if it is necessary to manage the reader manually.

Could this cause a slowdown in performance?

C# Solutions


Solution 1 - C#

Try to avoid using readers like this:

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, wrap them in using statements:

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 will ensure correct disposal of the object and freeing of resources.

If you forget then you are leaving the cleaning up to the garbage collector, which could take a while.

Solution 2 - C#

Note that disposing a SqlDataReader instantiated using SqlCommand.ExecuteReader() will not close/dispose the underlying connection.

There are two common patterns. In the first, the reader is opened and closed within the scope 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 data access method open a connection and return a reader. In this case it's important that the returned reader is opened using CommandBehavior.CloseConnection, so that closing/disposing the reader will close the underlying connection. The pattern 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 dispose the reader thus:

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

Solution 3 - C#

To be safe, wrap every SqlDataReader object in a using statement.

Solution 4 - C#

Just wrap your SQLDataReader with "using" statement. That should take care of most of your issues.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJon OwnbeyView Question on Stackoverflow
Solution 1 - C#CodebrainView Answer on Stackoverflow
Solution 2 - C#JoeView Answer on Stackoverflow
Solution 3 - C#KonView Answer on Stackoverflow
Solution 4 - C#J.W.View Answer on Stackoverflow