c# - 为什么我的应用程序在尝试关闭 SqlConnection 对象时挂起？

Question

我正在尝试从 SQL Server 上的 SQL 表中获取 C# 中的列信息。我正在关注此链接中的示例:http://support.microsoft.com/kb/310107我的程序在尝试关闭连接时奇怪地挂断了。如果连接未关闭，程序将退出而不会出现任何异常。这是我的代码:

SqlConnection connection = new SqlConnection(@"MyConnectionString"); 
connection.Open();
SqlCommand command = new SqlCommand("SELECT * FROM MyTable", connection);
SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo); // If this is changed to CommandBehavior.SchemaOnly, the program runs fast.
DataTable table = reader.GetSchemaTable();
Console.WriteLine(table.Rows.Count);
connection.Close(); // Alternatively If this line is commented out, the program runs fast.

将 SqlConnection在 using block 内也会导致应用程序挂起，除非 CommandBehavior.KeyInfo更改为 CommandBehavior.SchemaOnly .

using (SqlConnection connection = new SqlConnection(@"MyConnectionString"))
{
    connection.Open();
    SqlCommand command = new SqlCommand("SELECT * FROM MyTable", connection);
    SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo); // If this is changed to CommandBehavior.SchemaOnly, the program runs fast even here in the using
    DataTable table = reader.GetSchemaTable();
    Console.WriteLine(table.Rows.Count);
}

有问题的表有超过 300 万行，但由于我只获取模式信息，所以我认为这不是问题。我的问题是:为什么我的应用程序在尝试关闭连接时卡住了？

解决方案:也许这不是最优的，但它确实有效；我插入了一个 command.Cancel(); Close 之前的声明在连接时调用:

SqlConnection connection = new SqlConnection(@"MyConnectionString"); 
connection.Open();
SqlCommand command = new SqlCommand("SELECT * FROM MyTable", connection);
SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo); // If this is changed to CommandBehavior.SchemaOnly, the program runs fast.
DataTable table = reader.GetSchemaTable();
Console.WriteLine(table.Rows.Count);
command.Cancel(); // <-- This is it.
connection.Close(); // Alternatively If this line is commented out, the program runs fast.

Answer 1

很久以前我见过这样的东西。对我来说，这是因为我做了类似的事情:

SqlCommand command = new SqlCommand("SELECT * FROM MyTable", connection);
SqlDataReader reader = command.ExecuteReader();

// here, I started looping, reading one record at a time
// and after reading, say, 100 records, I'd break out of the loop

connection.Close();  // this would hang

问题是该命令似乎想要完成。也就是说，遍历整个结果集。我的结果集有数百万条记录。它会结束……最终。

我通过在调用 connection.Close() 之前添加对 command.Cancel() 的调用解决了这个问题。

参见 http://www.informit.com/guides/content.aspx?g=dotnet&seqNum=610获取更多信息。