NCache 4.6 - Online Documentation

Using SQL Dependency

 
NCache provides feature of SQL dependency for the purpose of synchronizing cache with Microsoft SQL Server 2005/2008. An item is added with SQL dependency, specifying SQL statement representing one or more rows (query result set) in database. Then NCache 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.
 
[SQL]
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];
 
 
  • Replace [IP-Address] with node IP address and [User Name] with database user name.
  • In a clustered environment, users are supposed to create SQL Service and Queue per NCache process.
  • You can also create required SQL Service and Queue using script "NCacheServiceQueue.sql" located at:
      "%Install Directory%/NCache/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 SqlCacheDependency 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
                {
                    //SQL Dependency did not work
                }
            }
            catch (OperationFailedException e)
            {
                //handle exception
            }
 
 
See Also