SQL Dependency [Deprecated]

There are various mechanisms in NCache to keep the data synchronized across the cache. There may be a scenario where the user wants to keep the data synchronized with the database, so every database update notifies the cache. This way data in the cache remains fresh, and operations occur on the updated data set.

NCache provides the feature of SQL dependency to synchronize cache with Microsoft SQL Server 2005 onwards. An item is added with SqlCacheDependency (provided by NCache), specifying an SQL statement representing a query result set in the database. NCache then establishes a link with the database against the result set. If an application updates, the SQL Server fires event notifications, which NCache catches and removes the corresponding item from the distributed cache.

Before using SQL Dependency, set up SQL Server Environment. To create SQL Dependency:

  • Enable Broker Service
  • Enable Database Permissions

Prerequisites

Add Data with SQL Server Dependency through API

To add data in the cache using the SQL Dependency, NCache provides the SqlCacheDependency class. This adds items to the cache with SQL Dependency. In the following example, each item is assigned an SQL dependency and then added to the cache using the Add method.

// Precondition: Cache is already connected

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

// Getting products from 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);
}
Note

To ensure the operation is fail-safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Add Data with SQL Dependency using Stored Procedure

NCache lets you provide an SQL Dependency on an item using a stored procedure. You can also specify the parameters to be passed along with this procedure using the SqlCacheDependency method. The following example uses the AddBulk method to insert multiple items at once, each with its own SQL dependency.

// Precondition: Cache is already connected

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

// Getting products from 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 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);
Important
  • In an environment where the user has more restricted access to the database, users are supposed to create SQL Service and Queue per NCache process by enabling the Custom SQL Notification Mode in the Options tab in the NCache Management Center.
  • You can also create the required SQL Service and Queue using the script NCacheServiceQueue.sql located at %NCHOME%\bin\resources.

Additional Resources

NCache provides a sample application for SQL dependency on GitHub.

See Also

.NET: Alachisoft.NCache.Runtime.Dependencies namespace.
Java: com.alachisoft.ncache.runtime.dependencies namespace.
Python: ncache.runtime.dependencies class.
Node.js: SqlCacheDependency class.