我用下面的方法来执行对数据库的查询和读取数据：

 使用（SqlConnection的连接=新的SqlConnection（连接字符串））
{
    connection.Open（）;

    使用（SqlCommand的命令=新的SqlCommand（SELECT * FROM表名，连接））
    {
        使用（SqlDataReader的读卡器= Command.ExecuteReader却（））
        {
              //读取和处理数据以某种方式（例外的可能的来源）
        } //＆LT;  - 读者在这里挂起如果出现异常，
    }
}
 

在读取和处理数据时会出现一些异常。问题是，当异常被抛出的DataReader 挂起关闭（）通话。你有什么想法，为什么？？？而如何以适当的方式解决这个问题？这个问题已经当我写 try..catch..finally代码块而不是使用键，名为 command.Cancel（）终于在之前。

工作版：

 使用（SqlConnection的连接=新的SqlConnection（连接字符串））
    {
        connection.Open（）;

        使用（SqlCommand的命令=新的SqlCommand（SELECT * FROM表名，连接））
        {
            SqlDataReader的读卡器= Command.ExecuteReader却（）;
            尝试
            {
                //读取和处理数据以某种方式（例外的可能的来源）
            }
            赶上（例外前）
            {
                //处理异常不知何故
            }
            最后
            {
               command.Cancel（）; //！
               reader.Dispose（）;
            }
        }
    }
 

解决方案

在发生异常时停止处理数据接收的所有数据之前。当然，前提是几行后中止的处理，即使没有例外重现此问题。

在命令或读取器配置，查询仍然在服务器上运行。 ADO.NET只是读取所有剩余的行和结果集疯狂，并引发他们离开。它确实是因为服务器发送他们的协议需要接收他们。

调用 SqlCommand.Cancel 发送一个注意到SQL Server导致查询真正终止。这是同样的事情，pressing在SSMS中取消按钮。

总之，当你停止处理行虽然很多更多的行内进出现此问题。你的解决方法（叫 SqlCommand.Cancel ）是正确的解决方案。

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();
            }
        } 
    }

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.