Data Caching with .Net 4.0 and Asp.Net MVC – part 3

Welcome to part 3 of this short data caching series. In the previous parts we had a look at how we can employ smart in-memory data caching to avoid round-trips back to the database when using Sql Server and the Entity Framework, set in an Asp.Net MVC context. I showed you how you can seamlessly query from cached or non-cached data, how to update cache items on inserts and updates, and wrapped this up in an extensible framework to keep everything nice and clean. In case you missed those parts, you can view them here:

I received some emails and comments asking a couple of different questions. How can this concept be applied to Sql Server without using EF? How can we create a dependency between Sql Server and our framework? This article is an attempt at answering these two questions, and as it turns out is also a great showcase for just how extensible our little framework is.. because I’m just about to hack it to bits. This article starts from where part 2 left off, so I strongly recommend you download the code from part 2 if you haven’t coded it to that point yourself.

You can grab the code for part 2 on Github. If you'd like to follow along with the source code for this part of the series, check it out on Github.com

How can this concept be applied to Sql Server without using the Entity Framework?

In case you haven’t guessed by now, the answer to this is fairly simple – essentially we just create a new implementation of IVehicleRepository and utilise the normal Ado.Net objects to interact with our database. My solution in this article deals with the following:

  • Refactoring VehicleRepository and moving some common code and methods into an abstract base class so that we can utilise some common bits and pieces in our new repository class
  • Creating a new repository class SqlVehicleRepository, which uses Ado.Net to retrieve data from the database
  • A crude Unit of Work implementation with transaction support

So, to being with lets set about refactoring our current repository class into a common base class. The main reason for doing this is because the code surrounding the cache and opting to do a database read is fairly common to any repository. Since we’ve already abstracted away the actual implementation of our cache, this refactoring is a fairly simple affair.

Our base class looks like this:

public abstract class VehicleRepositoryBase: IVehicleRepository {  
 public VehicleRepositoryBase(): this(new DefaultCacheProvider()) {}

 public VehicleRepositoryBase(ICacheProvider cacheProvider) {
  this.Cache = cacheProvider;
 }

 /// <summary> 
 /// Gets the key used to store vehicle data in the cache 
 /// </summary> 
 protected virtual string CacheKey {
  get {
   return "vehicles";
  }
 }

 /// <summary> 
 /// Gets the cache provider instance 
 /// </summary> 
 public ICacheProvider Cache {
  get;
  protected set;
 }

 /// <summary> 
 /// Gets the vehicle data. 
 /// </summary> 
 public virtual IEnumerable < Vehicle > GetVehicles() { // First, check the cache 
  var vehicleData = GetCachedData(); // If it's not in the cache, we need to read it from the repository 

  if (vehicleData == null) { // Get the data 
   vehicleData = LoadData().ToDictionary(v => v.Id);

   if (vehicleData.Any()) { // Put this data into the cache for 30 minutes 
    Cache.Set(CacheKey, vehicleData, 30);
   }
  }
  return vehicleData.Values;
 }

 /// <summary> 
 /// Loads the data from the data store 
 /// </summary> 
 protected abstract IEnumerable < Vehicle > LoadData();

 /// <summary> 
 /// Inserts a new vehicle 
 /// </summary> 
 public abstract void Insert(Vehicle vehicle);

 /// <summary> 
 /// Updates a vehicle 
 /// </summary> 
 public abstract void Update(Vehicle vehicle);

 /// <summary> 
 /// Saves the data changes. 
 /// </summary> 
 public abstract void SaveChanges();

 /// <summary> 
 /// Clears the data from the cache. 
 /// </summary> 
 public void ClearCache() {
  Cache.Invalidate(this.CacheKey);
 }

 protected Dictionary < Guid, Vehicle > GetCachedData() {
  var cacheData = Cache.Get(CacheKey) as Dictionary < Guid,
   Vehicle > ;
  return cacheData;
 }
}

Note that it still implements IVehicleRepository for us, and simply marks most of the available methods as asbtract so that we can implement later. The key thing though is the concept surrounding GetVehicles() and LoadData(). The former is the consumer’s entry point to getting some data, where the latter is only concerned with actually reading the data from the data source. This means that our base class can be concerned with all the caching stuff that we don’t want to have to worry about every time we create a new repository, and all we have to do with our specific repository implementations is actually load the data.

This leaves our VehicleRepository implementation without a GetVehicles() implementation, but instead has a LoadData() implementation:

public class VehicleRepository: VehicleRepositoryBase {

 protected CachingDemoEntities DataContext {
  get;
  private set;
 }

 public VehicleRepository(): this(new DefaultCacheProvider()) {}

 public VehicleRepository(ICacheProvider cacheProvider): base(cacheProvider) {
   this.DataContext = new CachingDemoEntities();
  }
  /// <summary> 
  /// Load the data from the data source 
  /// </summary> 
 protected override IEnumerable < Vehicle > LoadData() {
  return DataContext.Vehicles.OrderBy(v => v.Name).ToList();
 }

 /// <summary> 
 /// Updates a vehicle 
 /// </summary> 
 public override void Update(Vehicle vehicle) {
  if (vehicle.EntityState == EntityState.Detached) {
   DataContext.AttachTo("Vehicles", vehicle);
  }
  DataContext.ObjectStateManager.ChangeObjectState(vehicle, EntityState.Modified);
 }

 /// <summary> 
 /// Inserts a vehicle 
 /// </summary> 
 public override void Insert(Vehicle vehicle) {
  DataContext.AddToVehicles(vehicle);
 }

 /// <summary> 
 /// Saves data changes to the data store 
 /// </summary> 
 public override void SaveChanges() { // Update or add new/existing entities from the changeset 
  var changeset = DataContext.ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Modified);
  DataContext.SaveChanges();

  var cacheData = GetCachedData();

  if (cacheData != null) {
   foreach(var item in changeset) {
    var vehicle = item.Entity as Vehicle;
    cacheData[vehicle.Id] = vehicle;
   }
  }
 }
}

In addition to removing GetVehicles() and adding LoadData(), I’ve just provided some constructor overloads so that we can still pass through implementations of ICacheProvider to our base class, since that is handling the caching mechanisms now. The rest of the class remains basically the same; remember to mark each method with the override keyword also.

Implementing SqlVehicleRepository

This is where things get more interesting. Now that we have a solid base class which abstracts away the management of the cache, we can go ahead and implement our Ado.Net version of our repository. Note that since I have an Entity Framework model set up with my Vehicle class and I’m just about to demonstrate a new implementation using the same database table, I’m just going to use that same generated class here also, even though it has some extra EF junk on it that we don’t really need for this implementation. If you want to separate the two out and create a completely separate plain entity for this step, feel free as long as you feel able to follow along with the following.

The main bulk of the work here is to implement LoadData(). We’re simply going to use all the old Ado.Net objects here, so if you’ve used them before there should be no surprises here. Lets load the data:

protected override IEnumerable < Vehicle > LoadData() {  
 var connection = CreateConnection();

 var command = new SqlCommand() {
  Connection = connection, CommandType = System.Data.CommandType.Text, CommandText = VEHICLE_SELECT
 };

 List < Vehicle > vehicles = new List < Vehicle > ();

 using(connection) {
  connection.Open();

  var reader = command.ExecuteReader();

  if (reader.HasRows) {
   while (reader.Read()) {
    var vehicle = new Vehicle() {
     Id = (Guid) reader["Id"], Name = (string) reader["Name"], Price = (decimal) reader["Price"]
    };
    vehicles.Add(vehicle);
   }
  }
 }

 return vehicles;
}

As you can see, we’re simply reading the data in using normal Ado.Net objects and spitting out a list of Vehicle. The only thing I haven’t shown you is the VEHICLE_SELECT constant, which I have defined at the top of my class. I’ve also defined constants for inserting and updating (which I will use in a moment), where the actual values to be inserted/updated will go in the place of the parameter names defined in the constant:

#region Query Constants private
const string VEHICLE_SELECT = @ "Select * From Vehicle";  
private  
const string VEHICLE_INSERT = @ "Insert Into Vehicle (Id, Name, Price) Values (@Id, @Name, @Price)";  
private  
const string VEHICLE_UPDATE = @ "Update Vehicle Set Name=@Name, Price=@Price Where Id=@Id";#  
#endregion

We also need an implementation of CreateConnection(), which is as simple as it sounds. It reads the connection string from the application configuration file, and returns us a new instance of SqlConnection:

/// <summary> 
/// Creates the SqlConnection instance 
/// </summary> 
private SqlConnection CreateConnection() {  
return new SqlConnection(ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString);  
}

DemoConnectionString is a connection string entry which I have defined in my web.config file. You will need to add this entry and configure it to point to your own data store appropriately.

Since we are inheriting this class from VehicleRepositoryBase, this data is now automatically cached for us in the same way as the Entity Framework version was, thanks to our smart little framework. We’re not quite done yet though, as we still need to implement our inserting and updating.

Inserts, updates and units of work

The main thing to notice about the Entity Framework version is, thanks to the EF object context all the items to be inserted, updated and deleted are recorded and actioned later in a batch, rather than at the time we actually make the call to insert, update or delete something. This is the Unit of Work pattern in action, and we will implement a basic version of it here. The basic idea is to keep a list of things we want to insert and update for later, and when SaveChanges() is invoked, run through these lists and perform the database actions.

To start, I’ve created two lists at the top of my class to hold these changes, and also gone ahead and implemented the methods for inserting and updating:

private HashSet<Vehicle> _inserts;  
private HashSet<Vehicle> _updates;

public override void Insert(Vehicle vehicle) {  
 if (!_inserts.Contains(vehicle)) _inserts.Add(vehicle);
}

public override void Update(Vehicle vehicle) {  
 if (!_updates.Contains(vehicle)) _updates.Add(vehicle);
}

Note: I haven’t shown the code here, but I’ve just initialised these lists in my constructor – I recommend you do the same.

SaveChanges() is where the real action happens. We’re going to run through each list in turn, build up the correct SQL statements, and then execute them against the server. We’re also going to update our cached values, clear the Unit of Work lists, and wrap the whole thing inside a transaction to keep us from getting erroneous data if something goes wrong.

public override void SaveChanges() {  
 var cached = GetCachedData();
 List < Vehicle > itemsToRecache = new List < Vehicle > ();

 using(var connection = CreateConnection()) {
  connection.Open();

  using(TransactionScope scope = new TransactionScope()) { // Process inserts 
   foreach(var vehicle in _inserts) {

     var command = new SqlCommand() {
      Connection = connection, CommandType = System.Data.CommandType.Text
     };

     command.CommandText = VEHICLE_INSERT;
     command.Parameters.Add(CreateParameter("Id", SqlDbType.UniqueIdentifier, vehicle.Id));
     command.Parameters.Add(CreateParameter("Name", SqlDbType.VarChar, vehicle.Name));
     command.Parameters.Add(CreateParameter("Price", SqlDbType.Decimal, vehicle.Price));
     command.ExecuteNonQuery(); 

     // Insert this vehicle into our cache 
     itemsToRecache.Add(vehicle);
    } 

    // Process updates: 
    foreach(var vehicle in _updates) {

     var command = new SqlCommand() {
      Connection = connection, CommandType = CommandType.Text
     };

    command.CommandText = VEHICLE_UPDATE;
    command.Parameters.Add(CreateParameter("Id", SqlDbType.UniqueIdentifier, vehicle.Id));
    command.Parameters.Add(CreateParameter("Name", SqlDbType.VarChar, vehicle.Name));
    command.Parameters.Add(CreateParameter("Price", SqlDbType.Decimal, vehicle.Price));
    command.ExecuteNonQuery();
    itemsToRecache.Add(vehicle);
   }
   scope.Complete();
  }
 }

 itemsToRecache.ForEach(item => {
  cached[item.Id] = item;
 });

 _inserts.Clear();
 _updates.Clear();
}

private SqlParameter CreateParameter(string name, SqlDbType type, object value) {  
 var param = new SqlParameter(name, type);
 param.Value = value;

 return param;
}

I’ve added a method which is just a wrapper for creating a basic SqlParameter, as it can be a bit fiddly to do inline otherwise. A hidden mechanic at work here is the updating of the cache; we keep a list of items to be re-cached, instead of just updating the cache directly while iterating through the items of work, because we don’t want the cache to be updated if for some reason the transaction doesn’t complete while processing later items. If an error occurs, we just want the data to remain as it was, both in the cache and from the database.

Remember to clear the lists once the work has been completed, or else the same items will be processed again when you next call SaveChanges() using the same instance.*

This wraps up the SqlVehicleRepository implementation. The final thing to do is change your controller to use this implementation of the repository instead of the EF version we created in previous articles, like so:

public HomeController() : this(new SqlVehicleRepository())  
{ 
}

So now, without any further change to the presentation layer you should have your data coming through your SqlVehicleRepository code path instead of your EF implementation, and it should all be cached appropriately. What we’ve effectively done is abstracted our caching code to a level where we can easily add new cached data stores to our application without too much work, and proved the concept by creating such a data store. You should be able to see now how you could easily add an Xml-based store to the application, for example.

How can we create a dependency between Sql Server and our framework?

As I mentioned at the top of the article, there were two pertinent questions about this work, and this is the second. Unfortunately, the answer is not as straight-forward as the first. As I discovered during my research for this article and through my own development tests, it’s actually very difficult to implement an Sql cache dependency between the application and Sql Server using the new caching framework provided by the .Net Framework 4. I never actually got it working, and I had to jump through quite a few hoops just to reach dead-ends where nothing would be cached.

In my mind, this is no bad thing as really I would not want to implement such a dependency. Whenever a record in the database changes, the whole cache is dropped and recreated. This does not scale well and as your dataset (and your user count!) grows, you will find that the cache will be constantly dropped and re-created, making it completely inefficient and pointless. A far better solution is to implement a smart caching strategy which does not create a live dependency against the database, and just accept that if for any reason your data changes through actions which do not route through your application, you will encounter stale data.

However..

That said, I changed tact and got a working solution, and as it turns out is yet another example of how flexible our little framework can be – although you might not like it..

My solution for this problem is to fall back to the original System.Web.Caching framework to implement our in-memory cache. We already have an interface set up from previous articles which allow us to abstract away an implementation of the cache, and so here we can just implement another one which uses the old framework.

If you’ve looked into doing this yourself, you’ve probably seen many arguments which form for and against using System.Web.Caching, mainly because of the namespace name. The ‘Web’ part of the namespace has unruly connotations in the context of our quest for abstraction and a layered approach to developing applications. The truth is the namespace is the only part which should cause concern; the actual cache works fine outside of a web context and can be used as normal.

My implementation here is specifically geared towards using the cache in conjunction with our Sql data implementation, and uses SqlCacheDependency to create the dependency between our data code and the database itself.

The dbEntryName parameter you see in the constructor is actually the name of the cache dependency element in our web.config file (I cover this in a moment), rather than the name of the database. It just so happens I’ve named it the same as my database.

Here is the implementation:

public class SqlDependancyCacheProvider: ICacheProvider {  
 private Cache _cache;

 public string DbEntryName {
  get;
  set;
 }

 public string TableName {
  get;
  set;
 }

 public SqlDependancyCacheProvider(string dbEntryName, string tableName) {
  _cache = System.Web.HttpRuntime.Cache;
  DbEntryName = dbEntryName;
  TableName = tableName;
 }

 public object Get(string key) {
  return _cache[key];
 }

 public void Set(string key, object data, int cacheTime) {
  SqlCacheDependency dep = new SqlCacheDependency(DbEntryName, TableName);
  _cache.Add(key, data, dep, Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(cacheTime), System.Web.Caching.CacheItemPriority.Normal, null);
 }

 public bool IsSet(string key) {
  return (_cache[key] != null);
 }

 public void Invalidate(string key) {
  _cache.Remove(key);
 }
}

They key thing to note here is that it implements our ICacheProvider interface. Because of this, we can just plug it into our SqlVehicleRepository instance to start using it!

public SqlVehicleRepository() : this(new SqlDependancyCacheProvider("CachingDemo", "Vehicle"))  
{ 
}

You can of course pass it through to the constructor from the controller. “CachingDemo” in this case is the name of the caching dependency entry from the web.config file (more on this in a minute), and “Vehicle” is the name of the table that I want to create a dependency on.

There are a couple of things we need to take care of in order for the caching to actually work.

1. Enabling cache notification on the database tables

To set up the tables so that they can notify the cache when the data has changed, you need to run a couple of commands on your database and the table. Open up your Visual Studio Command Prompt from your start menu to run these.

These commands are:

aspnet_regsql.exe -S <server> -U <user> -P <password -d <database> -ed aspnet_regsql.exe -S <server> -U <user> -P <password> -E -d <database> -t <tablename> -et  

The first command will install the necessary tables and stored procedures which are required in order the change notification to work. In the command above, you will need to replace serveruser, password and database with your own settings. If you connect to your database using Windows Authentication you can leave out the -U and -P parameters altogether.

The second command installs the triggers which effectively cause the cache to invalidate when ever the data is altered. This command is run against a specific table, so you will need to run it against every table you wish to enable caching on. Again, configure the parameters according to your own connection to Sql Server.

Run these commands now.

2. Web configuration for caching

The second part to getting all of this working is to add an entry into the web.config which tells the caching framework how to connect to your database, and other parameters like poll time and so on. Place the following inside your web.config file somewhere inside the system.web element:

<caching>  
  <sqlCacheDependency enabled="true" pollTime="1000" > 
    <databases> 
      <add name="CachingDemo" connectionStringName="DemoConnectionString" /> 
    </databases> 
  </sqlCacheDependency> 
</caching>  

The important thing is to make sure that connectionStringName points to a valid connection string setting within your configuration file. The ‘name’ attribute is just an arbitrary symbol given to this cache dependency element, so that it can be reference by name when you create instances of SqlCacheDependency.

And that’s all there is to it. You should now be able to load up your page, verify that data is coming from our new cache provider (using the power of debugging and breakpoints) and also change the database data and have the application reload the data from the data store and rehydrate the in-memory cache.

Hopefully those who enquired about these issues have found this useful!

Source code for part 3 on Github.com