Connection Pooling.

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.