SqlDependency notifications

[jrenae]

Hello,

We're using sqldependency to refresh our application cache if need be, but we're finding that it is not alway refreshing when we expect it to.

While reviewing the code and looking at the cmd object associated with the sqlnotifications, I noticed that we're only selecting the identity column from the table.

Do we need to include all of the columns in the table that we're needing to 'reload' on change? In the below example I want the cache to be reloaded whenever col2 is updated or when there is a new record inserted to the table.

Do we need to change the select stmt to include col2 in order for the cache to be reloaded when col2 is updated for any record?


table definition

create table tbCacheMe (col1 int identity, col2 varchar(25))

//current sql select associated with sqlnotification...notice that we're only selecting the identity column

'select col1 from dbo.tbCacheMe'

Do we need this query instead to ensure the refresh since col2 is the only column that will be updated?

'select col1, col2 from dbo.tbCacheMe'

Thanks for any help.

 

[jmeckley]

you should use a timestamp to determine if the cache must be refreshed (not id or an arbitrary column). If the timestamp is cache is less than the database. invalidate the cache. the timestamp should be updated each time the table/record is updated.

the timestamp can be as simple as an incrementing bigint which adds 1 with each update.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jrenae]

So what would the select statement be to ensure that the OnChangeEvent was fired when we update col2?

 

[jmeckley]

timestamp in this sense can also mean revision. somewhere in the database you would add a column called timestamp. the logic within the application would update the timestamp. the sqlcachedependency wouldn't know about col2. it would only know about the timestamp. if the timestamp in cache < timestamp in the database invalidate the cache.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jrenae]

Still, my question is about the select statement associated with the sql notification.

According to your timestamp proposal, would I need to add the timestamp column to the select stmt in order for the cache to be reloaded automatically when a record was updated?

 

[jmeckley]

I'm not familiar with sql cache dependency objects. but the logic is pretty simple. here is a crude implementation.

public class MyCacheDependency : CacheDependency
{
   private int current = 0;

   public override bool HasChanged
   {
      get { return HasTimeStampChanged(); }
   }

   private bool HasTimeStampChanged()
   {
      //get a connection and command
      command.CommandText = "select timestamp from [table]";
      int now = (int)command.ExecuteScalar();
      bool changed = current < now;
      current = now;
      return changed;
   }
}
[code]

Jason Meckley
Programmer
Specialty Bakers, Inc.