Setup OleDb Environment
This feature is only available in NCache Enterprise.
Polling based dependency enables you to keep the cache items synchronized with any of the databases. SQL and Oracle dependencies need SQL/Oracle databases to notify NCache about any change in the database. They use database notifications to notify of any modification. However, if the database does not provide the Change Notifications feature, NCache provides the facility to synchronize the cache with the database using polling based dependency.
In order to use the OleDb dependency, please set the environment for using the dependency by following the given steps.
OleDb Dependency uses the concept of polling. In order to use polling, make sure that the trigger is supported by your data source.
Step 1: Create a Table in the Database
Create a table named
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:
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 a modified
flag to true. On cache clean up interval, NCache polls the
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
flag is true are removed from the
The 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) );
The syntax of SQL scripts specified here is for the Oracle database. Please change the syntax according to the database being used in your application.
Step 2: Create a Trigger in the Database
DELETE triggers for every table on which notification is
required. They set the
modified field of the corresponding row in the
ncache_db_sync table. To carry out the task, see the following sample
script that creates a trigger on the
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 the same
as defined in the corresponding trigger. For example, in the trigger explained
above, the cache key for product id 10 should be
On the clean interval,
work_in_progressflag for those rows where the
modifiedflag is set, and fetches all those rows.
Removes all keys from the cache as they are all expired now.
After successfully removing all the keys from the cache, all those rows where the
work_in_progressflag is set are removed from the
Once the environment is set, use the OleDb dependency using Sync Cache with OleDb in the Programmer's Guide.