关闭SQL连接，但打开的连接不断递增 [英] Closing SQL-connection but open connections keeps incrementing

问题描述

我有以下方法：

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执行这个查询检查这样的：

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?

推荐答案

这链接解释了连接池非常好。如果您想了解整个事情，你应该阅读这一块是非常不错的。

This link explains connection pooling very well. If you want to understand the whole thing you should read this one it is very good.

连接池减少数量   次新的连接必须   打开。该池进程维护所有权   物理连接。它管理   通过保持活了一组连接   对于每个给定的活动连接   连接配置。每当   用户调用打开一个连接上时，   普勒寻找可用   在池连接。如果一个汇集   连接可用时，它返回其   给调用者，而不是打开一个新的   连接。当应用程序调用   关闭连接上，池   它返回到合并集的活性   连接，而不是将其关闭。   一旦连接被​​返回到   池，它准备在被重用   接下来打开电话。

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.

这篇关于关闭SQL连接，但打开的连接不断递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！