SQL Dependency [Deprecated]

SQL Dependency is a deprecated mechanism in NCache used to synchronize distributed cache data with Microsoft SQL Server (2005+). Essentially, by utilizing SQL Server's Service Broker event notifications, NCache automatically invalidates and removes any stale cache items whenever the corresponding entities in the database change. This ensures data integrity by keeping the cache fresh. You can refer to the [Cache Startup Loader and Refresher] to maintain data integrity and benefit from a lack population related performance costs.

Environment Setup and Prerequisites for SQL Dependency

Implementing SQL Query-Based Dependency

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.

Implementing Stored Procedure-Based Dependency

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.