CLR Procedures in SQL Server with Cache

Database dependencies using SQL server notifications may reduce the application's performance, as the SQL server throws separate notifications for each data update. If there are too many changes in the database, these notifications may overwhelm network traffic, reducing the performance of both, NCache and user applications.

NCache allows you to write CLR stored procedures for the database to synchronize the cache with the database. CLR procedures do not involve creating dependency data structures like SqlCacheDependency. Also, no database monitoring or notification mechanism.

Why to Use CLR Procedures in SQL Server with Cache

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

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

Before using the CLR Procedures, Setup SQL Server for CLR Procedures.

Prerequisites

• .NET

• 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 the 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 and implement your application logic. For further details, refer to the Microsoft documentation on CLR Procedures.

The 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, the client.ncconf must be placed on the path C:\Windows\System32. Otherwise, the operations on the cache will not take place.

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RemoveOnUpdate(string cacheName, string key)
    {

        // Connect to the cache
        ICache cache = CacheManager.GetCache(cacheName);

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

        // Dispose the cache
        cache.Dispose();
    }
}

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

.NET: Alachisoft.NCache.Runtime.Dependencies namespace.