I have created an instance of sqlconnection and using transaction i am executing 5 insert statements (Database - SQL Server 2005). I have the finally block where i call the dispose method of the transaction and close method of the connection object.I often get the below mentioned error:
Exception Type: System.InvalidOperationException
Message: 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.
TargetSite: System.Data.ProviderBase.DbConnectionInternal GetConnection(System.Data.Common.DbConnection)
Source: System.Data
How do i resolve the above issue?do you a transaction.Commit()?
Put the code.
Regards.
PD:
this is a transaction example:
Code Snippet
using (dbcmd.Transaction = dbcon.BeginTransaction())
{
try
{
dbcmd.CommandText = string.Format(sql1, tablaDestino, sqlOrigen);
dbcmd.ExecuteNonQuery();
dbcmd.CommandText = string.Format(sql2, tablaDestino);
dbcmd.ExecuteNonQuery();
dbcmd.CommandText = string.Format(sql3, tablaDestino);
dbcmd.ExecuteNonQuery();
dbcmd.CommandText = string.Format(sql4, tablaDestino);
dbcmd.ExecuteNonQuery();
dbcmd.CommandText = string.Format(sql5, tablaDestino);
dbcmd.ExecuteNonQuery();
dbcmd.Transaction.Commit();
}
catch (DbException dbException)
{
dbcmd.Transaction.Rollback();
throw new PoolException("La transaci￠n de creaci￠n de pool ha fallado","CreatePool", 0, dbException);
}
}
|||Yes, I am doing a commit. We are using DAAB SQLHelper class.
|||You may try to increase the size of the connection pool. For example: Set Min Pool Size=10;Max Pool Size=1000 in your connection string
|||I have increased the connection pool size. min as 5 and max 100. still it gives me a problem.|||I've had simillar problem with the connection pooling. Have you made sure that the connection used is closed after you are finished with it.I found loads of open connections within my code and closing them seams to have helped.
Colin
|||Yes i am closing the connection after using.|||
I don't think this has anything to do with connection pooling once the connection is open -- as soon as you open your connection, it's yours until you close the connection -- no other connection can use it until you close it.
Connection pooling doesn't utilise your physical connection for other logical connections while it's open -- it simply doesn't physically close connections when you close them and assigns those connections to new logical connections by client applications when required.
|||Note that you simply may not have enough connections in the pool initially. By default, the MaxPoolSize=100. Try increasing this by placing MaxPoolSize=500 in the connection string.
