Cache invalidation notification SqlCacheDependency

Question

I am trying to create SQL cache dependency for some reference data in my ASP.NET application (ASP.NET 3.5, SQL 2008). I have enabled sql caching for my database and a couple of tables using aspnet_regiis utility. I have made the required sqlcachedependency entry in web.config. I have added the following code in the application_start event

DateTime dt = DateTime.Now;

            SqlCacheDependency dep1 = new SqlCacheDependency(databaseName, "Products");

            List<Product> productsList = new List<Product>();
            CacheItemRemovedCallback productRefresh = new CacheItemRemovedCallback(RefreshProducts);

            HttpRuntime.Cache.Insert("Products", productsList, depProducts, dt, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, productRefresh);

I reload data from the database in the callback function RefreshProducts.

For some reason I cannot get the callback function to be invoked when I change the data in table. I do not get any run time errors and I can see from AspNet_SqlCacheQueryRegisteredTablesStoredProcedure that the table is enabled for caching. I tested the rest of code by replacing sqldependency with a file dependency. When I changed the file the callback function was invoked immediately, so my mistake is most probably in setting up the sqlcachedependency.

Thanks

score 1 · Answer 1 · answered Jun 29 '11 at 15:55

The error was in MS SQL stored procedures dbo.AspNet_SqlCacheRegisterTableStoredProcedure and dbo.AspNet_SqlCacheUnRegisterTableStoredProcedure. These stored procedure register and unregister tables for SQL cache monitoring but will only work for tables in default schema. My tables were in a different schema so the triggers were not being created properly. I modified these two stored procedures to handle tables with non schema and the problem was resolved

Cache invalidation notification SqlCacheDependency

1 Answers1