Enabling Notifications For SQL Server

NOTE: This feature is not available in NCache Express and Professional edition.

For enabling notifications for SQLServer tables, you need to follow these steps:

Create a table 'ncache_db_sync' having four fields (cache_key VARCHAR, cache_id VARCHAR, modified BIT and work_in_progress BIT). Script to create the table is as follows:

//Create table'ncache_db_sync'

CREATE TABLE ncache_db_sync(

cache_key VARCHAR(256),

cache_id VARCHAR(256),

modified BIT DEFAULT(0),

work_in_progress BIT DEFAULT(0),

PRIMARY KEY(cache_key, cache_id) );
Create UPDATE and DELETE triggers, for every table on which notification is required. They set the 'modified' field of corresponding row in the ncache_db_sync table to 1.To carry out the task, see the following sample script that creates trigger on 'Products' table:

//create trigger

CREATE TRIGGER myTrigger

ON dbo.Products

FOR DELETE, UPDATE

AS

UPDATE ncdbs

SET modified = 1

from ncache_db_sync ncdbs

inner join Deleted old on cache_key = (Cast((old.ProductID) as VarChar)

+ ':dbo.Products' );

Note: cache_key must be the same key that is used to add corresponding record in the cache.

Now, As soon as an item is added to the cache with dependency, a row will be created in table 'ncache_db_sync' for this cache key. We'll have to make sure that the format of cache key while adding into cache is exactly same as defined in the corresponding trigger. For our example, cache key for product id 10 should be like, "10:dbo.Products".

Add the following namespaces in your application:

using Alachisoft.NCache.Runtime.Caching;

using Alachisoft.NCache.Runtime.Dependencies;

Following code demonstrates how to add items with dependency.

Cache _cache = NCache.InitializeCache("myreplicatedcache");

_cache.ExceptionsEnabled = true;

try

{

string connectionString = "Provider=SQLOLEDB;Data Source=localhost;Database=northwind;User Id=sa;Password=;";

ProductsFactory productsFactory = new ProductsFactory(connectionString);

Collection<Products> products = productsFactory.FindByCategoryID(8);

foreach (Products product in products)

{

Alachisoft.NCache.Runtime.Dependencies.DBCacheDependency dependency = Alachisoft.NCache.Runtime.Dependencies.DBDependencyFactory.CreateOleDbCacheDependency(connectionString, product.ProductID + ":dbo.Products");

//cache key for product id 10 will be "10:dbo.Products"

mycache.Insert(product.ProductID + ":dbo.Products", product, dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, Alachisoft.NCache.Runtime.CacheItemPriority.Default);

}

catch (Exception ex)

{

//throw it.

}

On execution of the above code, a few rows will be added into the table 'ncache_db_sync'. Table 'ncache_db_sync' should now look like:

As we can see the value for column 'modified' is false for all rows. This means that the cached items in myreplicatedcache for these cache_keys are still valid. As soon as we modify a row for, lets say, product id 10 in products table, it will set modified value as true in its respective row.

This means that the cached item for cache_key "10:dbo.Products" in myreplicatedcache is no more valid and this should be invalidated.

On clean interval, NCache DbCacheDependency does the following:

Sets the work_in_progress flag for those rows where modified flag is set. And fetches all those rows.
Removes all the keys from the cache as they all are invalid now.
After successfully removing all the keys from the cache, all those rows where work_in_progress flag is set are removed from the 'ncache_db_sync' table.

If a node crashes while it was removing items from the cache, the next node in the cluster will start process from step one.

Send comments on this topic.