NCache 4.4 - Online Documentation

Using Polling Based Dependency

 
Polling based dependency enables you to keep the cache items synchronized with any of the database. SQL and Oracle dependencies explained earlier need SQL/Oracle databases to notify NCache about any change in the database, however if  database does not provides Change Notifications feature, NCache provides the facility to synchronize cache with database using polling based dependency.
 
i. How to Prepare Database for Polling Dependency
 
Since polling based dependency works as NCache polls database for changes, database has to be configured to keep record of changes in database. This includes creating a table named "ncache_db_sync" and creating a trigger to update this table according to changes in database.
 
Follow these steps to configure database:
 
  • 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
fromncache_db_syncncdbs
inner join Deleted old on cache_key = (Cast((old.ProductID) as VarChar)
  + ':dbo.Products' );
 
The syntax of SQL scripts specified here are for SQL server database. Change the syntax according to the database.
 
 
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. The format of cache key while adding into cache should exactly be the exactly same as defined in the corresponding trigger, e.g., for trigger explained above, cache key for product id 10 should be, "10:dbo.Products".
 
ii. Adding Data with Polling Based Dependency
 
The following code shows the use of polling based dependency with SQL server database, where database has been already configured to use polling based dependency.
 
//Create object to be added with dependency
Product product=new Product();
product.ProductID=1001;
product.ProductName="Chai";
 
try
{
    //Creating Polling based dependency
    string connectionString = "Provider=SQLOLEDB;Data Source=localhost;Database=northwind;User Id=sa;Password=;";
    DBCacheDependency oledbDependency = DBDependencyFactory.CreateOleDbCacheDependency(connectionString, product.ProductID + ":dbo.Products");
 
    //cache key for product id 1 will be "1:dbo.Products"
    cache.Insert(product.ProductID + ":dbo.Products", product, oledbDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, Alachisoft.NCache.Runtime.CacheItemPriority.Default);
}
catch (OperationFailedException e)
{
    // handle exception
}
//Modifying Product record in database will automatically remove Product from cache
 
See Also
Cache Dependencies Overview | Using SQL Dependency | Using Oracle Dependency | Using CLR Procedures to Call NCache | Adding Data with File and Key Dependencies | Synchronizing Cache with Custom Data Sources | Synchronize Data across Multiple Caches  | Add Dependency to the Existing Cached Item | Aggregate Cache Dependency
Send comments on this topic.