Working around connection open errors

richardb posted on Wednesday, December 30, 2009

There's an annoying and intermittent feature with SQL and connection pooling whereby the connection in the pool can be marked as invalid.  So when your code comes to open the connection it gets given that invalid connection from the pool, and your code fails and an exception is thrown.

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

A workaround is to put in some automatic retry code in around the Using blocks, but would it be useful to put this in the CSLA data code - perhaps in ConnectionManager.cs?

 

private ConnectionManager(string connectionString, string label)
{
_label = label;
_connectionString = connectionString;
string provider = ConfigurationManager.AppSettings["dbProvider"];
if (string.IsNullOrEmpty(provider))
   provider = "System.Data.SqlClient";

DbProviderFactory factory = DbProviderFactories.GetFactory(provider);

// open connection

//TODO: Put some retry code here in case of invalid connection exception???????
_connection = factory.CreateConnection();
_connection.ConnectionString = connectionString;
_connection.Open();
}