I have a problem with SqlCacheDependency that I cannot wrap my head around. The CacheItemRemovedCallback will fire as soon as I add something to the cache when using a query for notification (it works when I am using databaseEntryName and tableName but that is to blunt for me). I have checked http://msdn.microsoft.com/en-us/library/ms181122.aspx about 20 times but I still cannot find what I am doing wrong.
The code I am using:
string connString = MsSqlUtil.GetConnectionString();
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connString);
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connString, "Product");
SqlDependency.Start(connString);
Product product = ProductProvider.Get(10);
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand cmdProduct = new SqlCommand(@"
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
SET ARITHABORT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT dbo.Product.ProductId, dbo.Product.Name FROM dbo.Product WHERE dbo.Product.ProductId = 10", connection);
SqlCacheDependency myProductDependency = new SqlCacheDependency(cmdProduct);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (SqlDataReader reader = cmdProduct.ExecuteReader())
{
while (reader.Read())
{
}
}
HttpContext.Current.Cache.Add("Product:10", product, myProductDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal, OnRemove);
}
//Callback
public static void OnRemove(string key, object cacheItem, System.Web.Caching.CacheItemRemovedReason reason)
{
//this fires directly and the reason is always DependencyChanged however nothing has changed in the database, weird!
}
I understand that there must be something wrogn with the query since http://msdn.microsoft.com/en-us/library/ms181122.aspx tells me "If these options or the isolation level is not set appropriately, the notification is fired immediately after the SELECT statement is executed." however I can't figure out what is wrong. The ProductId column is of type int and Name nvarchar(50)
