Is It Necessary to Manually Close and Dispose of SQLdatareader

Is it necessary to manually close and dispose of SqlDataReader?

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.

If SqlDataReader is going to be disposed, do I need to call close()?

If it's in a using block, then it is automatically closed. You do not need to explicitly close it.

BTW the SqlCommand in your example is disposable. You should create it in a using block too, otherwise any resources it controls won't be let go until the garbage collector collects.

Your undisposed SqlCommand is actually a good example of why C#'s emulation of RAII is not "real" RAII. You must take an explicit action (making blocks) for the RAII to kick in which is equivalent to an explicit close albeit with different syntax.

Is closing/disposing an SqlDataReader needed if you are already closing the SqlConnection?

In my opinion, there are two rules to follow here:

1. Classes that implement IDisposable should be wrapped in a using block.
2. You should not rely on a class's implementation of IDisposable to ignore rule 1.

That is, even if you know that disposing the connection object took care of disposing its associated command object, you should not rely on this behavior.

By the way, it's possible to nest using blocks in a cleaner fashion:

using (SqlConnection conn = new SqlConnection(conStr))
using (SqlCommand command = new SqlCommand())
{
    // dostuff
}

and I would use

SqlCommand command = conn.CreateCommand();

instead of creating a new SqlCommand and then associating it with the connection.

SqlDataReader hangs on Dispose()

When an exception occurs you stop processing data before all data is received. You can reproduce this issue even without exceptions if you abort processing after a few rows.

When the command or reader is disposed, the query is still running on the server. ADO.NET just reads all remaining rows and result sets like mad and throws them away. It does that because the server is sending them and the protocol requires receiving them.

Calling SqlCommand.Cancel sends an "attention" to SQL Server causing the query to truly abort. It is the same thing as pressing the cancel button in SSMS.

To summarize, this issue occurs whenever you stop processing rows although many more rows are inbound. Your workaround (calling SqlCommand.Cancel) is the correct solution.

correct point to close a data reader in vb.net

The best way is to use the Using-statement which ensures that unmanaged resources are disposed(even on error). This also closes the reader.

Using reader = myCommand.ExecuteReader()
   If reader.HasRows Then
      While reader.read

      End While
    End If 
End Using

Is it necessary to manually close and dispose of SqlDataReader?

Can I keep a SqlDataReader alive after closing connection?

You can't use the DataReader after the connection is closed, as it needs to use the connection to retrieve the data from the data source.

You need to read the data into a DataSet, or DataTable using the Load method, then you can close the connection.

Will putting a using statement around a DataReader close it?

Yes. using calls Dispose. Calling Dispose on SqlDataReader closes it.

This is psuedo-code of SqlDataReader gleaned from Reflector:

    public void Dispose()
    {
        this.Close();
    }

    public override void Close()
    {
        if( !IsClosed )
            CloseInternal(true);
    }

    private void CloseInternal(bool closeReader)
    {
        try
        {
            // Do some stuff to close the reader itself
        }
        catch(Exception ex)
        {
            this.Connection.Abort();
            throw;
        }

        if( this.Connection != null && CommandBehavior.CloseConnection == true )
        {
            this.Connection.Close();
        }
    }

Should I be using SqlDataReader inside a using statement?

The second option means your reader will be closed in the event of an exception after it has been created, so it is preferred.

It is effectively transformed by the compiler to:

SqlDataReader reader = command.ExecuteReader();
try
{
    ....
}
finally
{
  if (reader != null)
      ((IDisposable)reader).Dispose();
}

See MSDN for more info.

Why call SqlClient.SqlDataReader Close() method anyway?

Sure, it will be collected when it goes out of scope (if their are no other references to it). When it is collected, it will be closed through its Dispose() method. However, you never really know when the GC is going to deallocate things; if you don't close your readers, you very quickly run out of available connections to the database.

Further Reading

• O'Reilly's ADO.NET Connection Pool Explained
• Microsoft's Retrieving Data using a DataReader has a general overview of DataReaders.

~ William Riley-Land

What happens if you close a SqlConnection before the SqlDataReader?

It'll close the connection (returns it to the pool) and SqlDataReader would throw an exception (System.InvalidOperationException) in case it's used afterward.

---

Related Topics