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);
}
}
}
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.
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