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.
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();
}
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!
|
No responses found. Be the first to respond and make money from revenue sharing program.
|