Setup OleDb Environment

Polling based dependency enables you to keep the cache items synchronized with any of the database. SQL and Oracle dependencies need SQL/Oracle databases to notify NCache about any change in the database. They use database notifications to notify on any modification. However if database does not provide the Change Notifications feature, NCache provides the facility to synchronize cache with database using polling based dependency.

In order to use OleDb dependency, please set the environment for using the dependency by following the following steps.

Pre-Requisite

OleDb Dependency uses the concept of polling. In order to use polling, make sure that trigger is supported by your data source.

Step 1: Create Table in Database

Create a table named as ncache_db_sync in the database which is then used by NCache to synchronize the database with the cache store. There are 4 columns in this table which are as follows:

  • cache_key
  • cache_id
  • modified
  • work_in_progress

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.

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_sync table.

Following script is used to create the table:

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)
);
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.

Step 2: Create Trigger in Database

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' );

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.

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.

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.

Once the environment is set, use OleDb dependency using Sync Cache with OleDb in Programmer's Guide.

See Also

Setup SQL Server Environment
Setup Oracle Database Environment
Setup SQL Server for CLR Procedures
Monitor Caches