SqlDataReader hangs on Dispose()

Question

I use the following approach to execute queries over database and read data:

using(SqlConnection connection = new SqlConnection("Connection string"))
{
    connection.Open();

    using(SqlCommand command = new SqlCommand("SELECT * FROM TableName", connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
              // read and process data somehow (possible source of exceptions)
        } // <- reader hangs here if exception occurs
    } 
}

While reading and processing data some exceptions can occur. The problem is when exception is thrown DataReader hangs on Close() call. Do you have any ideas why??? And how to solve this issue in a proper way? The problem has gone when I wrote try..catch..finally block instead of using and called command.Cancel() before disposing the reader in finally.

Working version:

    using(SqlConnection connection = new SqlConnection("Connection string"))
    {
        connection.Open();

        using(SqlCommand command = new SqlCommand("SELECT * FROM TableName", connection))
        {
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                // read and process data somehow (possible source of exceptions)
            }
            catch(Exception ex)
            {
                // handle exception somehow
            }
            finally
            {
               command.Cancel(); // !!!
               reader.Dispose();
            }
        } 
    }

Answer 1

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.