.Net 4.0 MemoryCache with SqlChangeMonitor

Summary

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 service broker on your database:

ALTER DATABASE database_name SET TRUSTWORTHY ON WITH ROLLBACK IMMEDIATE
ALTER DATABASE database_name SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
ALTER AUTHORIZATION ON DATABASE::database_name TO sa

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

Code

public bool IsInMaintenanceMode()
{
 bool inMaintenanceMode;

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

  string connStr = "MY CONNECTION STRING";

  SqlDependency.Start(connStr);

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

    SqlDependency dep = new SqlDependency();

    dep.AddCommandDependency(command);

    conn.Open();

    inMaintenanceMode = (bool)command.ExecuteScalar();

    SqlChangeMonitor monitor = new SqlChangeMonitor(dep);

    policy.ChangeMonitors.Add(monitor);
   }
  }

  MemoryCache.Default.Add("MaintenanceMode", inMaintenanceMode, policy);
 }
 else
 {
  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.

Notes

• You MUST call SqlDependency.Start first, otherwise it just doesn't work.
• Your SQL Command MUST follow the guidelines located at http://msdn.microsoft.com/en-us/library/ms181122(SQL.100).aspx. 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 with dealing with this. I know I spent WAY too much time looking for documentation that just didn't exist.

CodeProject