Click here to Skip to main content
13,901,954 members
Click here to Skip to main content
Add your own
alternative version


46 bookmarked
Posted 10 Mar 2011
Licenced CPOL

.NET 4.0 MemoryCache with SqlChangeMonitor

, 15 Jun 2012
Rate this:
Please Sign up or sign in to vote.
How to use the SqlChangeMonitor with the new MemoryCache class in .NET 4.0.


There isn't a lot of documentation on the internet about how to use the SqlChangeMonitor with the new MemoryCache class in .NET 4.0, so I thought I would add my example.

Database Preparation

The first step is to prepare your database for SqlChangeMonitor. This feature uses the SQL Server Service Broker to setup a notification event that fires to notify when data changes that would change the returned recordset of a query, so we have to enable the service broker on our database:


With that out of the way, we can continue on to setting up the cache in code…


public bool IsInMaintenanceMode()
 bool inMaintenanceMode;

 if (MemoryCache.Default["MaintenanceMode"] == null)
  CacheItemPolicy policy = new CacheItemPolicy();

  string connStr = "MY CONNECTION STRING";


  using (SqlConnection conn = new SqlConnection(connStr))
   using (SqlCommand command = new SqlCommand(
          "Select MaintenanceMode From dbo.MaintenanceMode", conn))
    command.Notification = null;

    SqlDependency dep = new SqlDependency();



    inMaintenanceMode = (bool)command.ExecuteScalar();

    SqlChangeMonitor monitor = new SqlChangeMonitor(dep);


  MemoryCache.Default.Add("MaintenanceMode", inMaintenanceMode, policy);
  inMaintenanceMode = (bool)MemoryCache.Default.Get("MaintenanceMode");

 return inMaintenanceMode;

This code is a simple way to cache a value that specifies whether the application is currently in maintenance mode. The dbo.Maintenance table contains a single row with a single bit column. This code will allow your application to continuously check to see if it should go into maintenance mode, without hammering your database.

When the value changes in the database, the application receives a notification that it should invalidate the cache. Then, in the next call to IsInMaintenanceMode, MemoryCache.Default["MaintenanceMode"] returns null, causing it to re-register the notification. Just what we want.


  • You must call SqlDependency.Start first, otherwise it just doesn't work.
  • Your SQL Command must follow the guidelines located here. There are lots of things to consider about how you build your query, so pay close attention to this document.
  • After adding your command object to the SqlDependency object, you must execute the command at least once, otherwise it will not register the notification.
  • After executing the command once, you can dispose of your connection. Behind the scenes, .NET will keep a connection open to your SQL Server to listen for the notification.

I hope this helps some people out. I know I spent way too much time looking for documentation that just didn't exist.


  • I have attached a sample project illustrating the use of the code above. It is a simple Console application that just shows how you might use this. Run the SQL script in the attached code to create a database, then run the application. Once it is running, change the value of "MaintenanceMode" in the table. You will see when it is hitting the database, and when it is using the cache. I hope this provides a better example of usage.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Rick Bassham
Software Developer (Senior)
United States United States
I have been a software developer since 2005, focusing on .Net applications with MS SQL backends, and recently, C++ applications in Linux, Mac OS X, and Windows.

You may also be interested in...

Comments and Discussions

QuestionReusing the policy Pin
Gabriel Sandor28-Feb-14 3:29
memberGabriel Sandor28-Feb-14 3:29 
GeneralMy vote of 4 Pin
Christian Amado27-Jul-12 10:53
professionalChristian Amado27-Jul-12 10:53 
GeneralRe: My vote of 4 Pin
Rick Bassham27-Jul-12 11:22
memberRick Bassham27-Jul-12 11:22 
QuestionIs the sample source code downloadable? Pin
Jeremy Thomas17-Apr-12 19:10
memberJeremy Thomas17-Apr-12 19:10 
AnswerRe: Is the sample source code downloadable? Pin
Rick Bassham18-Apr-12 11:31
memberRick Bassham18-Apr-12 11:31 
Questionpartial example? Pin
scmahaffey24-Oct-11 11:09
memberscmahaffey24-Oct-11 11:09 
AnswerRe: partial example? Pin
Rick Bassham18-Apr-12 11:32
memberRick Bassham18-Apr-12 11:32 
GeneralMy vote of 5 Pin
Nickos_me22-Sep-11 22:10
memberNickos_me22-Sep-11 22:10 
QuestionHow to use SQLChangeMonitor with SDF Pin
Venkatesh.S24-Jul-11 22:47
memberVenkatesh.S24-Jul-11 22:47 
AnswerRe: How to use SQLChangeMonitor with SDF Pin
Rick Bassham18-Apr-12 11:33
memberRick Bassham18-Apr-12 11:33 
GeneralMy vote of 5 Pin
Menahem5-Jul-11 21:34
memberMenahem5-Jul-11 21:34 
GeneralMy vote of 1 Pin
Austin Rasmussen31-Mar-11 13:58
memberAustin Rasmussen31-Mar-11 13:58 
GeneralMy vote of 1 Pin
Zeeshan Anwar31-Mar-11 10:38
memberZeeshan Anwar31-Mar-11 10:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web06 | 2.8.190306.1 | Last Updated 15 Jun 2012
Article Copyright 2011 by Rick Bassham
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid