关闭 SQL 连接但打开的连接不断增加
我有以下方法：
public DataSet GetDataSet( string sp, params SqlParameter[] parameters ) {
DataSet ds = new DataSet();
using ( SqlConnection conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString
) ) {
using ( SqlCommand cmd = new SqlCommand() ) {
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sp;
if ( parameters != null ) {
foreach ( SqlParameter parm in parameters ) {
cmd.Parameters.Add( parm );
}
}
if ( conn.State == ConnectionState.Closed ) {
conn.Open();
}
using ( SqlDataAdapter da = new SqlDataAdapter( cmd ) ) {
da.Fill( ds );
}
}
}
return ds; }
我注意到多次调用此方法（大约 50 次）时会创建多个连接。 我通过在 SQL 中执行此查询来检查这一点：
SELECT DB_NAME(dbid) as 'DbNAme', COUNT(dbid) as 'Connections' from master.dbo.sysprocesses with (nolock) WHERE dbid > 0 GROUP BY dbid
调用上述方法时，连接数不断增加。难道它不应该一遍又一遍地使用相同的连接（连接池）而不是创建新的连接吗？
I have the following method:
public DataSet GetDataSet( string sp, params SqlParameter[] parameters ) {
DataSet ds = new DataSet();
using ( SqlConnection conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString
) ) {
using ( SqlCommand cmd = new SqlCommand() ) {
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sp;
if ( parameters != null ) {
foreach ( SqlParameter parm in parameters ) {
cmd.Parameters.Add( parm );
}
}
if ( conn.State == ConnectionState.Closed ) {
conn.Open();
}
using ( SqlDataAdapter da = new SqlDataAdapter( cmd ) ) {
da.Fill( ds );
}
}
}
return ds; }
I've noticed that multiple connections are created when calling this method multiple times (about 50 times).
I've checked this by executing this query in SQL:
SELECT DB_NAME(dbid) as 'DbNAme', COUNT(dbid) as 'Connections' from master.dbo.sysprocesses with (nolock) WHERE dbid > 0 GROUP BY dbid
The number of connections keeps incrementing when calling the above method. Shouldn't it use the same connection over and over again (connection pooling) instead of creating new ones?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
此链接解释了连接汇集得很好。如果你想了解整件事，你应该阅读这本书，它非常好。
This link explains connection pooling very well. If you want to understand the whole thing you should read this one it is very good.
尝试增加 web.config 中连接字符串的最大池大小，如下所示
或您定义的任何位置。它解决了问题，但暂时为了永久解决方案搜索您的代码，可能您没有关闭连接
希望它会帮助您
try to increase maximum pool size in you connection string in web.config like this
or wherever you defined it.it solves the problem but temporarily for permanent solution search your code probably you did not close the connection
hope it will help you
连接池并不意味着它将重用连接。由于建立 SQL 连接的成本很高，因此连接池保持固定的最大打开连接数，当您在连接上调用
.Close()时，它只是返回到池中，然后就可以当在新实例上调用Open()时，将其传递给新连接。该机制内置于 SqlConnection 类中，这就是它对用户透明发生的原因；简而言之：只要您正确
使用连接（就像您所做的那样），您就不必担心打开的连接数量。Connection pooling doesn't mean that it will reuse the connection. Since it is expensive to establish a SQL connection the connection pool keeps a fixed maximum of connections opens, and when you call
.Close()on the connection it is simply returned to the pool which then is able to pass it to a new connection whenOpen()is invoked on a new instance.This mechanism is built into the SqlConnection class which is why it happens transparently to the user; in short: You shouldn't worry about the number of open connections as long as you are
usingthe connections correctly (as you do).填充数据集后尝试关闭连接。使用语句释放对象但不会关闭连接。
Try to close connection after filling dataset. Using statment releases object but it doesnt close connection.