CODEWITHSUNDEEP
c#asp.netsqldependencyquery-notifications
Philip
Philip

Reputation: 33

SqlCacheDependency remove callback fires directly after Cache.Add

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)

Upvotes: 1

Views: 489

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294407

Watch QN:Subscription Event Class in SQL Profiler. Run your test case. There will be an event fired with EventSubClass value Subscription fired. the TextData will contain exactly the subscription notification Info, Source and Type (I think it will be in the EventText XML element).

With this you will know exactly how is your query not conforming and you can fix the issue accordingly.

Upvotes: 1

Related Questions