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 a database update. These are database notifications that get transformed into .NET events.

SqlDependency can be either of the following:

• Inline Queries
• Stored Procedures

You can find more details on this topic in the Sync Cache with SQL Server docs.

Using Inline Queries

Here's how you can create a SQL dependency using a dynamically constructed SQL query:

// Precondition: Cache is already connected
// Creating a connection string to get connected with the database
string connectionString = "your_connection_string_here";

// Getting products from the database
List<Product> products = FetchProductFromDB();

foreach (Product product in products)
{
    string productKey = $"Product: {product.ProductID}";

    // Creating an SQL dependency on the UnitPrice of product. Whenever the UnitPrice changes, the product is removed from the cache
    string query = $"SELECT UnitPrice FROM dbo.Products WHERE ProductID = {product.ProductID}";

    // Creating dependency
    SqlCacheDependency dependency = new SqlCacheDependency(connectionString, query);

    CacheItem productItem = new CacheItem(product);

    // Adding Dependency to product item
    productItem.Dependency = dependency;

    // Adding CacheItem in cache
    _cache.Add(productKey, productItem);
}

Using Stored Procedures

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

// Precondition: Cache is already connected

// Creating connection string to get connected with the database
string connectionString = "your_connection_string_here";
string spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID";

// Getting products from the database
List<Product> products = FetchProductFromDB();

// Creating dictionary of CacheItems
Dictionary<string, CacheItem> cacheItems = new Dictionary<string, CacheItem>();

foreach (Product product in products)
{
    string productKey = $"Product: {product.ProductID}";

    // Creating Param to be passed in stored procedure dictionary
    SqlCmdParams paramProductID = new SqlCmdParams
    {
        Type = CmdParamsType.Int,
        Value = product.ProductID
    };

    // Creating stored procedure params
    Dictionary<string, SqlCmdParams> parameters = new Dictionary<string, SqlCmdParams>();
    parameters.Add("@ProductID", paramProductID);

    CacheItem productItem = new CacheItem(product);

    // Creating an SQL dependency on the UnitPrice of the product. Whenever the UnitPrice changes, the product is removed from the cache
    SqlCacheDependency dependency = new SqlCacheDependency(connectionString, spGetUnitPriceByProductID, SqlCommandType.StoredProcedure, parameters);

    // Adding Dependency to product item
    productItem.Dependency = dependency;

    cacheItems.Add(productKey, productItem);
}
// Adding CacheItems in cache
_cache.AddBulk(cacheItems);

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:

• Inline Queries
• 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 the database update.

Using Inline Queries

Here is how you can use Oracle dependency in your .NET code using inline queries:

// Precondition: Cache is already connected

// Creating a connection string to get connected with the database
string connectionString = "your_connection_string_here";

// Getting products from the database
List<Product> products = FetchProductFromDB();

foreach (Product product in products)
{
      string productKey = $"Product: {product.ProductID}";

      // Creating an Oracle dependency on the UnitPrice of the product. Whenever the UnitPrice changes, the product is removed from the cache
      string query = $"SELECT ROWID, UnitPrice FROM Products WHERE ProductID = {product.ProductID}";

      OracleCacheDependency dependency = new OracleCacheDependency(connectionString, query);

      CacheItem productItem = new CacheItem(product);

      // Adding Dependency to product item
      productItem.Dependency = dependency;

      // Adding CacheItem in cache
      cache.Add(productKey, productItem);
}

Using Stored Procedures

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

// Precondition: Cache is already connected

// Creating a connection string to get connected with the database
string connectionString = "your_connection_string_here";

string spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID";

// Getting products from the database
List<Product> products = FetchProductFromDB();

foreach (Product product in products)
{
      string productKey = $"Product: {product.ProductID}";
      // Creating Param to be passed in stored procedure dictionary
      OracleCmdParams paramProductID = new OracleCmdParams
      {
            Type = OracleCmdParamsType.Int32,
            Value = product.ProductID
      };
      // Creating stored procedure params
      Dictionary<string, OracleCmdParams> parameters = new Dictionary<string, OracleCmdParams>();
      parameters.Add("@ProductID", paramProductID);

      CacheItem productItem = new CacheItem(product);

      // Creating an Oracle dependency on the UnitPrice of the product. Whenever the UnitPrice changes, the product is removed from the cache
      OracleCacheDependency dependency = new OracleCacheDependency(connectionString, spGetUnitPriceByProductID, OracleCommandType.StoredProcedure, parameters);

      // Adding Dependency to product item
      productItem.Dependency = dependency;

      // Adding CacheItem in cache
      cache.Add(productKey, productItem);
}

Sync Cache with MongoDB

NCache offers flexibility and control over cache invalidation through custom notification dependencies. For MongoDB, you can synchronize your cache in real-time with your MongoDB database using Change Streams.

This detects inserts, updates, deletes, and replacements on your MongoDB collections. NCache can immediately remove or update the corresponding cache entries when a change occurs, ensuring your cache remains fresh and consistent with the database.

Here is an example of how to implement this synchronization using MongoDB's Change Streams in your .NET application:

var pipeline = new EmptyPipelineDefinition<ChangeStreamDocument<Customer>>()
    .Match("{ operationType: { $in: ['insert', 'update', 'replace', 'delete'] } }");
 
var cursor = collection.Watch(pipeline);
await cursor.ForEachAsync(change =>
{
    string cacheKey = $"Customer:CustomerID:{change.FullDocument.Id}";
    cache.Remove(cacheKey);
});

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

        // Connect to the cache
        ICache cache = CacheManager.GetCache(cacheName);

        // Remove specified item
        cache.Remove(key);

        // Dispose the cache
        cache.Dispose();
    }

Auto Reload with Read-Through Handler

The default behaviour 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 caching 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. MongoDB

The following code snippet shows how data can be auto-reloaded in bulk into NCache from the database using Read-through. You can also find more details here.

String[] keys = { "Product:1001", "Product:1002",
"Product:1003","Product:1004"};

// Specify the readThruOptions for Read-through operations
var readThruOptions = new ReadThruOptions();
readThruOptions.Mode = ReadMode.ReadThru;

// Retrieve the dictionary of Products with corresponding products
IDictionary<string, Product> retrievedItems = cache.GetBulk<Product>(keys, readThruOptions);