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 that on every update in the database the cache needs to be notified. This way the data in the cache remains fresh and operations are performed on the updated data set.
NCache provides feature of SQL dependency for the purpose of synchronizing 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 data is updated in the database by any application, 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. In order to create SQL Dependency:
In order to get complete detail please refer to Setup SQL Server Environment section in Administrator's Guide.
Prerequisites
- Set up Environment for using SQL Dependency.
- Install the following NuGet package in your application:
- Include the following namespace in your application:
Alachisoft.NCache.Client
Alachisoft.NCache.Runtime.Dependencies
Alachisoft.NCache.Runtime.Exceptions
- Cache must be running.
- The application must be connected to cache before performing the operation.
- Make sure that the data being added is serializable.
- For API details, refer to: ICache, CacheItem, Dependency, SqlCacheDependency.
- To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
To handle any unseen exceptions, refer to the Troubleshooting section.
Make sure that broker is enabled.
- Make sure that the permissions are enabled in order to use NCache SQL cache dependency.
- Add the following Maven dependencies in your
pom.xml
file:
<dependency>
<groupId>com.alachisoft.ncache</groupId>
<artifactId>ncache-client</artifactId>
<version>x.x.x</version>
</dependency>
- Install the NCache Python client by executing the following command:
# Enterprise Client
pip install ncache-client
- Import the NCache module in your application.
- Cache must be running.
- To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
- To handle any unseen exceptions, refer to the Troubleshooting section.
- Add the following Maven dependencies in your
pom.xml
file:
<dependency>
<groupId>com.alachisoft.ncache</groupId>
<artifactId>ncache-scala-client</artifactId>
<version>x.x.x</version>
</dependency>
- Import the following packages in your application:
import com.alachisoft.ncache.scala.client.*;
import com.alachisoft.ncache.scala.runtime.dependencies.*;
- Cache must be running.
- The application must be connected to cache before performing the operation.
- Make sure that the data being added is serializable.
- To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
- To handle any unseen exceptions, refer to the Troubleshooting section.
Add Data with SQL Dependency through API
In order to add data in cache using SQL Dependency NCache provides with SqlCacheDependency
method. Using this method an item is added in the cache with SQL Dependency.
Following example adds SQL Dependency to an item and then add the item to the cache using the Insert method. The Insert
method adds a new item with dependency and if the item already exists in the cache it overwrites it.
try
{
// Creating a connection string to establish connection with the database
// Connection String is in AppSettings in App.config
string connectionString = ConfigurationManager.AppSettings["connectionstring"];
// Creating the query which selects the data on which the cache data is dependent
string query = "SELECT CustomerID, Address, City FROM dbo.Customers WHERE CustomerID = @CustomerID;";
var param = new SqlCmdParams();
param.Type = (CmdParamsType.Char);
param.Value = "ALFKI";
Dictionary<string, SqlCmdParams> sqlCmdParam = new Dictionary<string, SqlCmdParams>();
sqlCmdParam.Add("@CustomerID", param);
// Creating SQL Dependency
var sqlDependency = new SqlCacheDependency(connString, query, SqlCommandType.Text, sqlCmdParam);
// Creating SQL Dependency
SqlCacheDependency sqlDependency = new SqlCacheDependency(connectionString, query);
// Get orders against given customer ID
Order[] orders = FetchOrdersByCustomerID(param.Value);
foreach (var order in orders)
{
// Generate a unique cache key for this order
string key = $"Order:{order.OrderID}";
// Create a new cacheitem and add sql dependency to it
CacheItem item = new CacheItem(order);
item.Dependency = sqlDependency;
//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
}
}
catch (OperationFailedException ex)
{
if (ex.ErrorCode == 95000)
{
// Make sure that the query is valid
}
else
{
// 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 = "";
Connection connectionString = DriverManager.getConnection(connString);
// 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)
{
if (ex.getErrorCode() == 95000)
{
// Make sure that the query is valid
}
else
{
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
}
catch (Exception ex)
{
// Any generic exception like IllegalArgumentException or NullPointerException
}
// 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
try {
// Creating a connection string to establish connection with the database
val connString = "YourConnString"
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
}
}
Recommendation: 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 provides you with an ability to provide SQL Dependency on an item using stored procedure. You can also specify the parameters to be passed along with the stored procedure using the SqlCacheDependency
method.
The following example adds item to the cache with SQL Dependency through stored procedure using the Insert method. The Insert
method adds a new item with dependency and if the item already exists in the cache it overwrites its properties.
try
{
// Create a connection string to establish connection with the database
// Connection String is in <AppSettings> in App.config
string connectionString = ConfigurationManager.AppSettings["connectionstring"];
// The name of the stored procedure the item is dependent on
string storedProcName = "GetCustomerByID";
// Specify the CustomerIDs passed as parameters
var param = new SqlCmdParams();
param.Type = CmdParamsType.NVarChar;
param.Value = "ALFKI";
Dictionary<string, SqlCmdParams> sqlCmdParams = new Dictionary<string, SqlCmdParams>();
sqlCmdParams.Add("@CustomerID", param);
// Create SQL Dependency
// In case the stored procedure has no parameters pass null as the SqlCmdParams value
SqlCacheDependency sqlDependency = new SqlCacheDependency(connectionString, storedProcName, SqlCommandType.StoredProcedure, sqlCmdParams);
// Get orders from database against given customer ID
Order[] orders = FetchOrdersByCustomerID(param.Value);
foreach (var order in orders)
{
// Generate a unique cache key for this order
string key = $"Order:{order.OrderID}";
// Create a new cacheitem and add sql dependency to it
CacheItem item = new CacheItem(order);
item.Dependency = sqlDependency;
//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
}
}
catch (OperationFailedException ex)
{
// 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
// Or in case the stored procedure is not available
}
try
{
// 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
}
}
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
}
// 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";
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();
}
catch (error)
{
// Handle any errors
}
try:
# 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
except Exception as exp:
# Handle errors
try {
// 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
}
}
catch {
case exception: Exception => {
// Handle any errors
}
}
Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
Important
In an environment where the user has more restricted access on 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 Web Manager.
You can also create 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.
See Also
Cache Data Dependency on Oracle Database
Cache Data Dependency on OleDB
CLR Procedures in SQL Server with Cache
Locking Data For Concurrency Control
Cache Data Dependency on External Source