Setup SQL Server for CLR Procedures [Deprecated]

NCache provides you with CLR stored procedures that synchronize the cache with the database. It does not involve database monitoring or a notification mechanism; instead, you can execute any complex logic using CLR procedures, minimizing the risk of excessive notifications.

How to Setup SQL Server for CLR Procedures

Follow the given steps on the database in order to enable CLR procedures and then implement them.

Step 1: Enable CLR on Database

In order to use CLR stored procedures, make sure that CLR integration is enabled on the database. If you haven't enabled CLR integrations already, then execute the following command to enable CLR integrations:

-- Enable CLR Integration on Database
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Step 2: Install NuGet Package for Assemblies and Deploy Them

To use NCache in the CLR stored procedure, NCache assemblies need to be registered with the database. This will enable the database to use the NCache API in stored procedures. NCache provides CLRStoredProcedure.NCache for the required dependent assemblies. Therefore, you first need to download the CLRStoredProcedure.NCache NuGet package or install it in your application. The required assemblies should be referred from this NuGet package.

Step 3: Deploy the Assemblies

Once you have the NuGet package, deploy the assemblies using the following commands:

Note

In order to deploy Alachisoft.NCache.Client.dll in the following query, you need to provide the path according to the location of the NuGet package.

USE Northwind
ALTER DATABASE Northwind
SET TRUSTWORTHY ON;
GO
-- REGISTER SYSTEM ASSEMBLIES ...
DROP ASSEMBLY [System.Web]
DROP ASSEMBLY [System.Management]
DROP ASSEMBLY [Microsoft.CSharp]


CREATE ASSEMBLY [System.Web] FROM N'C:\Windows\Microsoft.Net\Framework64\v4.0.30319\System.Web.dll' WITH PERMISSION_SET=UNSAFE

CREATE ASSEMBLY [System.Management] FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Management.dll' WITH PERMISSION_SET=UNSAFE

CREATE ASSEMBLY [Microsoft.CSharp] FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Microsoft.CSharp.dll' WITH PERMISSION_SET=UNSAFE

CREATE ASSEMBLY [Alachisoft.NCache.Client] FROM N'C:\Users\john_doe\Downloads\CLRStoredProcedure.NCache\lib\net40\Alachisoft.NCache.Client.dll' WITH PERMISSION_SET=UNSAFE

After following these steps, the environment for CLR procedures will be set. Please refer to Programmer's Guide to get help in the implementation of stored procedures.