Using CLR Procedures in SQL Server

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.

Why to Use CLR Procedures

You can use CLR procedures since they give better results while executing complex logics. They ensure type safety and memory management. Large data sets can be managed using CLR procedures easily as they provide better code management.

To use CLR store procedures for NCache, follow steps specified below.

In order to get complete detail about setting up environment please refer to Setup SQL Server for CLR Procedures .

Pre-Requisites for Using CLR Procedure

Setup Environment for using CLR Procedures.
The .NET Framework must be 4.0 or above.
Microsoft SQL Server must be SQL Server 2008 or above.
Include the following namespace in your application:
- Alachisoft.NCache.Web.Caching
- Alachisoft.NCache.Runtime
The application must be connected to cache before performing the operation.
Cache must be running.
Make sure that the data being added is serializable.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Step 1: Create a new SQL Application

Create a new SQL Server Database Project application using Microsoft Visual Studio. Make sure to do the following in your application:

Go to the Properties and set the Permission level of SQLCLR to UNSAFE.
Go to the Properties and then Database Settings. In the Miscellaneous section select the Trustworthy checkbox and select OK.

Step 2: Add a stored Procedure

Add a stored procedure in your Database project. Write the synchronization logic in the database. In order to get detail about how to use CLR procedures refer to the Microsoft documentation on CLR Procedures

Following is a sample of the CLR procedure which removes an item from the cache in case it is updated.

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RemoveOnUpdate(string cacheName, string key)
    {
    try
    {
        cacheName = "myReplicatedCache";
        Cache cache = NCache.InitializeCache(cacheName);
        cache.Delete(key);
        cache.Dispose();
    }
    catch (OperationFailedException ex)
    {
        // NCache specific exception can occur due to:
        // Connection failures
        // Operation performed during state transfer
        // Operation timeout
    }
    catch (Exception ex)
    {
        // Any generic exception like ArgumentNullException or ArgumentException
    }
}

Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Note

Try to write the synchronization logic that performs operations in bulk.

Step 3: Publish the Database Project

After successfully creating the stored procedure:

Right click on the database project and select Publish.
Enter the Target Database Connection and select Publish.

Warning

Make sure your Visual Studio Database Project's target platform is same as of your SQL Server Version.

After publishing the stored procedure will be added to the stored procedures' list in the database.

Execute the stored procedure in the database.