Setup OleDb Database Dependency Environment

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 database dependency, please set the environment for using the dependency by following the given steps.

Prerequisites to Setup OleDb Database Dependency

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:

• 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 a modified flag to true. On cache clean up interval, NCache polls the 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.

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

Important

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

Create an UPDATE and 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 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 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 the clean interval, NCache DBCacheDependency does the following:

1. Sets the work_in_progress flag for those rows where the 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 the work_in_progress flag is set are removed from the ncache_db_sync table.

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

See Also

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