An Easy Way to Cache Static Data in ASP.NET

• Download example - 3.69 KB

Introduction

I was recently tasked with creating an ASP.NET application that retrieves mostly all of its reference data from an AS400 DB2 Database(IBM). Since the data on the DB2 was updated nightly, I wanted my application to make only one database call per day, per table. In this scenario, the data would be updated via a nightly batch process and I need to update my data every morning.

Purpose

This article will demonstrate an efficient way to keep look up data cached. Take note that this example is designed for smaller datasets. It probably would not be a good idea to keep large amounts of data in memory, but this example works very well for smaller tables. This sample uses the Northwind database that you can download here.

In order to run the sample, you must change the connection string "NorthwindConnectionString" in the web.config to point to your copy of Northwind.

Using the Code

This example is designed to return data that is updated nightly. In my production application, the data was being called from DB2. For simplicity and so that anyone can run the sample, this example uses SQL Server instead.

//Returns the next 6:00AM when the data will have been updated.            
private static DateTime cacheExpirationDateTime

{  get 
  {
   if (DateTime.Now.Hour < 6)
      return DateTime.Today.AddHours(6);    
   else
      return DateTime.Today.AddDays(1).AddHours(6);
   }
}        

private static DataTable dtTerritories
{  get
   {
   //Check if the item exists in the Cache, if not add it.
    if (System.Web.HttpContext.Current.Cache["dtTerritories"] == null)
    {
      DataTable dtTerritories = 
      GetLiveData("select * from dbo.Territories order by TerritoryDescription asc");
      dtTerritories.PrimaryKey = new DataColumn[] { dtTerritories.Columns[0] };
      //Add PrimaryKey for good measure. If filter on the key it will be quicker.
      System.Web.HttpContext.Current.Cache.Add(
      "dtTerritories", dtTerritories, null, cacheExpirationDateTime, 
      Cache.NoSlidingExpiration, CacheItemPriority.AboveNormal, null);
     }
   return (DataTable)System.Web.HttpContext.Current.Cache["dtTerritories"];
   }
}

//Use a DataView to avoid making database calls on static data
public static DataTable getTerritories(int regionID)
{
DataView dv = new DataView(dtTerritories);
dv.RowFilter = "RegionID = " + regionID.ToString();
return dv.ToTable();
} 

Other Options

This example is clearly not the only approach to doing this. ASP.NET offers many ways of accessing / storing data. This article is just one easy way... In this scenario, I returned DataTables for simplicity and ease of coding. In my actual project (not this example), I had completed my AS400 (DB2) DataAccess class, which returned data from 10 reference tables in less than 2 hours!

History

• 15^th October, 2008: Initial post