We have some code that hits our SQL Server 2005 database via LINQ to SQL to read a list of objects from a table, converts them to our serializable middle tier version of the objects, then caches that list using an SqlCacheDependency.
However, at several times throughout the day, a large update on the underlying table occurs which seems to have the effect of allowing LINQ to SQL to select a list but does not allow the caching query below to register a Notification dependency on the table during the oSqlCmd.ExecuteScalar(); command.
Two questions:
- Is it possible to alter the code below so that the query notification can be registered?
- If not, is there any clean up that needs to occur so that we don't end up with stranded conversation endpoints in the SQL Server?
try {
List<object> oList = new List<object>();
//Grab list from DB, convert to serializeable objects
//Register cache dependency
SqlConnection oSqlCon = new SqlConnection(new CSConfigurationMgr().GetConnectionString(ConnectionStringKey.CustomerMgmnt));
SqlCommand oSqlCmd = NotificationsSupport.CSSqlCacheDepCmd(@"...REALLY BIG QUERY... = @p0", oSqlCon);
oSqlCmd.Parameters.Add(
new SqlParameter
{
ParameterName = "@p0",
SqlDbType = SqlDbType.Int,
Value = iCustID
});
SqlCacheDependency oSqlDependency = new SqlCacheDependency(oSqlCmd);
oSqlCon.Open();
oSqlCmd.ExecuteScalar();
oSqlCon.Close();
//Create dependency
HttpRuntime.Cache.Insert(key, value, oSqlDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.High, new CacheItemRemovedCallback(OnListChanged));
}
catch (SqlException) { }