Polling based Database Dependency

NCache supports another database dependency called Polling Based Dependency which is basically designed to work on large scale. Notification Based Dependency only works with the database server which provides support for data change notifications i.e. either with SQL Server(2005 or above) or Oracle (10g or later). Whereas Polling Based Dependency also works with other databases which do not provide data change notifications support. Also, Notification Based Dependency is not as much resource efficient as Polling Based Dependency for using it on large scale.

In Polling Based Dependency, a table named ncache_db_sync needs to be created in database which is used by NCache to synchronize the database with cache store. Also, UPDATE and DELETE triggers need to be created for every table on which notification is required. These triggers will be scripted to invalidate corresponding cache keys in this table in case of data change.

When an item is added to the cache with dependency, a row will be created in this table for this cache key as shown:

In case of data change in the table, corresponding triggers will set modified flag to true. On cache clean up interval, NCache polls ncache_db_sync table for modified keys, collects them, sets their work_in_progress flag to true and finally removes all corresponding keys from the cache. After successfully removing keys from the cache, all those rows where the work_in_progress flag is true are removed from the ncache_db_synctable.

Important

In a clustered cache, if a node crashes while it was removing items from the cache, the next node in the cluster will again start the process.

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 provide the Change Notifications feature, NCache provides the facility to synchronize cache with database using polling based dependency.

Note

In notification based dependencies like Oracle dependency, it is the responsibility of the database to notify changes to the cache, where as in polling based dependency, NCache polls the database for any changes.

Prepare Database for Polling Dependency

Since polling based dependency works on the concept of NCache polling the database for changes, the database has to be configured to keep a record of those changes in a table. 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:
    1. cache_key
    2. cache_id
    3. modified
    4. work_in_progress

The 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 a trigger on the 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' );
Important

The syntax of SQL scripts specified here are for the Oracle database. Please change the syntax according to the database being used in your application.

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

On clean interval, NCache DBCacheDependency does the following:

  1. Sets the work_in_progress flag for those rows where modified flag is set, and fetches all those rows.

  2. Removes all keys from the cache as they are all expired now.

  3. 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.

Add Data with Polling Based Dependency

The following code shows how to use polling based dependency with the Oracle database. In this case the 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; //Prouct:1001
try{    
      //Creating Polling based dependency

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

      DBCacheDependency oledbDependency = DBDependencyFactory.CreateOleDbCacheDependency(connectionString, key);

      cache.Insert(key, 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{
            // item not removed successfully
      }
}
catch (OperationFailedException e){
    //handle exception
}