NCache 4.6 - 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.
 
1. 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 DEFAULT and work_in_progress BIT DEFAULT). Script to create the table is as follows:
 
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 myTrigger
ON dbo.Products
FOR DELETE, UPDATE
AS
UPDATE ncache_db_sync
SET modified = 1
From ncache_db_sync
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".
 
2. 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.
 
      To utilize the API, include the following namespace in your application: Alachisoft.NCache.Runtime.Dependencies.
 
    //Create object to be added with dependency
            Product product = new Product();
            product.ProductID = 1001;
            product.ProductName = "Chai";
 
            string key = "Product:" + product.ProductID;
 
            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, CacheItemPriority.Default);
 
                //Modify Product record in database while program waits...
                Thread.Sleep(5000);
 
                //... and then check for its existence
                Object item = cache.Get(key);
                if (item == null)
                {
                    //item removed successfully
                }
                else
                {
                    //dependency did not work
                }
            }
            catch (OperationFailedException e)
            {
                //handle exception
            }
 
 
See Also