Microsoft 70-487: Implement caching

Exam Objectives

Cache static data, apply cache policy (including expirations); use CacheDependency to refresh cache data; query notifications

Quick Overview of Training Materials

Exam Ref 70-487 - Chapter 1.2
[MSDN] System.Runtime.Caching
[MSDN] Detecting Changes with SqlDependency
[CodeProject] .NET 4.0 MemoryCache with SqlChangeMonitor
[CodeProject] Query Notification using SqlDependency and SqlCacheDependency

My GitHub Sample Code
70-486: Design a caching strategy

Caching static data and using cache policy

I beat the subject of caching absolutely to death in my "Design a caching strategy" post, and touch on it to a lesser extent in my Configure state management post, so I don't think there is any value in revisiting all the different methods in great depth here.  For the sake of review, the various caching strategies I looked at in that post include:

• Page output caching (ASP.NET)
• Object cache (MemoryCache and HttpCache)
• Http Caching (browser)
• Azure Caching (Redis and AppFabric)
• HTML5 Application Cache

The exam ref focuses all it's energy on ObjectCache and some of the supporting classes from the System.Runtime.Caching namespace.  ObjectCache is essentially a key-value store with a fairly simple interface:

• Add - add an item, do not overwrite if exists
• Set - add an item, overwrite if exists
• Get - return an item from the cache
• AddOrGetExisting - add an item, if it exists return what is already there
• Contains - check if a key has an entry in the cache
• GetCount - total number of entries in cache
• GetEnumerator - can be used to iterate over all entries
• GetValues - return a Set of values (takes an IEnumerable of keys)
• Remove - remove a given cache entry

One of the other facilities provided by the namespace is change monitoring.  Files and Sql databases can be monitored for changes, invalidating the cache when the data of interest is changed.  The HostFileChangeMonitor is use to track the state of a file, and SqlChangeMonitor for a database.  The database case I'll examine in a bit, and my example from the caching post includes the use of the file monitor.

One of the two hardest things to do correctly in programming is cache invalidation.  However, setting the expiration policy on the ObjectCache is not hard at all (whether that policy is optimal... well that's the hard part lol).  The CacheItemPolicy class allows you to set an absolute or sliding expiration, set change monitors, set the priority of the cache item (which in the Runtime space is only Default or DoNotRemove... the System.Web.Caching version has a much finer gradient...), or set callback delegates that fire when the cache is removed or updated.

The following code is from a proxy service I wrote for work (and ultimately didn't need).  It is creating a Windows Identity based on a username and password, and because this is interacting with Win32 and Active Directory and all that jazz, and I anticipated it being called pretty frequently, it seemed smart to cache the identity object and reuse it (many calls would be using a service account identity anyway).  The number of minutes to save an entry read from the App.config file and used to set the absolute expiration of the identity.  The policy is then included with the key and value when calling "Add":

class AuthHelper
{
    /// <summary>
    /// 
    /// Accecpts a username, password, and domain, and calls the Logon api (Win32) to log
    /// the user in and return a WindowsIdentity object.
    /// 
    /// </summary>
    /// <param name="username"></param>
    /// <param name="password"></param>
    /// <param name="domain"></param>
    /// <returns>WindowsIdentity object for the passed in credentials</returns>
    internal static WindowsIdentity Login(string username, string password, string domain)
    {
        //get default instance of MemoryCache
        ObjectCache cache = MemoryCache.Default;

        //use + and () as delineator: 
        // + is an invalid char in usernames
        // () are invalid in domain names
        string key = String.Format("(%s)+(%s)+(%s)", username, password, domain);

        //attempts to load the contents of the "filecontents" cache item
        WindowsIdentity serviceAcct = cache[key] as WindowsIdentity;

        //if this value is null, then the windows identity for the 
        //provided credentials was not found in cache
        //most of this code is found here 
        //http://stackoverflow.com/questions/9909784/impersonating-a-windows-user
        if (serviceAcct == null)
        {

            IntPtr userToken = IntPtr.Zero;

            bool success = External.LogonUser(
              username,
              domain,
              password,
              (int)LogonType.LOGON32_LOGON_INTERACTIVE, //2
              (int)LogonProvider.LOGON32_PROVIDER_DEFAULT, //0
              out userToken);

            serviceAcct = new WindowsIdentity(userToken);

            //create a caching policy object with configured duration in minutes
            CacheItemPolicy policy = new CacheItemPolicy();
            double minutes = 1.0;
            Double.TryParse(
              ConfigurationManager.AppSettings.Get("cachepolicy.expiration.minutes"),
              out minutes);
            policy.AbsoluteExpiration = DateTimeOffset.Now.AddMinutes(minutes);
            cache.Set(key, serviceAcct, policy);
        }
        return serviceAcct;
    }
}

Query Notifications

This turned into another one of those demos that made me want to light my laptop on fire, but I eventually managed to bang out a working example. The CodeProject article was instrumental in getting my code straightened out, since MSDN provided precious little in the way of working example code for SqlDependency. The "Detecting Changes" article makes sense after working with the CodeProject stuff, but on it's own it just didn't give enough context.  

There is a very specific order of events that need to occur for this to all work:

• SqlDependency.Start(connection_string)
• create SqlConnection(connection_string) and SqlCommand(query, connection)... in using blocks is best but probably not strictly necessary.
• create new SqlDependency instance, associate command.
• open connection and execute command.  Doing this "registers" the query.
• add dependency instance to a new SqlChangeMonitor
• add sql monitor to cache policy
• cache value, along with policy

There are a couple ways to respond to changes.  One is to add an event listener directly to the SqlDependency object through the OnChanged property.  Another is to use the RemovedCallback on the cache policy object, which will fire when the SqlMonitor invalidates the cache entry when the database changes.  The following code is from the WPF example app (the console version has code that is nearly identical):

public void UpdateCache()
{

    var policy = new CacheItemPolicy();

    var connectionString =
        ConfigurationManager.ConnectionStrings["Default"].ConnectionString;

    SqlDependency.Start(connectionString);

    using (var conn = new SqlConnection(connectionString))
    {

        using (SqlCommand command =
            new SqlCommand("SELECT PersonType FROM Person.Person WHERE PersonType = 'EM'", 
            conn))
        {
            SqlDependency sqlDependency = new SqlDependency(command);

            sqlDependency.OnChange += (s,x) => UpdateCache();

            ChangeMonitor sqlMonitor = new SqlChangeMonitor(sqlDependency);

            conn.Open();
            command.ExecuteScalar();

            var empCount = GetCurrentEmployeeCount();
            policy.ChangeMonitors.Add(sqlMonitor);
            cache.Add("employee_count", empCount, policy);

            UpdateList(empCount);
        }
    }
}

You'll need to make sure that Service Broker is enabled in SQL Server.  The CodeProject article also mentioned having "Trustworthy" set to True, though mine already was when I checked. I'm using Express 2012 and was still able to make this all work...

Now after going back and screwing around with the examples some more, I was getting some funky behavior from the WPF app, which uses an event handler directly on the SqlDependency to try and refresh the cache.  But for some weird reason it wasn't working right.  When I switched to using the RemovedCallback property on the policy object to refresh the cache, then it worked as expected... hmm... To manufacture data change events I opened a connection to the database from Server Explorer, right clicked the table of interest and selected "Show Data".

The Exam Ref was totally unhelpful in regards to understanding or implementing the above code.  I know, shocker.  Mostly he goes on for a couple pages railing on the weaknesses of the functionality and saying nobody uses it.  To be fair, the remarks on the MSDN page for the SqlDependency class notes that it isn't really suited to client applications that may open up hundreds or thousands of connections to the database, but rather is better for the server side cache management use case.  The quirks do make it difficult to get started, and there are many limitations to the queries that can be used in the command.  But I wouldn't dismiss it entirely.

CacheDependency

Just as there is a difference between the System.Web.Caching.Cache and the System.Runtime.Caching.Cache, there are subtle differences between the CacheDependency class from the System.Web.Caching namespace, and the similar Dependency classes described above. The Sql based dependency on the web side is called SqlCacheDependency.

Now, one point that threw me off at first was that there are two constructors for SqlCacheDependency, once taking a SqlCommand, and the other taking two strings (connection string, and table name).  Based on which of the two constructors you use, the dependencies and behavior can vary considerably.

Using the SqlCommand based constructor basically behaves the same as the SqlDependency I described above.  You need to call SqlDependency.Start(), you need to execute the command to "register" for polling, all that junk.  Really I get the sense that this constructor turns SqlCacheDependency into a wrapper (or at least does the exact same thing).  This has the advantage of giving you more control over the query.  In my example code below, the code that caches the employee count uses this approach, and it only triggers if the employee count changes.

The other constructor is meant for older SQL Server versions (the MSDN page mentions SQL Server 7.0 and SQL Server 2000... basically anything older than SQL Server 2005).  While this version doesn't require some of the contortions that the SqlCommand version seems to require in the immediate code around the constructor, it does require other bits and bobs to be in place elsewhere (which is arguably worse): enabling notifications and configuring polling.

The SqlCacheDependencyAdmin class has methods for enabling notifications on the database, and for enabling notifications for individual tables.  Is seems this is done by executing a stored procedure on the target database called AspNet_SqlCacheRegisterTableStoredProcedure.  A bug I ran into with this particular sproc is that it expects your tables to be in the [dbo] schema.  AdventureWorks isn't set up that way, but fortunately I found a SQL query in the ASP.NET forums that fixed it. Calling EnableTableForNotifications("schema.tablename") will add a trigger to that table, which is what actually executes the notification.

In my sample code, I put the calls to these in the Global.asax file so that they would only be called one each time I ran it (really they only need to be called once ever for that DB and table, but I wanted to make it idiot proof for educational purposes lol).  

private void PrepareDatabase()
{
    var connectionString =
        ConfigurationManager.ConnectionStrings["AdventureWorks"]
        .ConnectionString;

    try
    {
        SqlCacheDependencyAdmin.EnableNotifications(connectionString);
        SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, "Person.Person");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.StackTrace);
    }
}

In addition to the trigger on the table, the legacy constructor also requires it's own section in the configuration file.  The sqlCacheDependency element lives under system.web, and only has a few knobs to turn: "enable" can be true or false, "pollTime" sets the poll interval in milliseconds and must be >500, and the databases child element sets up the connection to the database.

  <system.web>
    <authentication mode="None" />
    <compilation debug="true" targetFramework="4.5.2" />
    <httpRuntime targetFramework="4.5.2" />
    <caching>
      <sqlCacheDependency enabled = "true" pollTime = "15000" >
        <databases>
          <add name="AdventureWorks"
            connectionStringName="AdventureWorks"
          />
        </databases>
      </sqlCacheDependency>
    </caching>
  </system.web>

The following code is the most interesting bit from the sample project.  UpdateCache() uses the SqlCommand constructor to get notification when the employee count changes in the table.  The UpdateCacheSimple() method uses the legacy constructor, and updates the cached customer count any time the Person table changes (even if it doesn't change the customer count).  It's probably unfair to call the legacy version "simple", since the PrepareDatabase() and web config stuff above is all in support of this constructor.  But looking just at the immediate code snippets, it does look simpler. Course it could just be the way I put it together... ¯\_(ツ)_/¯

private void UpdateCache()
{ 
    var connectionString =
        ConfigurationManager.ConnectionStrings["AdventureWorks"]
        .ConnectionString;
    SqlDependency.Start(connectionString);

    using (var conn = new SqlConnection(connectionString))
    {

        using (SqlCommand command =
            new SqlCommand("SELECT PersonType FROM Person.Person WHERE PersonType = 'EM'",
            conn))
        {
            var empCount = GetCurrentEmployeeCount();

            string key = "employee_count";
            string value = "Count: " + empCount + ", as of " + DateTime.Now.ToLongTimeString();
            SqlCacheDependency dep = new SqlCacheDependency(command);

            DateTime exp = DateTime.Now.AddMinutes(5);
            conn.Open();
            command.ExecuteScalar();

            HttpContext.Cache
                .Add(key, value, dep, exp, 
                Cache.NoSlidingExpiration, 
                CacheItemPriority.Default, CallBack);
        }
    }
}

private void UpdateCacheSimple()
{
    var connectionString =
        ConfigurationManager.ConnectionStrings["AdventureWorks"]
        .ConnectionString;

    var custCount = GetCurrentCustomerCount();

    string key = "customer_count";
    string value = "Count: " + custCount + ", as of " + DateTime.Now.ToLongTimeString();
    SqlCacheDependency dep = new SqlCacheDependency("AdventureWorks", "Person");
    DateTime exp = DateTime.Now.AddMinutes(5);

    HttpContext.Cache
        .Add(key, value, dep, exp,
        Cache.NoSlidingExpiration,
        CacheItemPriority.Default, CallBackSimple);
}

In the resulting page, we can see the different cache times.  This is because I changed a person "type" from a non-employee type to another non-employee type, so the employee count didn't change.  The customer count cache value (second one down), got refreshed based on it's courser grained trigger.

The Exam Ref actually spends a few pages going over some of the idiosyncrasies of using the SqlCacheDependency, particularly the difference in functionality between the two constructors. Without any concrete examples to accompany it, though, it felt pretty handwavy.  I will say that the author makes a good point about the limitations of the legacy constructor with regard to relational queries.  If we wanted to trigger updates based on a query that included JOIN, there isn't an obvious way of doing that without using the SqlCommand constructor.