SQL dependency caching with Entity Framework

Caching is great for performance. If you have some static data (data that doesn't change e.g. country names) there's no point in retrieving it from the database several times per request.  

Here's a little code snippet that explains how to do it:

string cacheKey = "UniqueKey";
var data = MemoryCache.Default[cacheKey] as List<string>; 
if (data != null) { return data; }
MemoryCache.Default.Set(cacheKey, data, DateTime.Now.AddMinutes(10));
return data;

Pretty simple. If the data exists in the cache, return it. If not, get it from the database, cache it and return it.

 

What happens if you have data that can occasionally change?   For example you might have an admin screen that allows a user to change country names or add new ones.

In this scenario the easiest thing to do is update the cache when updating the database.   This works fine for single process web applications but won't work if you have a web farm or web garden since each process will maintain it's own cache. This means an update triggered on one web server won't update the cache on another web server.

 

SQL Dependency to the rescue.   SQL Dependency allows a cached item to subscribe to an event which is triggered when the results of a database query changes.   This is great as it doesn't matter how many web servers or worker proccesses you have configured.   Whenever there is an update to a particular table, each worker process will receive an update event to remove the cached item.   The next time the data is requested, it will be retrieved from the database.

Here's how to set it up:

First you need to configure the database.   Run this query on the database that your web application uses:

ALTER DATABASE [name_of_database] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
EXEC sp_addrole 'sql_dependency_role'
GRANT CREATE PROCEDURE to sql_dependency_role
GRANT CREATE QUEUE to sql_dependency_role
GRANT CREATE SERVICE to sql_dependency_role
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to sql_dependency_role
GRANT VIEW DEFINITION TO sql_dependency_role
GRANT SELECT to sql_dependency_role
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sql_dependency_role
GRANT RECEIVE ON QueryNotificationErrorsQueue TO sql_dependency_role
EXEC sp_addrolemember 'sql_dependency_role', 'db_user_that_will_be_retrieving_the_data'

Make sure you update name_of_database and db_user_that_will_be_retrieving_the_data to the database and user that the web application uses.

 

Now here's the C# code to update the cache and subscribe to the update event:

 

var connectionString = ConfigurationManager.ConnectionStrings["NameOfConnectionString"].ConnectionString;
// important step otherwise it won't work
 SqlDependency.Start(connectionString);
CacheItemPolicy policy = new CacheItemPolicy();
// always add an absolute expiration so cached item doesn't remain indefinately in the case of a dependency failure
policy.AbsoluteExpiration = DateTime.Now.AddMinutes(10);
CacheItem item = new CacheItem(cacheKey, data);
using (SqlConnection connection = new SqlConnection(connectionString)) 
{
   connection.Open();
   // command which will be used to notify updates - probably want to parametrize this
   var sql = "SELECT UpdateDate FROM dbo.tableName WHERE Id = 1";
   using (SqlCommand command = new SqlCommand(sql))
   {
      command.Connection = connection;
      SqlDependency dependency = new SqlDependency(command);
      SqlChangeMonitor monitor = new SqlChangeMonitor(dependency);
      // add dependency
      policy.ChangeMonitors.Add(monitor);
      // update cache
      MemoryCache.Default.Set(item, policy);
      // execute once otherwise dependency not registered
      command.ExecuteNonQuery();
   }
}