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

问题描述

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

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

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

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

The site works fine on my localhost server.

这是粗略的代码.

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?

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

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;

推荐答案

抱歉我只在第一时间发表评论，但我几乎每天都会发布类似的评论，因为很多人认为封装 ADO 是明智之举.NET 功能转换为 DB 类(我也是 10 年前).大多数情况下，他们决定使用静态/共享对象，因为这似乎比为任何操作创建新对象更快.

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.

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

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

ADO.NET 在ADO-NET 连接池:

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

连接池减少了新连接的次数必须打开.池化者保持物理的所有权联系.它通过保持一组活动的活动来管理连接每个给定连接配置的连接.每当用户在连接上调用 Open，池化器寻找可用的池中的连接.如果池连接可用，它将它返回给调用者而不是打开一个新的连接.当...的时候应用程序在连接上调用 Close，池化器将其返回给池中的活动连接集而不是关闭它.一旦连接返回到池中，它准备好在下一个公开电话.

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.

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

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.

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

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.

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

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).

结论:

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

这不仅适用于 Connections(尽管最引人注目).每个实现 IDisposable 的对象都应该是处置(最简单的using-statement)，在 System.Data.SqlClient 命名空间中更是如此.

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

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

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.

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

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

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

这篇关于ExecuteReader 需要一个打开且可用的连接.连接的当前状态是 Connecting的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！