Synchronize Cache with SQL / NoSQL Databases

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

• NCache Details
• SQL Dependency Docs
• Sync Cache with SQL Server Blog

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

• NCache Details
• Oracle Dependency Docs

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

• NCache Details
• OleDB Dependency Docs

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

• NCache Details
• CLR Procedures Docs

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

• NCache Details
• Read-Through Docs
• Sync Cache with CosmosDB Blog