SqlCacheDependency immediately gets DependencyChanged

[Reinout]

Hi

I set up a sql cache dependency, but always receive a "DependencyChanged" notification straight away (through CacheItemRemoved callback). Modifying the procedure didn't help me.
It used to work running on a SQL 2000 database with a SqlDataSource and a few things set in the configuration file.
The current setup is using SQL 2005 Express and caching in the datalayer/business objects.

Running profiler, I notice events ran by the SqlQueryNotificationService :

exec sp_executesql N'BEGIN CONVERSATION TIMER (''f31d830f-fc01-dc11-9742-00166f698a50'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-103f7ce3-56c0-4c67-affb-bb320ed5687a]), TIMEOUT @p2;',N'@p2 int',@p2=60000

exec dbo.p_b2ex_get_workbooks

exec sp_executesql N'END CONVERSATION @p1; BEGIN CONVERSATION TIMER (''f31d830f-fc01-dc11-9742-00166f698a50'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-103f7ce3-56c0-4c67-affb-bb320ed5687a]), TIMEOUT @p2;',N'@p2 int,@p1 uniqueidentifier',@p2=60000,@p1='F91D830F-FC01-DC11-9742-00166F698A50'


Any ideas about what's wrong or missing ?

Many thanks
Reinout

Code :

Global.asax :

void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
System.Data.SqlClient.SqlDependency.Start(Configur ationManager.ConnectionStrings["B2exDatabase"].ToString());
}

void Application_End(object sender, EventArgs e)
{
// Code that runs on application shutdown
System.Data.SqlClient.SqlDependency.Stop(Configura tionManager.ConnectionStrings["B2exDatabase"].ToString());
}

Business Object :

private void DataPortal_Fetch(FilterCriteria criteria)
{
RaiseListChangedEvents = false;
IsReadOnly = false;

WorkbookList wbList;

Cache cache = HttpRuntime.Cache;
wbList = cache["Workbooks"] as WorkbookList;

if (wbList == null)
{
SqlCacheDependency scd = null;

using (DataTable dt = B2exDAL.Instance.GetWorkbooks(ref scd))
{
DataTableReader dtr = dt.CreateDataReader();
SafeDataReader sdr = new SafeDataReader(dtr);

while (sdr.Read())
{
this.Add(WorkbookInfo.GetWorkbook(sdr));
}
}

wbList = this;

cache.Insert("Workbooks", wbList, scd, Cache.NoAbsoluteExpiration, new TimeSpan(0, 20, 0),
CacheItemPriority.NotRemovable, new System.Web.Caching.CacheItemRemovedCallback(Test)) ;
}

IsReadOnly = true;
RaiseListChangedEvents = true;
}

private void Test(string text, object sender, System.Web.Caching.CacheItemRemovedReason reason)
{
// Notify deletion from cache
}

DAL :

public override DataTable GetWorkbooks(ref System.Web.Caching.SqlCacheDependency scd)
{
DataTable workbooks = new DataTable();

using (SqlConnection conn = new SqlConnection(base.ConnectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = PROC_GET_WORKBOOKS;

scd = new SqlCacheDependency(cmd);

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;

adapter.Fill(workbooks);
}

return workbooks;
}

[kenobewan]

Here is an article that may help:
SQL Express Dependency Caching

[Reinout]

Apparantly I did nothing wrong in the code.

Since I was becoming kinda desperate, I changed my procedure to a hardcoded select statement without any other code. All of a sudden, it worked.
I found out that left joins are not allowed when using the notification service.

At the moment, I don't see another way than creating a custom query giving me an indication for changed records in one of the tables, and at that time invalidate the BO.
At least it seems to be better than polling the DB manually every few seconds.

Other workarounds are still welcome though !

[Reinout]

Updated DAL code :


public override DataTable GetWorkbooks(ref System.Web.Caching.SqlCacheDependency scd)
{
DataTable workbooks = new DataTable();

using (SqlConnection conn = new SqlConnection(base.ConnectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = PROC_GET_WORKBOOKS;

SqlCommand cmdDependency = new SqlCommand("SELECT <field> FROM <table>", conn);
scd = new SqlCacheDependency(cmdDependency);

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;

adapter.Fill(workbooks);
}

return workbooks;
}

[zumonth]

I had the same issue with a stored proc before, and after spending a few hours, I tried something that made it work.

Initially I was setting the command text to the name of the stored procedure, say, "GET_PUBLISHERS", and I used to get the notification immediately, causing my cache to get flushed. Then I added "dbo." in front of the stored proc name, like so: "dbo.GET_PUBLISHERS", and voila, it started to work!