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’s assembly directory before deploying them on database. Copy these assemblies from GAC to [InstallDir]/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 [InstallDir]/bin/IDE/NCache Manager to [InstallDir]/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 the 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, 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:

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:

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
  }