Runtime Caching with SqlChangeMonitor

I was looking for a cache solution similar to the one provided by System.Web.Caching for my services layer and I was introduced to System.Runtime.Caching by my associate (after he completed the research assignment which I gave him that is ;p ). New in .NET 4, System.Runtime.Caching provides an easy to use cache solution for non-ASP.NET Web applications and is just what I needed.

Sometime back I talked about Query Notifications & SqlDependency. Now, I'm gonna do something similar but I will attempt to wire-up a SqlDependency to the SqlChangeMonitor to allow our cache data to be auto-refreshed when there are changes to the database table. This is useful for caching reference data for services that are built on WCF (or WEB API).

I will show it with a very rough code example. Please refactor it to your needs.

Start by declaring a few global variables.

private static MemoryCache _cache;

private SqlChangeMonitor _monitor;

private SqlDependency _dependency;

private bool _hasDataChanged;

The static MemoryCache variable is defined for coding convenience so that we don't have to keep referring to MemoryCache.Default all the time.

At the constructor of the class, initialize the _cache and call the SqlDependency.Start() method.

_cache = MemoryCache.Default;

SqlDependency.Start(CONNECTION_STRING);

Next, create the main function that loads the data into a CacheItem:

private CacheItem LoadData(out CacheItemPolicy policy)

{

    const string SQL_STATEMENT = "SELECT [ID], [Data] FROM dbo.TestData";

    var data = new List<TestData>();

    var db = new SqlDatabase(CONNECTION_STRING);

    using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))

    {

        // Initialize SqlDependency

        _dependency = new SqlDependency(cmd as SqlCommand);

        _dependency.OnChange += dependency_OnChange;

        using (IDataReader dr = db.ExecuteReader(cmd))

        {

            while (dr.Read())

            {

                // Create a new TestData

                var testData = new TestData();

                // Read values.

                testData.ID = GetDataValue<int>(dr, "ID");

                testData.Data = GetDataValue<string>(dr, "Data");

                // Add to List.

                data.Add(testData);

            }

        }

    }

    // Create a new monitor.

    _monitor = new SqlChangeMonitor(_dependency);

    // Create a policy.

    policy = new CacheItemPolicy();

    policy.ChangeMonitors.Add(_monitor);

    policy.UpdateCallback = CacheUpdateCallback;

    // Put results into Cache Item.

    var item = new CacheItem("TestData", data);

    // Reset the data changed flag.

    _hasDataChanged = false;

    return item;

}

Few things to note in this code-snippet:

1. The CONNECTION_STRING is a constant I defined for my connection string and its value is not shown in this example.
2. I'm using Enterprise Library Data Access Application Block (DAAB) but you can use standard ADO.NET.
3. I'm loading the data into an Entity called TestData with a custom GetDataValue method that checks for null before assigning the values.
4. I'm using a hard-coded SQL statement but you can change it to a stored procedure if you wish.

In the example, we pass the command into a new SqlDependency instance after creating it and subscribe to the OnChange event. After loading the data, we create a new SqlChangeMonitor and pass in the SqlDependency instance to its constructor. We then create a new CacheItemPolicy and add the SqlChangeMonitor to its ChangeMonitors collection. Next, we register to the UpdateCallback and create a new CacheItem to store the data. Finally, we set the _hasDataChanged flag to false before returning the CacheItem.

The OnChange event doesn't really do much other than setting the _hasDataChanged flag to true when there is a change in the TestData table.

void dependency_OnChange(object sender, SqlNotificationEventArgs e)

{

    // DataChange Detection

    _hasDataChanged = true;

}

The CacheUpdateCallback method contains a little more code to refresh the cache item in memory.

private void CacheUpdateCallback(CacheEntryUpdateArguments args)

{

    // Dispose of monitor

    if (_monitor != null)

        _monitor.Dispose();

    // Disconnect event to prevent recursion.

    _dependency.OnChange -= dependency_OnChange;

    // Refresh the cache if tracking data changes.

    if (_hasDataChanged)

    {

        // Refresh the cache item.

        CacheItemPolicy policy;

        args.UpdatedCacheItem = LoadData(out policy);

        args.UpdatedCacheItemPolicy = policy;

    }

}

The _monitor and the OnChange event subscription will be cleaned up in this method and the _hasDataChanged flag is checked to see if the items in cache should be refreshed. Without doing this, the data in the cache will be invalidated and reset to null.

Finally, the method that we want to expose out to callers to call will be like the following:

public List<TestData> Select()

{

    if (_cache["TestData"] == null)

    {

        // Create a policy.

        CacheItemPolicy policy = null;

        // Load data into Cache Item.

        var item = LoadData(outpolicy);

        // Set Cache Item into cache with the policy.

        _cache.Set(item, policy);

    }

    return _cache["TestData"] as List<TestData>;

}

The method first checks the cache for any data and if none is available, it will query the database for it and sets it to the cache. If the data is available, it will be retrieved directly from the cache.

I hope this post is useful to you. Please make sure that you follow the rules and guidelines of SQL Server Service Broker (i.e. ALTER DATABASE LeaveSample SET ENABLE_BROKER) and Query Notifications to get it working. :)