SqlCacheDepende ncy immediately gets DependencyChang ed

[Reinout]

Hi

I set up a sql cache dependency, but always receive a "DependencyChan ged" notification straight away (through CacheItemRemove d 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 SqlQueryNotific ationService :

exec sp_executesql N'BEGIN CONVERSATION TIMER (''f31d830f-fc01-dc11-9742-00166f698a50'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_na me, conversation_ha ndle, cast(message_bo dy AS XML) as message_body from [SqlQueryNotific ationService-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_na me, conversation_ha ndle, cast(message_bo dy AS XML) as message_body from [SqlQueryNotific ationService-103f7ce3-56c0-4c67-affb-bb320ed5687a]), TIMEOUT @p2;',N'@p2 int,@p1 uniqueidentifie r',@p2=60000,@p 1='F91D830F-FC01-DC11-9742-00166F698A50'


Any ideas about what's wrong or missing ?

Many thanks
Reinout

Code :

Global.asax :

void Application_Sta rt(object sender, EventArgs e)
{
// Code that runs on application startup
System.Data.Sql Client.SqlDepen dency.Start(Con figurationManag er.ConnectionSt rings["B2exDataba se"].ToString());
}

void Application_End (object sender, EventArgs e)
{
// Code that runs on application shutdown
System.Data.Sql Client.SqlDepen dency.Stop(Conf igurationManage r.ConnectionStr ings["B2exDataba se"].ToString());
}

Business Object :

private void DataPortal_Fetc h(FilterCriteri a criteria)
{
RaiseListChange dEvents = false;
IsReadOnly = false;

WorkbookList wbList;

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

if (wbList == null)
{
SqlCacheDepende ncy scd = null;

using (DataTable dt = B2exDAL.Instanc e.GetWorkbooks( ref scd))
{
DataTableReader dtr = dt.CreateDataRe ader();
SafeDataReader sdr = new SafeDataReader( dtr);

while (sdr.Read())
{
this.Add(Workbo okInfo.GetWorkb ook(sdr));
}
}

wbList = this;

cache.Insert("W orkbooks", wbList, scd, Cache.NoAbsolut eExpiration, new TimeSpan(0, 20, 0),
CacheItemPriori ty.NotRemovable , new System.Web.Cach ing.CacheItemRe movedCallback(T est));
}

IsReadOnly = true;
RaiseListChange dEvents = true;
}

private void Test(string text, object sender, System.Web.Cach ing.CacheItemRe movedReason reason)
{
// Notify deletion from cache
}

DAL :

public override DataTable GetWorkbooks(re f System.Web.Cach ing.SqlCacheDep endency scd)
{
DataTable workbooks = new DataTable();

using (SqlConnection conn = new SqlConnection(b ase.ConnectionS tring))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Sto redProcedure;
cmd.CommandText = PROC_GET_WORKBO OKS;

scd = new SqlCacheDepende ncy(cmd);

SqlDataAdapter adapter = new SqlDataAdapter( );
adapter.SelectC ommand = cmd;

adapter.Fill(wo rkbooks);
}

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(re f System.Web.Cach ing.SqlCacheDep endency scd)
{
DataTable workbooks = new DataTable();

using (SqlConnection conn = new SqlConnection(b ase.ConnectionS tring))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Sto redProcedure;
cmd.CommandText = PROC_GET_WORKBO OKS;

SqlCommand cmdDependency = new SqlCommand("SEL ECT <field> FROM <table>", conn);
scd = new SqlCacheDepende ncy(cmdDependen cy);

SqlDataAdapter adapter = new SqlDataAdapter( );
adapter.SelectC ommand = cmd;

adapter.Fill(wo rkbooks);
}

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_PUBLIS HERS", and voila, it started to work!