CLR Procedures in SQL Server with Cache

Note

This feature is only available in NCache Enterprise Edition.

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 the steps specified below.

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

Prerequisites

• .NET/.NET Core

• To learn about the standard prerequisites required to work with all NCache client side features please refer to the given page on Client Side API Prerequisites.
• Setup Environment for using CLR Procedures.
• The .NET Framework must be 4.8.
• Microsoft SQL Server must be SQL Server 2008 or above.
• For API details refer to: ICache, CacheManager, Dispose, Remove.

Note

It is recommended to use CLRStoredProcedure.NCache Nuget package only for CLR procedure applications and not for object data caching applications.

Step 1: Create a New Application

Create a new application StoredProcedure using Microsoft Visual Studio. The application should meet the following criteria;

• it must be a Class Library.
• it must use the .NET framework 4.8.

Step 2: Add a CLR Stored Procedure

Add a CLR stored procedure in your application. Write the your desired logic in the application. 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 stored procedure which removes an item from the cache in case it is updated.

Important

In case NCache is not installed on the machine where you are using CLR stored procedures, client.ncconf must be placed on the path C:\Windows\System32. Otherwise, the operations on the cache will not be performed.

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RemoveOnUpdate(string cacheName, string key)
    {
        try
        {
            // Connect to the cache
            ICache cache = CacheManager.GetCache(cacheName);

            // Remove specified item
            cache.Remove(key);

            // Dispose the cache
            cache.Dispose();
        }
        catch (OperationFailedException ex)
        {
            // NCache specific exception

            // Exception can occur due to:
            // Connection Failures
            // Operation Timeout
            // Operation performed during state transfer
            SqlContext.Pipe.Send($“NCache operation failed due to {ex}”);
        }
        catch (Exception ex)
        {
            // Any generic exception like ArgumentNullException or ArgumentException
            // Argument exception occurs in case of empty string name
            SqlContext.Pipe.Send($“Operation failed due to {ex}”);

        }
    }
}

Note

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: Execute CLR Stored Procedure

• Build project StoredProcudure to generate StoredProcudure.dll.

• Now run the following command in SQL Server to deploy the dll.

Note

Modify the path in this query according to the path of your application.

CREATE ASSEMBLY [NCacheCLRStoredProcedures] FROM N'C:\Users\john_doe\source\repos\StoredProcedure\bin\Debug\StoredProcedure.dll' WITH PERMISSION_SET=UNSAFE

• Enable CLR integration with SQL Server using the following command:

sp_configure 'clr enabled', 1

• Create a stored procedure in SQL Server using the following command:

CREATE PROCEDURE RemoveOnUpdate
@cacheName AS nvarchar(4000),
@key AS nvarchar(4000)
AS
EXTERNAL NAME NCacheCLRStoredProcedures.StoredProcedures.RemoveOnUpdate

• Execute CLR stored procedure using the following command:

Note

Make sure that the cache specified in this step exists in client.ncconf.

EXEC RemoveOnUpdate “demoCache”, “key:123”

See Also

Cache Data Dependency on SQL Server
Cache Data Dependency on OleDB
Cache Data Dependency on Oracle Database
Locking Data For Concurrency Control
Cache Data Dependency on External Source