Closing SQL-connection but open connections keeps incrementing

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?

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.

try to increase maximum pool size in you connection string in web.config like this

<add name="ConString" connectionString="SERVER=localhost;DATABASE=databasename;UID=username;PWD=password;Pooling=true;Max Pool Size=100;"/>

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

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 when Open() 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 using the connections correctly (as you do).

Try to close connection after filling dataset. Using statment releases object but it doesnt close connection.