Synchronize Cache with SQL / NoSQL Databases

NCache is an extremely fast and linearly scalable distributed cache that lets you cache application improve your application performance. When you cache application data, you are creating a copy of data in the cache that also exists in your database. If this data in the database changes, you want to make sure the cache is also updated so it is always consistent with the database.

To handle this situation, NCache provides powerful database synchronization features where the cache synchronizes itself when data changes in relational or NoSQL database. Synchronization here means removing the corresponding cached item from the cache (or reloading it with Read-through). NCache provides the following for database synchronization:

  1. SQLDependency
  2. OracleDependency
  3. Sync Cache with Cosmos DB / MongoDB
  4. OleDB Dependency
  5. CLR Procedures
  6. Auto Reload with Read-Through

The following diagram shows the basic architecture of how NCache supports database synchronization:

Database Synchronization

SQL Dependency

SqlDependency is used by NCache in case your database is SQL Server. You can specify a SqlDependency for any cached item when you're adding or updating it in the cache. SQL Server monitors the dataset for any additions, updates, or removals and notifies NCache through SQL Notifications almost immediately after database update. These are database notifications that get transformed into .NET events.

SqlDependency can be either of the following:

  • Parameterized SQL Query
  • Stored Procedure

You can find more detail on this topic in the blog Sync Cache with SQL Server.

Using Parametrized Query

Here is how you can use SqlDependency in your .NET code with dynamic parametrized queries:

string connString = ConfigurationManager.AppSettings["connectionString"];
string query = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID > @productID";
...
var param = new SqlCmdParams();
...
sqlCmdParam.Add("@productID", param);

SqlCacheDependency sqlDepenency = new SqlCacheDependency(connString, query, SqlCommandType.Text, sqlCmdParam);

Using Stored Procedures

Here is how you can use SqlDependency in your .NET code with stored procedures:

...
var param = new SqlCmdParams();
...
sqlCmdParam.Add("productID", param);

SqlCacheDependency sqlDependency = new SqlCacheDependency(connString, "sp_GetProductByID", SqlCommandType.StoredProcedure, sqlCmdParams);

Oracle Dependency

OracleDependency is used by NCache in case your database is Oracle 10g or later and running on either Windows or Unix. Just like SqlDependency, you can specify OracleDependency for any cached item when you're adding or updating it in the cache.

OracleDependency can be either of the following:

  • Parameterized SQL Query
  • Stored Procedure

Oracle Server then monitors this data-set for any additions, updates, or removals and when that happens, it notifies NCache through Oracle Notifications almost immediately after database update. These notifications use Oracle's client/server communication underneath and talk to NCache through .NET.

Using Parameterized Queries

Here is how you can use OracleDependency in your .NET code with dynamic parameterized queries:

...
var param = new OracleCmdParams();
. . .
oracleParam.Add("productID", param);

OracleCacheDependency oracleDepenency = new OracleCacheDependency(connString, query, OracleCommandType.Text, oracleParam);

Using Stored Procedures

You can use parameterized stored procedure calls in Oracle dependency as shown here:

...
OracleCmdParams param = new OracleCmdParams();
. . .
oracleParam.Add("productID", param);

OracleCacheDependency oracleDependency = new OracleCacheDependency(connString, "sp_GetProductByID", OracleCommandType.StoredProcedure, oracleParam);

Sync Cache with Cosmos DB / MongoDB

NCache also provides more flexibility and control of how and when to invoke a dependency on a cache item. Extensible Notification Dependency allows you to implement your custom logic which specifies when to remove data from the cache. You can write your custom provider for your underlying database like Cosmos DB and MongoDB, and this provider is deployed on the cache. The cache then checks all deployed dependencies whether they need to be removed or not.

You can create your custom provider and deploy it on the cache to use custom dependency in your application. The following code snippet shows how a deployed custom provider for Cosmos DB synchronization can be used in your .NET code while adding items to cache:

Customer customer = LoadCustomerFromDB(customerId);

CacheDependency cosmosDbDependency = new CosmosDbNotificationDependency<Customer>(. . .) ;

var cacheItem = new CacheItem(customer);
cacheItem.Dependency = cosmosDbDependency;
cache.Insert("Customer:" + customer.Id, cacheItem);

OleDB Dependency

OleDB Dependency OleDB Dependency is used by NCache in case your database is neither SQL Server nor Oracle but is an OLEDB compliant database. You can also use DbDependency with SQL Server and Oracle if you do not want their event notifications which could get chatty in case your data is changing very rapidly.

In DbDependency, you create a table called ncache_db_sync in the database which contains one row for each cached item with DbDependency. You modify your database triggers to update rows in this table when the corresponding data in the database changes. NCache polls this table for updated rows, so in one poll, NCache fetches thousands of rows and synchronizes them with the database.

Here is how you can use DbDependency in your .NET code.

DBCacheDependency oledbDependency = DBDependencyFactory.CreateOleDbCacheDependency(connectionString, "PrimaryKey:dbo.Products");

var cacheItem = new CacheItem(product);
cacheItem.Dependency = oledbDependency;
cache.Insert(key, cacheItem);

CLR Procedures

When you have a very large number of items in the cache and all of them need to be synchronized with the database, it is much better to write a CLR Procedure in SQL Server on Windows. This CLR Procedure is called from your database triggers when the relevant data changes. This CLR Procedure then makes asynchronous NCache API calls to either add, update, or remove the corresponding cached item from the cache.

Here is an example of a CLR Procedure that removes the object if it is updated.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void RemoveOnUpdate(string cacheName, string key)
{            
    cacheName = "demoClusteredCache";
    Cache cache = CacheManager.GetCache(cacheName);
    cache.RemoveAsync(key);
}

Auto Reload with Read-Through Handler

The default behavior of database synchronization is to remove the cached item from the database when the corresponding data in the database changes. However, there are situations where you simply want to update it with the latest version of the data.

To handle this need, NCache allows you to combine database synchronization with the Read-Through handler feature of NCache. With this, NCache simply calls the Read-through handler to reload the latest copy of the cached item and then updates the cache with it. This feature provides the flexibility to synchronize NCache with any relational or NoSQL database that has been configured including the following (and more):

  1. SQL Server
  2. Oracle
  3. Cosmos DB
  4. MongoDB

The following code snippet shows how data can be auto reloaded in bulk into NCache from Cosmos DB using Read-Through. You can also find more detail in the blog Sync Cache with CosmosDB.

public Task ProcessChangesAsync(IChangeFeedObserverContext context, IReadOnlyList docs, CancellationToken cancellationToken) 
{
    cacheItems = cache.GetBulk(DocsKeys, new ReadThruOptions {Mode = ReadMode.ReadThruForced} );
    return Task.CompletedTask;
}

What to Do Next?

NCache Details
Download Free 30 Day Install Key
Request a Personalized LIVE Demo
Read Product Documentation

Signup for monthly email newsletter to get latest updates.

© Copyright Alachisoft 2002 - . All rights reserved. NCache is a registered trademark of Diyatech Corp.