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.

 

i. 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

 

 

ii. 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).

 

 

Copy following assemblies from"NCache\bin\IDE\NCache Manager" to "NCache/bin/assembly/2.0"

 

 

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 ...

dropassembly [System.ServiceModel]

dropassembly SMdiagnostics

dropassembly [System.Web]

dropassembly [System.Messaging]

dropassembly [System.Management]

 

CREATEASSEMBLY [SMdiagnostics] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v3.0\Windows Communication Foundation\SMdiagnostics.dll' WITHpermission_set=unsafe

 

CREATEASSEMBLY [System.Web] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll' WITHpermission_set=unsafe

 

CREATEASSEMBLY [System.Management] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.management.dll' WITHpermission_set=unsafe

 

CREATEASSEMBLY [System.Messaging] AUTHORIZATION dbo

FROM N'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'

WITHpermission_set=unsafe

 

CREATEASSEMBLY [System.ServiceModel] AUTHORIZATION dbo

FROM N'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'

WITHpermission_set=unsafe

 

--REGISTER NCACHE ASSEMBLIES ...

CREATEASSEMBLY [Alachisoft.NCache.Web] FROM N'C:\Program Files\NCache\bin\assembly\2.0\Alachisoft.NCache.Web.dll'WITHpermission_set=unsafe

 

iii. Create and Deploy CLR Stored Procedure

 

 

 

 

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

        }

    }

}

 

 

 

 

iv. 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

v.     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";

try

{

    //Cache key should be same as used in trigger

    cache.Insert("1:dbo.Products", product);

}

catch (OperationFailedException e)

{

    // handle exception

}

 

//Modifying Product record in database will automatically remove Product from cache

 

While adding data in cache, cache key format should be same as passed to stored procedure from trigger in database.

 

 

See Also

Cache Dependencies Overview | Using SQL Dependency | Using Polling Based Dependency | Using Oracle Dependency | Adding Data with File and Key Dependencies | Synchronizing Cache with Custom Data Sources | Synchronize Data across Multiple Caches | Add Dependency to the Existing Cached Item | Aggregate Cache Dependency

---

Send comments on this topic.