Timeout period elapsed obtaining connection from the pool

Timeout period elapsed obtaining connection from the pool

Old forum URL: forums.lhotka.net/forums/t/10419.aspx


JonM posted on Tuesday, June 07, 2011

I'm using CSLA.net 4.1 in a Silverlight MVVM application with WCF dataportal. 

So I"ve run into an issue where after the application runs for a while I get errors that new connections can't be added to the connection pool.  This tells me that my sql calls are not returning connections to the pool. 

My google searches turn up that I need to call Connection.Close() instead of just letting the dispose call deal with it.  So I'm using the Csla ConnectionManager class to manage my connections.  Should I not be doing that?  I've included a little code that shows a typical dataportal call. I'm just looking for a little best practices help here.  If I add a a call to sqlcmd.Connection.Close() in the last line of the using block it fixes it.  However I then have to put this everywhere!  Should'nt the ConnectionManager be dealing with this or is it not intended for dataportal use?  or am I using it wrong?

using (SqlCommand sqlcmd = Csla.Data.ConnectionManager<SqlConnection>.GetManager("TestDB").Connection.CreateCommand())

{

         sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;

         sqlcmd.CommandText = "fetchData";

         sqlcmd.Parameters.AddWithValue("@DataID", criteria.Value);

         using (SqlDataReader sqldr = sqlcmd.ExecuteReader())

         {

                 if (sqldr.Read())

                 {

                          DataValue = sqldr.GetString(0);

                 }

         }

}

 

JonnyBee replied on Tuesday, June 07, 2011

Unfortunately, your code pattern is not correct.

It should read:

using (var conn= Csla.Data.ConnectionManager<SqlConnection>.GetManager("TestDB"))
{
  using (SqlCommand sqlcmd = conn.Connection.CreateCommand())
  {
    sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;
    sqlcmd.CommandText = "fetchData";
    sqlcmd.Parameters.AddWithValue("@DataID"criteria.Value);
    using (SqlDataReader sqldr = sqlcmd.ExecuteReader())
    {
      if (sqldr.Read())
      {
              DataValue = sqldr.GetString(0);
      }
    }
  }
}

The Csla.Data.ConnectionManager needs it 's own using statement as it ecapsulates the connection
and uses reference counting to determine when to release/return the connection to the connection pool.

JonM replied on Wednesday, June 08, 2011

Thank you!  The using I have was calling dispose on command but not on connection.  I just didn't see it!  Thanks!

Copyright (c) Marimer LLC