• Webinars
  • Docs
  • Download
  • Blogs
  • Contact Us
Show / Hide Table of Contents
  • Programmer's Guide
  • Client Side API Programming
    • Setting Up Development Environment
    • Basic Cache Operations
      • Initialize Cache
      • Add Data to Cache
      • Update Data in Cache
      • Fetch Data From Cache
      • Remove Data From Cache
      • Dispose Cache
    • Bulk Operations
      • Adding Collection to Cache
      • Updating Collection in Cache
      • Retrieving Collection from Cache
      • Removing Collection from Cache
      • Deleting Collection from Cache
    • Asynchronous Operations
      • Using Asynchronous Operations
      • Using Asynchronous Operations with Callback Methods
    • Groups and Subgroups
      • Adding/Updating Data Group in Cache
      • Retrieving Data Group from Cache
      • Removing Data Group from Cache
    • Tagging Data in NCache
      • Creating Tags
      • Adding Items with Tags
      • Retrieving Previously Tagged Data
      • Removing Tagged Items from Cache
    • Named Tags
    • Data Expiration Strategies
      • Using Absolute Expiration
      • Using Sliding Expiration
    • Cache Dependencies
      • Key Dependency
      • File Dependency
      • Notification based Dependencies
        • Database Dependency using SQL Server
        • Database Dependency using Oracle
      • Polling Based Dependency
      • Custom Data Source Dependency
      • Multiple Cache Sync Dependency
      • Aggregate Dependency
      • Add Dependency to Existing Item
      • Using CLR Procedures to Call NCache
    • Locking Data in NCache
      • Locking Items in Cache (Pessimistic Locking)
      • Locking Items with Cache Item Versioning (Optimistic Locking)
    • SQL Reference for NCache
      • SQL Syntax
      • Querying Samples for Operators
      • Querying Data in NCache
      • NCache Language Integrated Query (LINQ)
        • Using LINQ in NCache
        • Configuring LINQPad for NCache
        • Querying NCache Data in LINQPad
    • Event Notifications
      • Cache Level Event Notifications
      • Item Level Event Notifications
      • Custom Event Notifications
    • Publish/Subscribe (Pub/Sub) in NCache
      • Pub/Sub Topics
      • Managing Topics
      • Pub/Sub Messages
        • Message Behavior and Properties
        • Creating a Message
      • Publish Messages to Topic
      • Subscribe for Topic Messages
      • Monitoring Pub/Sub Topics
    • Continuous Query
    • Using Streams in NCache
      • Opening with Stream Modes
      • Adding and Updating Data with Streams
      • Retrieving Data from Streams
      • Closing a Stream
    • Security and Encryption
      • NCache Security
      • NCache Data Encryption
    • Data Compression
    • NCache Management API
  • Server Side API Programming
    • Cache Startup Loader
      • Components of Cache Startup Loader
      • Sample Implementation of ICacheLoader on Single Node
      • Sample Implementation of ICacheLoader with Distribution Hints
    • Data Source Providers (Backing Source)
      • Read-Through Caching
        • Configure Read-Through Provider
        • Using Read-Through with Cache Operations
      • Write-Through Caching
        • Configuring Write-Through Provider
        • Using Write-Through with Basic Operations
        • Using Write-Behind with Basic Operations
        • Using Write-Behind with Bulk Operations
        • Using Write-Behind with Async Operations
        • Monitor Write-Through Counters
    • Custom Dependency
      • Sample Implementation of Custom Dependency
      • Sample Usage of Custom Dependency
    • WAN Replication through Bridge
      • Bridge Configurations
      • Implementing Bridge Conflict Resolver
    • Entry Processor
      • Sample Implementation of IEntryProcessor Interface
      • Sample Usage of EntryProcessor
    • MapReduce
      • Sample Implementation of MapReduce Interfaces
      • Sample Usage of MapReduce
    • Aggregator
      • Sample Implementation of IValueExtractor Interface
      • Sample Implementation of IAggregator Interface
      • Sample Usage of Aggregator
    • Dynamic Compact Serialization
  • Client Side ASP.NET Features
    • ASP.NET
      • ASP.NET Session State Provider for NCache
      • Multi-Region ASP.NET Session State Provider for NCache
    • ASP.NET Core
      • Session Storage in ASP.NET Core
        • Configure NCache ASP.NET Core Session Provider
        • Configure ASP.NET Core Sessions with NCache IDistributedCache Provider
      • Multi-Region ASP.NET Core Session Provider for NCache
      • Object Caching in ASP.NET Core
    • ASP.NET SignalR
      • Using NCache Extension for SignalR
    • View State Caching
      • Configuring and Using Content Optimization
      • Group View State with Sessions
      • Limit View State Caching
      • Perform Page Level Grouping for View State
    • ASP.NET Output Cache
      • Configure ASP.NET Output Caching
      • Using ASP.NET Output Cache with Custom Hooks
  • Client Side Third Party Integrations
    • Migrating AppFabric to NCache
      • AppFabric API vs. NCache API
    • NHibernate
      • NCache as NHibernate Second Level Cache
      • Using NHibernate Query Caching
      • Configuring Database Synchronization with NHibernate
    • Entity Framework Caching Integration
      • NCache as Entity Framework Second Level Cache
      • Entity Framework Caching Config File
    • Entity Framework Core Caching
      • Installing NCache Entity Framework Core Provider
      • Configuring NCache Entity Framework Core Provider
      • Using NCache Entity Framework Core Provider
        • Caching Options for EF Core Provider
        • LINQ APIs for EF Core Provider
        • Cache Only APIs for EF Core Provider
        • Query Deferred APIs for EF Core Provider
      • Logging in NCache Entity Framework Core Provider
    • Memcached
      • NCache Memcached Gateway Approach
      • Memcached Client Plugin for .NET
    • Debug NCache Providers in Visual Studio
    • NCache for Visual Studio Extension

Using CLR Procedures to Call NCache

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. To use CLR store procedures for NCache, follow steps specified below. All example SQL scripts specified are for SQL Server 2008 and .NET framework 2.0.

1. Enable CLR Integration on Database

The user should allow CLR procedures to be executed in database by executing the following query on his/her database:

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

2. Register Assemblies with Database

To use NCache in CLR stored procedure, NCache assemblies need to be registered with database. This will enable database to use NCache API in stored procedure.

SQL server does not resolve referred assemblies from GAC, therefore assemblies required by NCache need to be copied in NCache’s assembly directory before deploying them on database. Copy these assemblies from GAC to %NCHOME%/bin/assembly/2.0 folder (choose 4.0 if the target platform is .NET 4.0).

  • log4net.dll
  • protobuf-net.dll
  • Oracle.DataAccess.dll

Copy following assemblies from %NCHOME%/bin/IDE/NCache Manager to %NCHOME%/bin/assembly/2.0.

  • Renci.SshNet.dll
  • SharpSnmpLib.dll

Execute the following query to register assemblies (If NCache install directory is different from used in this query, change path accordingly).

use Northwind
alter database Northwind
set trustworthyon;
go
-- REGISTER SYSTEM ASSEMBLIES ...
drop assembly [System.ServiceModel]
drop assembly SMdiagnostics
drop assembly [System.Web]
drop assembly [System.Messaging]
drop assembly [System.Management]

CREATE ASSEMBLY [SMdiagnostics] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v3.0\Windows Communication Foundation\SMdiagnostics.dll' WITH permission_set=unsafe

CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll' WITH permission_set=unsafe

CREATE ASSEMBLY [System.Management] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.management.dll' WITH permission_set=unsafe

CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH permission_set=unsafe

CREATE ASSEMBLY [System.ServiceModel] AUTHORIZATION dbo FROM N'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll' WITH permission_set=unsafe

--REGISTER NCACHE ASSEMBLIES ...
CREATE ASSEMBLY [Alachisoft.NCache.Web] FROM N'C:\Program Files\NCache\bin\assembly\2.0\Alachisoft.NCache.Web.dll' WITH permission_set=unsafe

3. Create and Deploy CLR Stored Procedure

Create a SQL CLR database project for your database in Visual Studio as shown below.

  • Add reference to NCache assembly named Alachisoft.NCache.Web in the project.

  • Add a stored procedure in your project. Write synchronization logic in database. Following is a sample CLR stored procedure which will remove an item if it is updated in cache.

    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
          public static void RemoveOnUpdate(string cacheName, string key)
          {
              try{
                    if (string.IsNullOrEmpty(cacheName))
                      cacheName = "mycache";
                    if (!string.IsNullOrEmpty(key))
                    {
                        Cache cache = NCache.InitializeCache(cacheName);
                        cache.Delete(key);
                        cache.Dispose();
                    }
                }
                catch (OperationFailedException exp){
                  //handle exception
                }
            }
        }

Deploy stored procedure on database. After deployment, the stored procedure will appear in database stored procedures, as shown below.

4. Write Trigger to Call Stored Procedure

Finally, a database trigger needs to be written that will call stored procedure created in the previous steps whenever an update or delete is performed on database table. Following is a sample script to create trigger on Product table in Northwind database:

Create TRIGGER [dbo].[myTrigger]
ON [dbo].[Products]
FOR DELETE,UPDATE
AS
Declare @cacheName asnvarchar(4000)
Declare @key asvarchar(4000)
select @key=Cast((Deleted.ProductID)asnVarChar)+':dbo.Products', @cacheName='mycache' from Deleted
EXEC dbo.RemoveOnUpdate@cacheName, @key

5. Adding Data in Cache

Following is a sample code that adds an item in cache and updates it in database to verify that item has been removed from cache:

To utilize the API, include the following namespace in your application: Alachisoft.NCache.Runtime.Dependencies.

  //Create object to be added with dependency
  Product product = new Product();
  product.ProductID = 1001;
  product.ProductName = "Chai";
  string key = "1:dbo.Products";
  try{
      //Cache key should be same as used in trigger
      cache.Insert(key, product);

      //Modify Product record in database while program waits...
      Thread.Sleep(5000);

      //... and then check for its existence
      Object item = cache.Get(key);
      if (item == null){
          // item removed successfully
      }
      else{
         // item not removed successfully
      }
  }
  catch (OperationFailedException e){
      // handle exception
  }
Back to top Copyright © 2017 Alachisoft