当试图连接到通过ASP.NET MSSQL数据库在线，我会得到以下两个或两个以上的人同时连接：
ExecuteReader需要一个开放和可用的连接。连接的当前状态为连接。
该网站工作正常我本地主机服务器上。
这是粗糙code。
公众推广retrievePromotion（）
{
INT promotionID = 0;
字符串promotionTitle =;
字符串promotionUrl =;
促销促销= NULL;
SqlOpenConnection（）;
SqlCommand的SQL = SqlCommandConnection（）;
sql.CommandText =SELECT TOP 1 PromotionID，PromotionTitle，PromotionURL从促进;
SqlDataReader的博士= sql.ExecuteReader（）;
而（dr.Read（））
{
promotionID = DB2int（DR [PromotionID]）;
promotionTitle = DB2string（DR [PromotionTitle]）;
promotionUrl = DB2string（DR [PromotionURL]）;
推广=新的促销活动（promotionID，promotionTitle，promotionUrl）;
}
dr.Dispose（）;
sql.Dispose（）;
CloseConnection（）;
返回推广;
}
我想知道什么可能出现了问题，如何解决呢？
编辑：不要忘记，我的连接字符串和连接都处于静态。我相信，这就是原因。请指教。
公共静态字符串conString = ConfigurationManager.ConnectionStrings [的DbConnection]的ConnectionString。
公共静态的SqlConnection康恩= NULL;
解决方案
对不起，只是评论摆在首位，但我张贴几乎每天都有类似的评论，因为很多人都认为这将是明智封装ADO。 NET功能集成到一个DB-类（我也是10年前）。主要是他们决定使用静态/共享的对象，因为它似乎快于创造任何行动的新对象。
这是效果进行方面还是在故障安全性方面都不是一个好主意。
不要在连接池的境内挖走
有一个很好的理由ADO.NET在内部管理中的 ADO-NET的DBMS的基本连接连接池：
在实践中，大多数应用中仅使用一个或几个不同的 配置进行连接。这意味着，应用过程中 执行，许多相同的连接将被反复打开和 关闭。为了尽量减少打开连接的成本，ADO.NET采用 所谓的连接池优化技术。
连接池减少了倍新连接数 必须打开。该池进程保持对物理的所有权 连接。它通过保持活了一套主动管理连接 对于每个给定连接配置的连接。每当用户 调用打开一个连接上，池查找可用 在池连接。如果一个池连接可用， 它返回给调用者，而不是打开一个新的连接。当。。。的时候 应用程序调用关闭的连接上，池返回给 汇集一组活动连接，而不是关闭它。一旦 连接返回到池中，它准备在被重用 接下来打开电话。
所以，很显然，我们没有理由以避免因为实际上他们没有创建，打开创建，打开或关闭连接和关闭的。这是唯一的一个标志连接池知道什么时候一个连接可以被重复使用或没有。但是，这是一个非常重要的标志，因为如果一个连接使用（连接池假设），一个新的物理连接必须双头呆到DBMS的是非常昂贵的。
所以，你没有获得性能的提升却截然相反。如果指定了最大池大小（100是默认值）达到，你甚至可以例外（太多打开的连接...）。因此，这不仅会影响性能极大，而且是严重错误的来源，（不使用交易）数据倾销领域。
如果你甚至可以使用静态连接你为每一个线程试图访问该对象的锁。 ASP.NET本质上是多线程的环境。因此，孤单的这些锁导致的性能问题，最好的一个很好的机会。其实迟早你会得到很多不同的异常（如您的 ExecuteReader需要开放和可用的连接的）。
结论
请不要重复使用连接或ADO.NET对象都没有。 请不要让他们的静态/共享的（在VB.NET） 始终创建，打开（如果连接的），使用，关闭和处置，你需要他们（FE的方法） 使用使用语句 处置和关闭（如果连接的）隐含
这是真的不仅是连接（虽然大部分noticable）。每个对象实施 的IDisposable 应处置（简单的由 使用语句 ），更加的 System.Data.SqlClient的命名空间。
以上所有的讲对一个自定义的DB-类，它封装和重复使用的所有对象。这就是为什么我评论垃圾桶它的原因。这只是一个问题的根源。
修改：这是一个可能实现你的 retrievePromotion -method：
公众推广retrievePromotion（INT promotionID）
{
推广促销= NULL;
VAR的connectionString = System.Configuration.ConfigurationManager.ConnectionStrings [MainConnStr]的ConnectionString。
使用（SqlConnection的连接=新的SqlConnection（的connectionString））
{
VAR查询字符串=SELECT PromotionID，PromotionTitle，PromotionURL从促进WHERE PromotionID = @ PromotionID;
使用（VAR DA =新的SqlDataAdapter（查询字符串，连接））
{
//你也可以使用一个SqlDataReader代替
//注意，一个数据表不需要被布置，因为它不实现IDisposable
VAR tblPromotion =新的DataTable（）;
//避免SQL注入
da.SelectCommand.Parameters.Add（@ PromotionID，SqlDbType.Int）;
。da.SelectCommand.Parameters [@ PromotionID]值= promotionID;
尝试
{
connection.Open（）;
da.Fill（tblPromotion）;
如果（tblPromotion.Rows.Count！= 0）
{
变种promoRow = tblPromotion.Rows [0];
促销=新的促销活动（）
{
promotionID = promotionID，
promotionTitle = promoRow.Field＆LT;字符串＆GT;（PromotionTitle），
promotionUrl = promoRow.Field＆LT;字符串＆GT;（PromotionURL）
};
}
}
赶上（例外前）
{
//登录此异常或扔它的堆栈跟踪
//我们并不需要一个最后块关闭连接，因为它会被隐含封闭在一个用语句
扔;
}
}
}
返回宣传片;
}
When attempting to connect to MSSQL database via ASP.NET online, I will get the following when two or more people connect simultaneously:
ExecuteReader requires an open and available Connection. The connection's current state is 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;
解决方案
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.
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:
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.
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.
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.
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).
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 theusing-statement to dispose and close(in case of Connections) implicitely
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.
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.
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();
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;
}
相关推荐
最新文章