SQL Server Pooling in ASP.NET

[karunakar]

Hi All


I am getting following the Error when I am running my web application(C#)
using the SQL Server as my Database. Frequent clicks on multiple links
causes this error.


ERROR: "Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached."

My Web.config is as follows:
<add key="SQLdbConnString"
value="server=192.168.0.XXX;uid=sa;pwd=xx;database=xxxxxx;Min Pool
Size=5;Max Pool Size=40;"/>
I'm using the SQLHelper Class to connect to the database.


private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,
SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[]
cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}



Please suggest a way to handle the problem.

Adv Thanks

Venu.

 

[Hans Kesting]

Hi All


I am getting following the Error when I am running my web application(C#)
using the SQL Server as my Database. Frequent clicks on multiple links
causes this error.


ERROR: "Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached."

My Web.config is as follows:
<add key="SQLdbConnString"
value="server=192.168.0.XXX;uid=sa;pwd=xx;database=xxxxxx;Min Pool
Size=5;Max Pool Size=40;"/>
I'm using the SQLHelper Class to connect to the database.


private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,
SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[]
cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}



Please suggest a way to handle the problem.

Adv Thanks

Venu.

You left out some code. I don't see where you execute the command,
and more importantly, I don't see where you *close* the connection.

The errormessage says that there are too many opened connections. The
solution is to close the connection as soon as you are done with it. You
might even use a try/finally (the execute in the "try", the close in the
"finally") to be really sure the connection will close.

 

[Morgan]

As Hans suggested, you're not showing where you close the connection when
you're done with it. You should ALWAYS call the .Close() method when using a
DataReader, otherwise you'll end up with the error you received.