Sync Cache with SQL Server

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:

Enable Broker Service
Enable Database Permissions

In order to get complete detail please refer to Setup SQL Server Environment section in Administrator's Guide.

Pre-Requisites for Using SQL Dependency

Set up Environment for using SQL Dependency.
Include the following namespace in your application:
- Alachisoft.NCache.Web.Caching
- Alachisoft.NCache.Runtime
- Alachisoft.NCache.Runtime.Dependencies
The application must be connected to cache before performing the operation.
Cache must be running.
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.
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

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 key is dependent
    string query = $"SELECT ProductName FROM dbo.Products WHERE ProductID = 1001;";   

    // Get product from database against given product ID
    Product product = FetchProductFromDB(connectionString, query);

    // Generate a unique cache key for this product
    string key = $"Product:{product.ProductID}";

    // Creating SQL Dependency
    SqlCacheDependency sqlDependency = new SqlCacheDependency(connectionString, query);

    // Create a new cacheitem and add sql dependency to it
    CacheItem item = new CacheItem(product);
    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
    // This can be done by using
    // Cache.Contains()
    // Count
}
catch (OperationFailedException ex)
{                
    if (ex.Message.Contains("The SQL Server Service Broker for the current database is not enabled"))
    {
        Console.WriteLine(ex.InnerException);
        // Enable the broker service as discussed above
    }
    else if (ex.Message.Contains("A network-related or instance-specific error occurred while establishing a connection to SQL Server."))
    {
        // Make sure that the connection string is valid
    }
    else if (ex.Message.Contains("Incorrect syntax near the keyword"))
    {
        // Make sure that the query is valid
    }
    else
    {
        // Exception can occur due to:
        // Connection Failures
        // Operation performed during state transfer
        // Operation Timeout
    }
}
catch (Exception ex)
{
    // Any generic exception like ArgumentNullException or ArgumentException
}

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.

Note

The stored procedure used in this example is provided by SQL Server by default.

try
{
    // Get product from database against given product ID
    Product product = FetchProductFromDB(1001);

    // Generate a unique cache key for this product
    string key = $"Product:{product.ProductID}";

    // 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
    // The stored procedure used here is provided by SQL Server by default
    string storedProcName = "GetCustomerByProduct";

    // Create SQL Dependency
    // Null is passed as CmdParams
    SqlCacheDependency sqlDependency = new SqlCacheDependency(connectionString, storedProcName, SqlCommandType.StoredProcedure, null);

    // Create a new cacheitem and add sql dependency to it
    CacheItem item = new CacheItem(product);
    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
    // This can be done by using
    // cache.Contains()
    // cache.Count
}
catch (OperationFailedException ex)
{
    if (ex.Message.Contains("The SQL Server Service Broker for the current database is not enabled"))
    {
        // Enable the broker service as discussed above
    }
    else if (ex.Message.Contains("A network-related or instance-specific error occurred while establishing a connection to SQL Server."))
    {
        // Make sure that the connection string is valid
    }
    else if (ex.Message.Contains("Could not find stored procedure"))
    {
        // Make sure that the name of the stored procedure is valid
    }
    else
    {
        // Exception can occur due to:
        // Connection Failures
        // Operation performed during state transfer
        // Operation Timeout
    }
}
catch (Exception ex)
{
    // Any generic exception like ArgumentNullException or ArgumentException
}

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 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 at:

GitHub
Shipped with NCache: %NCHOME%\samples\dotnet\Dependencies\SQLDependency