CODEWITHSUNDEEP
asp.net
pyram
pyram

Reputation: 935

asp.net Enterprise Library, All connections in use (closed)

im using the asp.net Enterprise Library to insert and update data to an sql database.

c# code:

Database db = DatabaseFactory.CreateDatabase();
IDbConnection connection = db.GetConnection();
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();
DBCommandWrapper command;

try
{
//insert part
command = db.GetStoredProcCommandWrapper("stored_procedure1");
command.CommandTimeout = 900;
command.AddInParameter("@parameter1", DbType.Int32, 3);
db.ExecuteNonQuery(command, transaction);

//update part
command = db.GetStoredProcCommandWrapper("stored_procedure2");
command.CommandTimeout = 900;
command.AddInParameter("@param1", DbType.Int32, 5);
db.ExecuteNonQuery(command, transaction);

transaction.Commit();
}
catch (SqlException e)
{
  transaction.Rollback();
  throw (new ApplicationException("sql error", e));
}
catch (Exception e)
{
  transaction.Rollback();
  throw (new ApplicationException("error", e));
}
finally
{
connection.Close();
}

the code is on a method. The method is executed many times and sometimes im getting 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 questions are: Is it ok to have the insert and update part together as it is above?

Having the insert and update part together is causing this error?

Thanks.

Upvotes: 1

Views: 745

Answers (1)

Victor
Victor

Reputation: 666

First you do not roll back your transaction on error.

Secondly in order to guarantee that your finally block is called you need to add the catch block.

Try {
...
}
Catch {
}
finally {
close connection
}

Read the second paragraph of the link located below

http://msdn.microsoft.com/en-us/library/zwc8s4fz(v=vs.100).aspx

Upvotes: 3

Related Questions