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

Set up Environment for using SQL Dependency.
To learn about the standard prerequisites required to work with all NCache client-side features, please refer to the given page on Client-Side API Prerequisites.
For API details, refer to: ICache, CacheItem, Dependency, SqlCacheDependency.
Make sure that broker is enabled.
Make sure that the permissions are enabled in order to use NCache SQL cache dependency.

Install either of the following NuGet packages in your .NET client application:
- Enterprise: Install-Package Alachisoft.NCache.SDK -Version 4.9.1.0
- Professional: Install-Package Alachisoft.NCache.Professional.SDK -Version 4.9.1.0
Create a new Console Application.
Make sure that the data being added is serializable.
Add NCache References by locating %NCHOME%\NCache\bin\assembly\4.0 and adding Alachisoft.NCache.Web and Alachisoft.NCache.Runtime as appropriate.
Include the Alachisoft.NCache.Runtime.Dependencies namespace in your application.
To learn more about the NCache Legacy API, please download the NCache 4.9 documents available as a .zip file on the Alachisoft Website.

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

// Precondition: Cache is already connected

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

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


for (Product product : products) {
    String productKey = "Product:" + product.getProductID();

    // Creating an SQL dependency on the UnitPrice of the product. Whenever the UnitPrice changes, the product is removed from the cache
    String query = "SELECT UnitPrice FROM dbo.Products WHERE ProductID = " + product.getProductID();

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

    CacheItem productItem = new CacheItem(product);

    // Adding Dependency to the product item
    productItem.setDependency(dependency);

    // Adding CacheItem to the cache
    cache.add(productKey, productItem);

    System.out.println("Product added to cache: " + productKey);
}

# Precondition: Cache is already connected

# Creating a connection string to establish connection with the database
conn_string = "your_connection_string_here"

query = "SELECT CustomerID, Address, City FROM dbo.Customers WHERE CustomerID = ?"
query_command = ncache.QueryCommand(query)
query_command.set_parameters({"CustomerID": "ALFKI"})

# Creating SQL dependency
sql_dependency = ncache.SqlCacheDependency(conn_string, query)

# Get orders against Customer ID
orders = fetch_orders_by_customer_id("ALFKI")

for order in orders:
    # Generate a unique key for this order
    key = "Order:" + order.get_order_id()

    # Create a new cache item and add sql dependency to it
    cache_item = ncache.CacheItem(order)
    cache_item.set_dependency(sql_dependency)

    # Add cache item in the cache with sql dependency
    cache.insert(key, cache_item)

    # For successful addition of item with SQL Dependency
    # Update the record in the database and check if key is present

// Precondition: Cache is already connected

// This is an async method
// Creating a connection string to establish connection with the database
var connection = new ActiveXObject("adodb.connection");
let connString = "your_connection_string_here";

let query = "SELECT CustomerID, Address, City FROM dbo.Customers WHERE CustomerID = ?";
let queryCommand = new QueryCommand(query);
queryCommand.getParameters().put("CustomerID", this.customerId);
connection.Open(query, connString);

// Creating SQL dependency
let sqlDependency = new ncache.SqlCacheDependency(connString, query);

// Get orders against customerId
let orders = await this.fetchOrdersByCustomerId(this.customerId);

for (let order in orders)
{
    // Generate a unique key for this order
    let key = "Order:" + order.OrderId;

    // Create a new cache item and add sql dependency to it
    let cacheItem = new ncache.CacheItem(order);
    cacheItem.setDependency(sqlDependency);

    // Add cache item in the cache with sql dependency
    await this.cache.insert(key, cacheItem);

    // For successful addition of item with SQL Dependency
    // Update the record in the database and check if key is present
}
connection.close();

// Using NCache Enterprise 4.9.1
// 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 sqlDependency = new SqlCacheDependency(connectionString, query);

    // Adding products with SqlCacheDependency
    cache.Insert(productKey, product, sqlDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal);

}

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

// Precondition: Cache is already connected

String connectionString = "your_connection_string_here";
String spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID";

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

// Create a map to store CacheItems
Map<String, CacheItem> cacheItems = new HashMap<>();

for (Product product : products) {
    String productKey = "Product:" + product.getProductID();

    // Creating Param to be passed in the stored procedure dictionary
    SqlCmdParams paramProductID = new SqlCmdParams();
    paramProductID.setCmdParamsType(CmdParamsType.Int);
    paramProductID.setValue(product.getProductID());

    // Creating stored procedure params
    Map<String, SqlCmdParams> parameters  = new HashMap<>();
    parameters.put("@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 the product item
    productItem.setDependency(dependency);
    cacheItems.put(productKey, productItem);
    System.out.println("Added product to cache items: " + productKey);
}

// Adding CacheItems in cache using addBulk method
cache.addBulk(cacheItems);
System.out.println("Products added to cache.");

# Precondition: Cache is already connected

# Creating a connection string to establish connection with the database
conn_string = "your_connection_string_here"

# Name of the stored procedure item is dependent upon
stored_proc_name = "GetCustomerByID"

# Specify the customerID passed as parameters
sql_cmd_params = {"CustomerID": "ALFKI"}

# Create sql dependency
# In case the stored procedure has no parameters, pass null as the SqlCmdParams value
sql_cache_dependency = ncache.SqlCacheDependency(conn_string, stored_proc_name, ncache.SqlCommandType.STORED_PROCEDURE, sql_cmd_params)

# Get orders from database against customerId
orders = fetch_orders_by_customer_id("ALFKI")

for order in orders:
    # Generate a unique cache key for this order
    key = "Order:" + order.get_order_id()

    # Create a new cache item and add sql dependency to it
    item = ncache.CacheItem(order)
    item.set_dependency(sql_cache_dependency)

    # Add cache item in the cache with sql dependency
    cache.insert(key, item)

# For successful addition of item with SQL Dependency
# Update the record in the database and check if key is present

// Precondition: Cache is already connected

// This is an async method
// Creating a connection string to establish connection with the database
var connection = new ActiveXObject("adodb.connection");
let connString = "your_connection_string_here";
connection.Open(connString);

// Name of the stored procedure item is dependent upon
let storedProcName = "GetCustomerByID";

// Specify the customerID passed as parameters
let sqlCmdParams = new Map();
sqlCmdParams.set("CustomerID", this.customerId);

// Create sql dependency
// In case the stored procedure has no parameters, pass null as the SqlCmdParams value
let sqlCacheDependency = new ncache.SqlCacheDependency(connString, storedProcName,
    ncache.SqlCommandType.StoredProcedure, sqlCmdParams);

// Get orders from database against customerId
let orders = await this.fetchOrdersByCustomerId(this.customerId);

for (let order in orders)
{
    // Generate a unique cache key for this order
    let key = "Order:" + order.OrderId;

    // Create a new cache item and add sql dependency to it
    let item = new ncache.CacheItem(order);
    item.setDependency(sqlCacheDependency);

    // Add cache item in the cache with sql dependency
    this.cache.insert(key, item);

    // For successful addition of item with SQL Dependency
    // Update the record in the database and check if key is present
}
connection.close();

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.