• Webinars
  • Docs
  • Download
  • Blogs
  • Contact Us
Show / Hide Table of Contents
  • Programmer's Guide
  • Client Side API Programming
    • Setting Up Development Environment
    • Basic Cache Operations
      • Initialize Cache
      • Add Data to Cache
      • Update Data in Cache
      • Fetch Data From Cache
      • Remove Data From Cache
      • Dispose Cache
    • Bulk Operations
      • Adding Collection to Cache
      • Updating Collection in Cache
      • Retrieving Collection from Cache
      • Removing Collection from Cache
      • Deleting Collection from Cache
    • Asynchronous Operations
      • Using Asynchronous Operations
      • Using Asynchronous Operations with Callback Methods
    • Groups and Subgroups
      • Adding/Updating Data Group in Cache
      • Retrieving Data Group from Cache
      • Removing Data Group from Cache
    • Tagging Data in NCache
      • Creating Tags
      • Adding Items with Tags
      • Retrieving Previously Tagged Data
      • Removing Tagged Items from Cache
    • Named Tags
    • Data Expiration Strategies
      • Using Absolute Expiration
      • Using Sliding Expiration
    • Cache Dependencies
      • Key Dependency
      • File Dependency
      • Notification based Dependencies
        • Database Dependency using SQL Server
        • Database Dependency using Oracle
      • Polling Based Dependency
      • Custom Data Source Dependency
      • Multiple Cache Sync Dependency
      • Aggregate Dependency
      • Add Dependency to Existing Item
      • Using CLR Procedures to Call NCache
    • Locking Data in NCache
      • Locking Items in Cache (Pessimistic Locking)
      • Locking Items with Cache Item Versioning (Optimistic Locking)
    • SQL Reference for NCache
      • SQL Syntax
      • Querying Samples for Operators
      • Querying Data in NCache
      • NCache Language Integrated Query (LINQ)
        • Using LINQ in NCache
        • Configuring LINQPad for NCache
        • Querying NCache Data in LINQPad
    • Event Notifications
      • Cache Level Event Notifications
      • Item Level Event Notifications
      • Custom Event Notifications
    • Publish/Subscribe (Pub/Sub) in NCache
      • Pub/Sub Topics
      • Managing Topics
      • Pub/Sub Messages
        • Message Behavior and Properties
        • Creating a Message
      • Publish Messages to Topic
      • Subscribe for Topic Messages
      • Monitoring Pub/Sub Topics
    • Continuous Query
    • Using Streams in NCache
      • Opening with Stream Modes
      • Adding and Updating Data with Streams
      • Retrieving Data from Streams
      • Closing a Stream
    • Security and Encryption
      • NCache Security
      • NCache Data Encryption
    • Data Compression
    • NCache Management API
  • Server Side API Programming
    • Cache Startup Loader
      • Components of Cache Startup Loader
      • Sample Implementation of ICacheLoader on Single Node
      • Sample Implementation of ICacheLoader with Distribution Hints
    • Data Source Providers (Backing Source)
      • Read-Through Caching
        • Configure Read-Through Provider
        • Using Read-Through with Cache Operations
      • Write-Through Caching
        • Configuring Write-Through Provider
        • Using Write-Through with Basic Operations
        • Using Write-Behind with Basic Operations
        • Using Write-Behind with Bulk Operations
        • Using Write-Behind with Async Operations
        • Monitor Write-Through Counters
    • Custom Dependency
      • Sample Implementation of Custom Dependency
      • Sample Usage of Custom Dependency
    • WAN Replication through Bridge
      • Bridge Configurations
      • Implementing Bridge Conflict Resolver
    • Entry Processor
      • Sample Implementation of IEntryProcessor Interface
      • Sample Usage of EntryProcessor
    • MapReduce
      • Sample Implementation of MapReduce Interfaces
      • Sample Usage of MapReduce
    • Aggregator
      • Sample Implementation of IValueExtractor Interface
      • Sample Implementation of IAggregator Interface
      • Sample Usage of Aggregator
    • Dynamic Compact Serialization
  • Client Side ASP.NET Features
    • ASP.NET
      • ASP.NET Session State Provider for NCache
      • Multi-Region ASP.NET Session State Provider for NCache
    • ASP.NET Core
      • Session Storage in ASP.NET Core
        • Configure NCache ASP.NET Core Session Provider
        • Configure ASP.NET Core Sessions with NCache IDistributedCache Provider
      • Multi-Region ASP.NET Core Session Provider for NCache
      • Object Caching in ASP.NET Core
    • ASP.NET SignalR
      • Using NCache Extension for SignalR
    • View State Caching
      • Configuring and Using Content Optimization
      • Group View State with Sessions
      • Limit View State Caching
      • Perform Page Level Grouping for View State
    • ASP.NET Output Cache
      • Configure ASP.NET Output Caching
      • Using ASP.NET Output Cache with Custom Hooks
  • Client Side Third Party Integrations
    • Migrating AppFabric to NCache
      • AppFabric API vs. NCache API
    • NHibernate
      • NCache as NHibernate Second Level Cache
      • Using NHibernate Query Caching
      • Configuring Database Synchronization with NHibernate
    • Entity Framework Caching Integration
      • NCache as Entity Framework Second Level Cache
      • Entity Framework Caching Config File
    • Entity Framework Core Caching
      • Installing NCache Entity Framework Core Provider
      • Configuring NCache Entity Framework Core Provider
      • Using NCache Entity Framework Core Provider
        • Caching Options for EF Core Provider
        • LINQ APIs for EF Core Provider
        • Cache Only APIs for EF Core Provider
        • Query Deferred APIs for EF Core Provider
      • Logging in NCache Entity Framework Core Provider
    • Memcached
      • NCache Memcached Gateway Approach
      • Memcached Client Plugin for .NET
    • Debug NCache Providers in Visual Studio
    • NCache for Visual Studio Extension

Database Dependency Using SQL Server

NCache provides feature of SQL dependency for the purpose of synchronizing cache with Microsoft SQL Server 2005/2008. An item is added with SqlDependency (provided by .NET framework), specifying an SQL statement representing a query result set in the database. NCache then establishes a link with the database against these rows. If data is updated in the database by any application, SQL Server fires a .NET event notification which NCache catches and removes the corresponding item from the distributed cache.

1. Configuring Notification on SQL Server

NCache tracks changes in database using notifications received from SQL server about changes in database. Internally NCache registers for these data change notification and receives notification from database in case of any change in the registered query result set. On the basis of these received notification, cache invalidates the corresponding data and removes it from cache.

Notifications must be enabled in SQL server database for NCache. Unlike time based expirations where data is invalidated by cache clean up thread on a configurable interval, NCache removes the corresponding data as soon it receives notification from the database.

For this purpose, enable the service broker in SQL Server 2005 or above before NCache can use SqlCacheDependency. This allows SQL Server 2005 to send event notifications to NCache to let it know that data has changed in the database.

ALTER DATABASE Northwind SET ENABLE_BROKER;
GO

To verify that the broker is running, properties in the database server can be checked using SQL Management studio or run the following query:

SELECT is_broker_enabled FROM sys.databases WHERE name = '<dbname>'`

If the result is “1”, it means broker has been enabled.

For further details, refer to MSDN article on Enabling Event Notifications for SQL Server.

2. Database Permissions

The following permissions need to be enabled in order to use NCache SQL cache dependency if the user does not have database permissions. Database permissions are defined for two different modes: Default mode and Custom mode.

Default Mode

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <database_principal>
GRANT CREATE QUEUE TO <database_principal>
GRANT CREATE SERVICE TO <database_principal>
GRANT CREATE PROCEDURE TO <database_principal>

Custom Mode

CREATE QUEUE "NCacheSQLQueue-[IP-Address]";
CREATE SERVICE "NCacheSQLService-[IP-Address]"
ON
QUEUE."NCacheSQLQueue-[IP-Address]"([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO[User Name];
GRANT RECEIVE ON :: "NCacheSQLQueue-[IP-Address]"TO[User Name];
GRANT RECEIVE ON :: QueryNotificationErrorsQueue TO[User Name];
GRANT SEND ON SERVICE :: "NCacheSQLService-[IP-Address]"to[User Name];
Note
  • Replace [IP-Address] with node IP address and [User Name] with database user name.
Important
  • In a clustered environment, 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.

3. Adding Data with SQL Dependency

To add data with SQL dependency, create SqlCacheDependency object, and use Add/Insert API to add item with SqlCacheDependency.

To utilize the API, include the following namespace in your application: Alachisoft.NCache.Runtime.Dependencies.


Product product = new Product();
product.ProductID = 1001;
product.ProductName = "Chai";

string key = "Product:" + product.ProductID;

try{
      //Creating SqlCacheDependency
      string connectionString = "Data Source=localhost;Initial  Catalog=Northwind;Integrated Security=SSPI;";
      string query = "SELECT ProductID FROM dbo.Products WHERE ProductID = 1001";

      SqlCacheDependency sqlDependency = new SqlCacheDependency(connectionString, query);

      //Adding cache item "Product:1001" with SqlCacheDependency
      cache.Insert(key, product, sqlDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal);

      //Modify Product record in database while program waits...
      Thread.Sleep(5000);

      //... and then check for its existence
      Object item = cache.Get(key);
      if (item == null){
          // item removed successfully
      }
      else{
      // item not removed successfully
      }
}
catch (OperationFailedException e){
      //handle exception
}

4. Write Trigger to Call Stored Procedure

A database trigger needs to be written that will call stored procedure created in the previous steps whenever an update or delete is performed on database table. Following is a sample script to create trigger on Product table in Northwind database:

CREATE TRIGGER [dbo].[myTrigger]
ON [dbo].[Products]
FOR DELETE,UPDATE
AS
DECLARE @cacheName AS NVARCHAR(4000)
DECLARE @key AS VARCHAR(4000)

SELECT @key=Cast((Deleted.ProductID)asnVarChar)+':dbo.Products', @cacheName='mycache' FROM Deleted

EXEC dbo.RemoveOnUpdate@cacheName, @key

5. Adding Data in Cache

Following is a sample code that adds an item in cache and updates it in database to verify that item has been removed from cache:

To utilize the API, include the following namespace in your application: Alachisoft.NCache.Runtime.Dependencies.

  //Create object to be added with dependency
  Product product = new Product();
  product.ProductID = 1001;
  product.ProductName = "Chai";

  string key = "1:dbo.Products";

  try{
      //Cache key should be same as used in trigger
      cache.Insert(key, product);

      //Modify Product record in database while program waits...
      Thread.Sleep(5000);

      //... and then check for its existence
      Object item = cache.Get(key);
      if (item == null){
          // item removed successfully
      }
      else{
          // item not removed successfully
      }
  }
  catch (OperationFailedException e){
      // handle exception
  }
Back to top Copyright © 2017 Alachisoft