Connection leak in C # DataBase.ExecuteScalar

Question

The following method in a static class gives me a timeout exception because the connection pool is maximum.

In debug mode, I looked at sql management studio and saw that there were 150 sleeping processes.

I expected the connections to close automatically ... I also tried to put a static member and still got the same error.

Any ideas? heres code:

public static Decimal ExecuteScalarDec(string procName, params object[] parameters) { try { return (Decimal)DatabaseFactory.CreateDatabase().ExecuteScalar(procName, parameters); } catch (Exception ex) { throw new Exception(procName.ToString() + " " + parameters.ToString(), ex); } } 

---

"By design, most database class methods handle opening and closing database connections for each call, so the application code should not contain code for managing connections." ExecuteReader is an exception (because it returns a resource). ExecuteScalar is in limbo: it returns a scalar. However, I think the scalar can be quite heavy, for example. a stream built from a large return data type, and this will require the cone to be open. - Remus Rusanu

I can not comment on your answer because it says: “commenting requires 50 reputations” After I registered my user ...

I return the Id column to executeScalar (), and the value is returned - I know this, because the next call to execute the scalar is called only after receiving the value ... This is not a seam, to make sense that the stream will remain open forever And I saw in sql Management that all processes were asleep.

Answer 1

 public static Decimal ExecuteScalarDec(string procName, params object[] parameters) { try { using (Database database = DatabaseFactory.CreateDatabase()) { return (Decimal)database.ExecuteScalar(procName, parameters); } } catch (Exception ex) { throw new Exception(procName.ToString() + " " + parameters.ToString(), ex); } } 

Update

OK, as this is EnterpriseLibrary code. The database class implements ExecuetScalar like this (other signatures will eventually crash):

  public virtual object ExecuteScalar(DbCommand command) { if (command == null) throw new ArgumentNullException("command"); using (ConnectionWrapper wrapper = GetOpenConnection()) { PrepareCommand(command, wrapper.Connection); return DoExecuteScalar(command); } } 

and ConnectionWrapper provides the connection (the end of the source file in the link), therefore, the theory goes, your call should be in order and delete the connection.

The GetOpenConnection () method returns a wrapper that removes the connection ... unless the current TransactionScopeConnections exists:

  protected ConnectionWrapper GetOpenConnection(bool disposeInnerConnection) { DbConnection connection = TransactionScopeConnections.GetConnection(this); if (connection != null) { return new ConnectionWrapper(connection, false); } return new ConnectionWrapper(GetNewOpenConnection(), disposeInnerConnection); } 

And this is how TransactionScopeConnections returns the connection:

  public static DbConnection GetConnection(Database db) { Transaction currentTransaction = Transaction.Current; if (currentTransaction == null) return null; Dictionary<string, DbConnection> connectionList; DbConnection connection; lock (transactionConnections) { if (!transactionConnections.TryGetValue(currentTransaction, out connectionList)) { // We don't have a list for this transaction, so create a new one connectionList = new Dictionary<string, DbConnection>(); transactionConnections.Add(currentTransaction, connectionList); // We need to know when this previously unknown transaction is completed too currentTransaction.TransactionCompleted += OnTransactionCompleted; } } lock (connectionList) { // Next we'll see if there is already a connection. If not, we'll create a new connection and add it // to the transaction list of connections. // This collection should only be modified by the thread where the transaction scope was created // while the transaction scope is active. // However there no documentation to confirm this, so we err on the safe side and lock. if (!connectionList.TryGetValue(db.ConnectionString, out connection)) { // we're betting the cost of acquiring a new finer-grained lock is less than // that of opening a new connection, and besides this allows threads to work in parallel connection = db.GetNewOpenConnection(); connectionList.Add(db.ConnectionString, connection); } } return connection; } 

Now, if I'm not mistaken, TransactionsScopeConnections will always create a new connection for a completely new database object (as in your case) and save the internal dictionary in it. The Database object does not implement a one-time, so I'm lost in determining who should clear the connections from this internal TransactionScopeConnecitons list.

Matt, is it possible to follow the next steps in this article about CLR leaks and see if there are a large number of database objects in your process? Download SOS and run !dumpheap -type Microsoft.Practices.EnterpriseLibrary.Data.Database . If you find many objects, can you track a stack of contacts on some of them with !gcroot <AddressOfObject>