C# ExecuteReader 需要一个打开且可用的连接。连接的当前状态是 Connecting

ExecuteReader requires an open and available Connection. The connection's current state is Connecting

提问by Guo Hong Lim

When attempting to connect to MSSQL database via ASP.NET online, I will get the following when two or more people connect simultaneously:

当尝试通过 ASP.NET 在线连接到 MSSQL 数据库时，当两个或更多人同时连接时，我会得到以下信息：

ExecuteReader requires an open and available Connection. The connection's current state is Connecting.

ExecuteReader 需要一个打开且可用的连接。连接的当前状态是 Connecting。

The site works fine on my localhost server.

该站点在我的本地主机服务器上运行良好。

This is the rough code.

这是粗略的代码。

public Promotion retrievePromotion()
{
    int promotionID = 0;
    string promotionTitle = "";
    string promotionUrl = "";
    Promotion promotion = null;
    SqlOpenConnection();
    SqlCommand sql = SqlCommandConnection();

    sql.CommandText = "SELECT TOP 1 PromotionID, PromotionTitle, PromotionURL FROM Promotion";

    SqlDataReader dr = sql.ExecuteReader();
    while (dr.Read())
    {
        promotionID = DB2int(dr["PromotionID"]);
        promotionTitle = DB2string(dr["PromotionTitle"]);
        promotionUrl = DB2string(dr["PromotionURL"]);
        promotion = new Promotion(promotionID, promotionTitle, promotionUrl);
    }
    dr.Dispose();
    sql.Dispose();
    CloseConnection();
    return promotion;
}

May I know what might have gone wrong and how do I fix it?

我可以知道哪里出了问题，我该如何解决？

Edit: Not to forget, my connection string and connection are both in static. I believe this is the reason. Please advise.

编辑：不要忘记，我的连接字符串和连接都是静态的。我相信这就是原因。请指教。

public static string conString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
public static SqlConnection conn = null;

采纳答案by Tim Schmelter

Sorry for only commenting in the first place, but i'm posting almost every day a similar comment since many people think that it would be smart to encapsulate ADO.NET functionality into a DB-Class(me too 10 years ago). Mostly they decide to use static/shared objects since it seems to be faster than to create a new object for any action.

抱歉，我一开始只发表评论，但我几乎每天都会发表类似的评论，因为很多人认为将 ADO.NET 功能封装到 DB-Class 中是明智的（10 年前我也是如此）。大多数情况下，他们决定使用静态/共享对象，因为它似乎比为任何操作创建新对象更快。

That is neither a good idea in terms of peformance nor in terms of fail-safety.

这在性能和故障安全方面都不是一个好主意。

Don't poach on the Connection-Pool's territory

不要在连接池的领土上偷猎

There's a good reason why ADO.NET internally manages the underlying Connections to the DBMS in the ADO-NET Connection-Pool:

ADO.NET 在内部管理ADO-NET Connection-Pool 中与 DBMS 的底层连接是有充分理由的：

In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

实际上，大多数应用程序仅使用一种或几种不同的连接配置。这意味着在应用程序执行期间，将重复打开和关闭许多相同的连接。为了最小化打开连接的成本，ADO.NET 使用了一种称为连接池的优化技术。

连接池减少了必须打开新连接的次数。池化器维护物理连接的所有权。它通过为每个给定的连接配置保持一组活动连接来管理连接。每当用户在连接上调用 Open 时，池化器就会在池中寻找可用的连接。如果池连接可用，它会将其返回给调用者，而不是打开一个新连接。当应用程序对连接调用 Close 时，池化器将它返回到活动连接的池中，而不是关闭它。一旦连接返回到池中，它就可以在下一次 Open 调用中重用。

So obviously there's no reason to avoid creating,opening or closing connections since actually they aren't created,opened and closed at all. This is "only" a flag for the connection pool to know when a connection can be reused or not. But it's a very important flag, because if a connection is "in use"(the connection pool assumes), a new physical connection must be openend to the DBMS what is very expensive.

所以显然没有理由避免创建、打开或关闭连接，因为实际上它们根本没有被创建、打开和关闭。这是连接池的“唯一”标志，用于了解何时可以重用连接。但这是一个非常重要的标志，因为如果连接“正在使用”（连接池假设），则必须向 DBMS 开放新的物理连接，这是非常昂贵的。

So you're gaining no performance improvement but the opposite. If the maximum pool size specified (100 is the default) is reached, you would even get exceptions(too many open connections ...). So this will not only impact the performance tremendously but also be a source for nasty errors and (without using Transactions) a data-dumping-area.

因此，您没有获得任何性能改进，而是相反。如果达到指定的最大池大小（默认值为 100），您甚至会收到异常（打开的连接太多...）。因此，这不仅会极大地影响性能，而且还会成为令人讨厌的错误和（不使用事务）数据转储区域的来源。

If you're even using static connections you're creating a lock for every thread trying to access this object. ASP.NET is a multithreading environment by nature. So theres a great chance for these locks which causes performance issues at best. Actually sooner or later you'll get many different exceptions(like your ExecuteReader requires an open and available Connection).

如果您甚至在使用静态连接，您就会为每个试图访问此对象的线程创建一个锁。ASP.NET 本质上是一个多线程环境。所以这些锁很有可能会导致性能问题。实际上迟早你会得到许多不同的异常（比如你的ExecuteReader 需要一个开放和可用的 Connection）。

Conclusion:

结论：

• Don't reuse connections or any ADO.NET objects at all.
• Don't make them static/shared(in VB.NET)
• Always create, open(in case of Connections), use, close and dispose them where you need them(f.e. in a method)
• use the using-statementto dispose and close(in case of Connections) implicitely

• 根本不要重用连接或任何 ADO.NET 对象。
• 不要让它们静态/共享（在 VB.NET 中）
• 始终在需要的地方创建、打开（在连接的情况下）、使用、关闭和处置它们（fe 在方法中）
• 使用using-statement隐式地处理和关闭（在连接的情况下）

That's true not only for Connections(although most noticable). Every object implementing IDisposableshould be disposed(simplest by using-statement), all the more in the System.Data.SqlClientnamespace.

这不仅适用于 Connections（尽管最引人注目）。每个实现的对象IDisposable都应该被处理（最简单的是using-statement），尤其是在System.Data.SqlClient命名空间中。

All the above speaks against a custom DB-Class which encapsulates and reuse all objects. That's the reason why i commented to trash it. That's only a problem source.

以上所有内容都反对封装和重用所有对象的自定义 DB-Class。这就是为什么我评论垃圾它的原因。那只是一个问题的来源。

---

---

Edit: Here's a possible implementation of your retrievePromotion-method:

编辑：这是您的retrievePromotion方法的可能实现：

public Promotion retrievePromotion(int promotionID)
{
    Promotion promo = null;
    var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        var queryString = "SELECT PromotionID, PromotionTitle, PromotionURL FROM Promotion WHERE PromotionID=@PromotionID";
        using (var da = new SqlDataAdapter(queryString, connection))
        {
            // you could also use a SqlDataReader instead
            // note that a DataTable does not need to be disposed since it does not implement IDisposable
            var tblPromotion = new DataTable();
            // avoid SQL-Injection
            da.SelectCommand.Parameters.Add("@PromotionID", SqlDbType.Int);
            da.SelectCommand.Parameters["@PromotionID"].Value = promotionID;
            try
            {
                connection.Open(); // not necessarily needed in this case because DataAdapter.Fill does it otherwise 
                da.Fill(tblPromotion);
                if (tblPromotion.Rows.Count != 0)
                {
                    var promoRow = tblPromotion.Rows[0];
                    promo = new Promotion()
                    {
                        promotionID    = promotionID,
                        promotionTitle = promoRow.Field<String>("PromotionTitle"),
                        promotionUrl   = promoRow.Field<String>("PromotionURL")
                    };
                }
            }
            catch (Exception ex)
            {
                // log this exception or throw it up the StackTrace
                // we do not need a finally-block to close the connection since it will be closed implicitely in an using-statement
                throw;
            }
        }
    }
    return promo;
}

回答by Damon Abdiel

I caught this error a few days ago.

几天前我发现了这个错误。

IN my case it was because I was using a Transaction on a Singleton.

就我而言，这是因为我在单例上使用事务。

.Net does not work well with Singleton as stated above.

如上所述，.Net 不适用于 Singleton。

My solution was this:

我的解决方案是这样的：

public class DbHelper : DbHelperCore
{
    public DbHelper()
    {
        Connection = null;
        Transaction = null;
    }

    public static DbHelper instance
    {
        get
        {
            if (HttpContext.Current is null)
                return new DbHelper();
            else if (HttpContext.Current.Items["dbh"] == null)
                HttpContext.Current.Items["dbh"] = new DbHelper();

            return (DbHelper)HttpContext.Current.Items["dbh"];
        }
    }

    public override void BeginTransaction()
    {
        Connection = new SqlConnection(Entity.Connection.getCon);
        if (Connection.State == System.Data.ConnectionState.Closed)
            Connection.Open();
        Transaction = Connection.BeginTransaction();
    }
}

I used HttpContext.Current.Items for my instance. This class DbHelper and DbHelperCore is my own class

我为我的实例使用了 HttpContext.Current.Items。这个类 DbHelper 和 DbHelperCore 是我自己的类