NCache 4.6 - Online Documentation

Using CLR Procedures to Call NCache

 
Database dependencies using SQL server notifications may reduce application's performance as SQL server throws a separate notification for each data update which is then handled by NCache. If there are too many changes being made in database, notifications may overwhelm network traffic, reducing performance of both NCache and user application.
 
NCache allows you to write CLR stored procedures for database to synchronize cache with the database. CLR procedures does not involve creating dependency data structures like SqlCacheDependency, also no database monitoring or notification mechanism is required. To use CLR store procedures for NCache, follow steps specified below. All example SQL scripts specified are for SQL Server 2008 and .NET framework 2.0.
 
1. Enable CLR Integration on Database
 
The user should allow CLR procedures to be executed in database by executing the following query on his/her database:
 
-- Enable CLR Integration on Database
sp_configure'clr enabled', 1
GO
RECONFIGURE
GO
 
 
2. Register Assemblies with Database
 
To  use NCache in CLR stored procedure, NCache assemblies need to be registered with database. This will enable database to use NCache API in stored procedure.
 
SQL server does not resolve referred assemblies from GAC, therefore  assemblies required by NCache need to be copied in NCache assemblies directory before deploying them on database. Copy these assemblies from GAC to "NCache/bin/assembly/2.0" folder (choose 4.0 if the target platform is .NET 4.0).
 
  • log4net.dll
  • protobuf-net.dll
  • Oracle.DataAccess.dll
 
Copy following assemblies from "NCache\bin\IDE\NCache Manager" to "NCache/bin/assembly/2.0"
 
  • Renci.SshNet.dll
  • SharpSnmpLib.dll
 
Execute the following query to register assemblies (If NCache install directory is different from used in this query, change path accordingly).
 
use Northwind
 
alter database Northwind
set trustworthyon;
go
 
-- REGISTER SYSTEM ASSEMBLIES ...
drop assembly [System.ServiceModel]
drop assembly SMdiagnostics
drop assembly [System.Web]
drop assembly [System.Messaging]
drop assembly [System.Management]
 
CREATE ASSEMBLY [SMdiagnostics] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v3.0\Windows Communication Foundation\SMdiagnostics.dll' WITH permission_set=unsafe
 
CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll' WITH permission_set=unsafe
 
CREATE ASSEMBLY [System.Management] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.management.dll' WITH permission_set=unsafe
 
CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo
FROM N'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH permission_set=unsafe
 
CREATE ASSEMBLY [System.ServiceModel] AUTHORIZATION dbo
FROM N'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'
WITH permission_set=unsafe
 
--REGISTER NCACHE ASSEMBLIES ...
CREATE ASSEMBLY [Alachisoft.NCache.Web] FROM N'C:\Program Files\NCache\bin\assembly\2.0\Alachisoft.NCache.Web.dll' WITH permission_set=unsafe
 
 
3. Create and Deploy CLR Stored Procedure
 
  • Create  a SQL CLR database project for your database in Visual Studio as shown below.
 
 
  • Add reference to NCache assembly named Alachisoft.NCache.Web in project.
 
  • Add a stored procedure in your project. Write synchronization logic in database. Following is a sample CLR stored procedure which will remove an item if it is updated in cache.
 
    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void RemoveOnUpdate(string cacheName, string key)
        {
            try
            {
 
                if (string.IsNullOrEmpty(cacheName))
                    cacheName = "mycache";
 
                if (!string.IsNullOrEmpty(key))
                {
                    Cache cache = NCache.InitializeCache(cacheName);
 
                    cache.Delete(key);
 
                    cache.Dispose();
                }
            }
            catch (OperationFailedException exp)
            {
                //handle exception
            }
        }
    }
 
  • Deploy stored procedure on database. After deployment stored procedure will appear in database stored procedures, as shown below.
 
 
 
4. Write Trigger to Call Stored Procedure
 
Finally 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 asnvarchar(4000)
declare @key asvarchar(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:
 
    //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
                }
            }
            catch (OperationFailedException e)
            {
                // handle exception
            }
 
 
While adding data in cache, cache key format should be same as passed to stored procedure from trigger in database.
 
 
See Also