views:

36

answers:

0

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:

  1. Is it possible to alter the code below so that the query notification can be registered?
  2. 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) { }