Cache Data Dependency on SQL Server

Note

This feature is only available in NCache Enterprise Edition.

There are various mechanisms in NCache to keep the data synced across the cache. There may be a scenario where the user wants to keep the data synced 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

For further details, please refer to the Setup SQL Server Environment section in Administrator's Guide.

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.

Add Data with SQL Dependency through API

To add data in the cache using SQL Dependency NCache provides the SqlCacheDependency class. This adds items to the cache with SQL Dependency.

The following example adds SQL Dependency to an item and then adds the item to the cache using the Insert method. The Insert method adds a new item with dependency, and if this data already exists in the cache, it overwrites it.

try
{
    // 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);
    }
}

catch (OperationFailedException ex)
{
    // Make sure that the query is valid
    // Exception can occur due to:
    // Connection Failures
    // Operation Timeout
    // Operation performed during state transfer
}
catch (Exception ex)
{
    // Any generic exception like ArgumentNullException or ArgumentException
    // Exception may occur due to incorrect connection string or broker not enabled
}

try
{
    // Creating a connection string to establish connection with the database
    String connString = "your_connection_string_here";
    Connection connectionString = DriverManager.getConnectionconnStri(ng);

    // Creating the query which selects the data on which cache data is dependent
    String query = "SELECT CustomerID, Address, City FROM dbo.Customers WHERE CustomerID = ?";
    QueryCommand queryCommand = new QueryCommand(query);
    queryCommand.getParameters().put("CustomerID", customerId);

    // Creating SQL dependency
    SqlCacheDependency sqlDependency = new SqlCacheDependency(String.valueOf(connString), query);

    // Get orders against customerId
    Order[] orders = fetchOrdersByCustomerId(customerId);

    for (Order order : orders) {
        // Generate a unique key for this order
        String key = "Order:" + order.orderId;

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

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

        // For successful addition of item with SQL Dependency
        // Update the record in the database and check if key is present
    }
}
catch (OperationFailedException ex)
{
        // Exception can occur due to:
        // Connection Failures
        // Operation Timeout
        // Operation performed during state transfer
}
catch (Exception ex)
{
    // Any generic exception like IllegalArgumentException or NullPointerException
}

try {
    // Creating a connection string to establish connection with the database

    val connString = "your_connection_string_here"
    val customerId = "ALFKI"

    // Creating the query which selects the data on which cache data is dependent
    val query = "SELECT CustomerID, Address, City FROM dbo.Customers WHERE CustomerID = ?"
    val queryCommand = new QueryCommand(query)
    queryCommand.setParameters(Map("CustomerID" -> customerId))

    // Creating SQL dependency
    val sqlDependency = new SqlCacheDependency(connString, query)

    // Get orders against customerId
    val orders = fetchOrdersByCustomerId(customerId)

    for (order <- orders) {
      // Generate a unique key for this order
      val key = "Order:" + order.getOrderId

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

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

      // For successful addition of item with SQL Dependency
      // Update the record in the database and check if key is present
    }
}
catch {
    case exception: Exception => {
      // Handle any errors
    }
}

// This is an async method
try
{
    // 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();
}
catch (error)
{
    // Handle any errors
}

try:
    # 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
except Exception as exp:
    # Handle errors

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 a 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 adds an item to the cache with SQL Dependency through the stored procedure using the Insert method. The Insert method adds a new item with dependency, and if this data already exists in the cache, it overwrites its properties.

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

// Creating a connection string to establish connection with the database
String connString = "";
Connection connectionString = DriverManager.getConnection(connString);

// Name of the stored procedure item is dependent un
String storedProcName = "GetCustomerByID";

// Specify the customerID passed as parameters
Map<String, SqlCmdParams> sqlCmdParams = new HashMap;
sqlCmdParams.put("CustomerID", customerId);

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

// Get orders from database against customerId
Order[] orders = fetchOrdersByCustomerId(customerId);

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

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

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

// Creating a connection string to establish connection with the database
val connString = "YourConnString"
val customerId = "ALFKI"

// Name of the stored procedure item is dependent un
val storedProcName = "GetCustomerByID"

// Specify the customerID passed as parameters
var param = SqlCmdParams()
param.setValue(customerId)

var sqlCmdParams: Map[String, SqlCmdParams] = Map("CustomerID" -> param)

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

// Get orders from database against customerId
var orders = fetchOrdersByCustomerId(customerId)

for (order <- orders)
{
    // Generate a unique cache key for this order
    var key = "Order:" + order.getOrderId

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

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

// 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 un
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();

# 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

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 checking “Use custom SQL Notification Mode” checkbox in Options tab in NCache Manager.
You can also create the required SQL Service and Queue using script NCacheServiceQueue.sql located at %NCHOME%/bin/resources.

Additional Resources

NCache provides sample application for SQL dependency on GitHub.

Cache Data Dependency on SQL Server

Note

Prerequisites

Add Data with SQL Dependency through API

Note

Add Data with SQL Dependency using Stored Procedure

Important

Additional Resources

See Also