'ExecuteReader需要打开且可用的连接。连接的当前状态为“打开” [英] 'ExecuteReader requires an open and available Connection. The connection's current state is open'

问题描述

一个用C＃编写的相当大的Web应用程序不断抛出2个错误：

A fairly large web application written in C# keeps throwing up 2 errors:

’ExecuteReader需要一个开放且可用的Connection。连接的当前状态为打开状态。'
和
'关闭阅读器后调用Read的尝试无效。'

'ExecuteReader requires an open and available Connection. The connection's current state is open.' and 'Invalid attempt to call Read when reader is closed.'

这些错误是偶发的-这些页面过去通常可以在95％的时间内正常加载，但是最近它们变得流行，它们一直在出现，并且基本上破坏了应用程序的功能。

These errors were sporadic -- the pages used to load fine about 95% of the time, but recently they've become endemic, they're occurring all the time and basically crippling the application's functionality.

该Web应用程序高度依赖于MS SQL数据库，而且错误似乎不仅限于一页，而是几乎所有连接到数据库的页面。

The web app is highly reliant on an MS SQL database, and the errors appear to not be confined to just one page, but nearly all the pages that connect to the database.

查询是这样执行的：

Database.Open(); // Custom class that has our connection string hard coded.

string query = "SELECT * FROM table"; // (dummy query)
SqlCommand command = new SqlCommand(query, Database.Conn);

SqlDataReader reader = null;

try {
    reader = command.ExecuteReader(CommandBehaviour.CloseConnection);

    if (reader.HasRows) {

        while (reader.Read()) {
            // Do something with the data.
        }
   }
    reader.Close();
}
catch (Exception e) {
    throw new Exception(e.Message);
}
finally {
    if (reader != null) {
        reader.Close();
    }
}

我已经在网络上研究了这些错误，已经看到了一些我没有用的潜在解决方案：

I've researched these errors on the web and I've seen a few potential solutions that I've tried to no avail:

将代码的各个部分放入using（）块中。
为阅读器指定CommandBehaviour.CloseConnection。
检查是否启用了MARS。
确保每次都创建一个新的连接对象。

Putting various parts of the code in a using() block. Specifying CommandBehaviour.CloseConnection for the reader. Checking that MARS is enabled. Ensuring that a new connection object is created each time.

我花了很长时间寻找解决方案，更不用说很长时间了使它起作用，现在我几乎要拔头发了！

I've spent ages searching for solutions to this, not to mention a long long time trying to make it work, and I'm almost on the verge of pulling my hair out now!

请帮助！

编辑-解决了该问题，请参见注释部分。

EDIT -- Fixed the problem, see the comments section.

推荐答案

除了leppie 的答案，您还应该 Dispose（） 类型：

In addition to leppie's answer, you should also be Dispose()ing of any IDisposable types:

        try
        {
            Database.Open(); // Custom class that has our connection string hard coded.

            string query = "SELECT * FROM table"; // (dummy query)

            using (SqlCommand command = new SqlCommand(query, Database.Conn))
            using (SqlDataReader reader = command.ExecuteReader(CommandBehaviour.CloseConnection))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        // Do something with the data.
                    }
                }
            }
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }

这篇关于'ExecuteReader需要打开且可用的连接。连接的当前状态为“打开”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！