Click here to Skip to main content
11,631,864 members (70,923 online)
Click here to Skip to main content

An Easy Way to Cache Static Data in ASP.NET

, 15 Oct 2008 CPOL 32.2K 227 41
Rate this:
Please Sign up or sign in to vote.
Improve performance by caching an application's look up data

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

  • 15th October, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ToddHileHoffer
Web Developer
United States United States
Life.

You may also be interested in...

Comments and Discussions

 
Generalthanks a million Pin
dvd_Besoo22-Nov-09 4:34
memberdvd_Besoo22-Nov-09 4:34 
Generaljust a tip. Pin
Gevorg16-Oct-08 14:47
memberGevorg16-Oct-08 14:47 
GeneralRe: just a tip. Pin
ToddHileHoffer17-Oct-08 2:17
memberToddHileHoffer17-Oct-08 2:17 
GeneralRe: just a tip. Pin
jdhforever8-Nov-09 21:48
memberjdhforever8-Nov-09 21:48 
GeneralLINQ sample Pin
ToddHileHoffer16-Oct-08 7:59
memberToddHileHoffer16-Oct-08 7:59 
GeneralNice job! Pin
ballistikx15-Oct-08 6:24
memberballistikx15-Oct-08 6:24 
GeneralRe: Nice job! Pin
ToddHileHoffer15-Oct-08 7:16
memberToddHileHoffer15-Oct-08 7:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150728.1 | Last Updated 15 Oct 2008
Article Copyright 2008 by ToddHileHoffer
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid