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.
To get complete details about setting up the environment, please refer to Setup SQL Server for CLR Procedures.
Prerequisites
- 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.
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 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 generateStoredProcudure.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.