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:

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’s assembly directory before
deploying them on database. Copy these assemblies from GAC to
%NCHOME%/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 %NCHOME%/bin/IDE/NCache Manager to
%NCHOME%/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).

Deploy stored procedure on database. After deployment, the 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: