Connection pooling problem

[PH]

Hi;

I got an application written in ASP.NET (VB.NET and C#) that uses
connection pooling.
The Max connections defined in the connection command are 50, and there
are no more than 3 or 4 user using the application (intranet).
I very carefully open the connections in the LOAD event and close them
in the PreRender event.
Usually I got only one connection opened per FORM.

I also got a Class called ExecuteSQL(). This class has a public member
called Connection of type SQLConnection.
If this parameter (connection) is supplied I open the connection as I
explained before, in the LOAD event, I call ExecuteSQL, and close the
connection on PreRender.

If i do not supply this parameter, ExecuteSQL() uses an internal
connection, and close I close it calling ExecuteSQL.Dispose().

Again I'm very careful opening and closing connections.

Anyway, I got this error when doing something in the database saying
that a connection cannot be retrieved from the connection pool, probably
because all of them are in use.
Remember that I got at most 4 users and 50 predefined connections in the
pool.

What's happening?
Some help? ;-)

Thanks

[Peter Bromberg [C# MVP]]

If you are getting this exception, you need to revisit your code since
somewhere you have connections that - even though you "Say" they are, are NOT
Being closed.

The best practices pattern for ADO.NET is to open a connection IMMEDIATELY
BEFORE doing the Sql call, and close it immediately thereafter.

This allows your connection to return to the pool. Subsequent requests for
this connection using the same connection string don't incur the overhead of
instantiating a connection, since it comes directly out of the pool.

The default pool has 100 connections. In most cases, there should be no need
to change this.

Hope that helps.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"PH" wrote:

Hi;

I got an application written in ASP.NET (VB.NET and C#) that uses
connection pooling.
The Max connections defined in the connection command are 50, and there
are no more than 3 or 4 user using the application (intranet).
I very carefully open the connections in the LOAD event and close them
in the PreRender event.
Usually I got only one connection opened per FORM.

I also got a Class called ExecuteSQL(). This class has a public member
called Connection of type SQLConnection.
If this parameter (connection) is supplied I open the connection as I
explained before, in the LOAD event, I call ExecuteSQL, and close the
connection on PreRender.

If i do not supply this parameter, ExecuteSQL() uses an internal
connection, and close I close it calling ExecuteSQL.Dispose().

Again I'm very careful opening and closing connections.

Anyway, I got this error when doing something in the database saying
that a connection cannot be retrieved from the connection pool, probably
because all of them are in use.
Remember that I got at most 4 users and 50 predefined connections in the
pool.

What's happening?
Some help? ;-)

Thanks

[Mark R. Dawson]

Hi PH,
I don't know you are coding but one good way to make sure you never forget
to close a connection is to use the "using" statement. This guarantees that
the Dispose method always gets called which internally calls close and makes
sure the underlying connection gets returned to the connection pool.

For example:

using(SqlConnection connection = new SqlConnection())
{
// do DB open and execute tasks
}

// Here the connection has been closed.

The using statement will call Dispose even if an exception is thrown inside
the code, since behind the scenes it makes sure the call to Dispose is in a
finally block.

Mark Dawson
http://www.markdawson.org
"PH" wrote:

Hi;

I got an application written in ASP.NET (VB.NET and C#) that uses
connection pooling.
The Max connections defined in the connection command are 50, and there
are no more than 3 or 4 user using the application (intranet).
I very carefully open the connections in the LOAD event and close them
in the PreRender event.
Usually I got only one connection opened per FORM.

I also got a Class called ExecuteSQL(). This class has a public member
called Connection of type SQLConnection.
If this parameter (connection) is supplied I open the connection as I
explained before, in the LOAD event, I call ExecuteSQL, and close the
connection on PreRender.

If i do not supply this parameter, ExecuteSQL() uses an internal
connection, and close I close it calling ExecuteSQL.Dispose().

Again I'm very careful opening and closing connections.

Anyway, I got this error when doing something in the database saying
that a connection cannot be retrieved from the connection pool, probably
because all of them are in use.
Remember that I got at most 4 users and 50 predefined connections in the
pool.

What's happening?
Some help? ;-)

Thanks