DotNetStories
Data caching in ASP.Net applications
In this post I will continue my series of posts on caching.
You can read my other post in Output caching here.You can read on how to cache a page depending on the user's browser language.
Output caching has its place as a caching mechanism. But right now I will focus on data caching.The advantages of data caching are well known but I will highlight the main points.
- We have improvements in response times
- We have reduced database round trips
- We have different levels of caching and it is up to us as developers to use the appropriate one
I will demonstrate data caching with a hands on example.Embrace yourselves for a very long post.
I assume that you have access to a version of SQL Server and Northwind database.
If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here
1) Launch Visual Studio 2010/2008/2005 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.
2) Add a new item to your site, a web form. Leave the default name, Default.aspx
3) Add ASP.Net folder in your site, App_Code. Add another item in your file, a class file and call it DataAccess.cs.Add a namespace in the same class file and name it Caching.
4) Go to View - > Server Explorer (Database Explorer) and add a connection to the Northwind database.You have just to set the instance of the SQL Server and the name of the database.Test the connection.
5) In your web.config (which is pretty much empty) add a <connectionStrings> section. Ιn my case it like the one below. In your case you must set the Data Source property to your SQL server instance.
<connectionStrings> <add name="NorthwindConnectionString" connectionString="Data Source=USER-PC\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/></
connectionStrings>6) My intention is to populate a datatable object witn some data from the Customers table from the Northwind database.I will create a static method inside the static class.
Inside my DataAccess.cs file I have this code so far
namespace Caching{
public static class DataAcess{
public static DataTable GetCustomers(){
string conn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString; SqlDataAdapter adapt = new SqlDataAdapter("select top 10 * from customers order by country", conn);DataTable mydatatable = new DataTable();adapt.Fill(mydatatable);
return mydatatable;}
}
}
This is very simple static method. It returns a Datatable object as intented.Inside this method I do the following,
-
I get the connection string from the configuration file
-
I create a new adapter object by passing in as parameters, the SQL statement and the connection string
-
I create a new DataTable object and fill it in with data by calling the Fill method of the adapter object.
7) Make sure you have added the following using statements at the beginning of the file.
using
System.Data;using
System.Data.SqlClient;using
System.Configuration;8) Add a Gridview and an ObjectDatasource control on the form.Leave the default names. Set the DataSourceID of the GridView to ObjectDataSource1.
Configure the ObjectDatasource control to select as the Select method the GetCustomers() method of the DataAccess class.The markup for this specifi control should look like this. <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetCustomers" TypeName="Caching.DataAcess"> </asp:ObjectDataSource>9) Run your application and you will see the returning rows displayed to the database.So far we have done nothing regarding caching. We just laid down the foundation.
10) We will make some changes in our GetCustomers() method to incorporate caching.
namespace Caching{
public static class DataAcess{
public static DataTable GetCustomers(){
DataTable mydatatable = null;if (HttpContext.Current.Cache["customers"] == null){
string conn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
SqlDataAdapter adapt = new SqlDataAdapter("select top 10 * from customers order by country", conn);mydatatable = new DataTable();adapt.Fill(mydatatable);
HttpContext.Current.Cache["customers"] = mydatatable;}
elsemydatatable = HttpContext.Current.Cache["customers"] as DataTable;return mydatatable;}
}
}
I make a simple check, if the data is already in the cache I take it from there. If not, I get it from the datasource.Run your application and you will see the results in the grid. Refresh the page and the records will be served from the cache.
11) Every time we add something into the cache we can specify several attributes regarding the chach entry. We can specify attributes like Expires-we specify when the cache will be flushed. We also can congifure the Priority,Dependency (The dependency, this cache entry has on a file or another cache entry) of the cache entry.
12) We can rewrite the code above to use the attributes.
Replace this line of code, in the GetCustomers() method.
HttpContext.Current.Cache["customers"] = mydatatable;
with this line of code
HttpContext
.Current.Cache.Insert("customers", mydatatable, null, DateTime.Now.AddHours(6), Cache.NoSlidingExpiration);I use the Insert method of the Cache object.
If you want to find out more about/understand better what I do here have a look here.
The parameters I pass are a key to reference the object,then the object to be added to the cache,then I specify I have no dependencies,the expiration date is 6 hours from now and finally I specify that the object in the cache will not be removed regarding the time it was last accessed.
Run your application and you will see the results in the grid. Refresh the page and the records will be served from the cache.
13) Now let's see how to remove objects from the cache. We can call the Remove method.We can have implicitly removed data from the cache for reasons like data expiration and memory consumption.
Let's add another method in our DataAccess class file.
public static void RemoveFromCache(){
HttpContext.Current.Cache.Remove("customers");}
Add a button to the web form. Inside the Button1_Click even thandling routine type, protected void Button1_Click(object sender, EventArgs e)
{
DataAcess.RemoveFromCache();
}
Do not forget to add the namespace in the beginning of the file, in my case is
using Caching;
Run your application. The data will be inserted in the cache and the second request will be served from the cache. Now click the button. The items will be removed from the cache.
14) Add a new item to your site, a web form. Leave the default name.Connect to your Northwind database. From the Server Explorer window( Database Explorer ) drag and drop the customers table on the form. A Gridview and a sqldatasource control will be added to the form. You can specify caching in the declarative datasources.
I enabled caching in the sqldatasource control by settting the EnabledCaching to true and CacheDuration to 7200 seconds.
Run your application and you will see the customers records being served from the database in the initial request. After that they are cached and cache serves the coming requests.
15) While designing our application, we must be very careful about caching. Sometimes the user must see the latest data, in some cases caching makes no sense at all.Sometimes an item can remain in the cache while a certain other condition is valid.We want to have the data cached for performance reasons but at the same time we want them to have the latest data(most recent updated data) when something changes in the underlying data store. So we must have some sort of depedency between the underlying data store and the cache. There is something that is called Cache Dependency that brings together cache and the origininal data source.
Add another item on your website, a web form.Leave the default name.Add a GridView control and an ObjectDataSource control on the form.Add another item in your website, an xml file. This is going to be an xml file about footballers.The contents of the xml file are,
<?xml version="1.0" ?>
<footballers>
<footballer Position="Attacking Midfielder">
<team>Liverpool</team>
<name>Steven Gerrard</name>
<manager>Kenny Dalglish</manager>
<price>40.000.000</price>
</footballer>
<footballer Position="Striker">
<team>Manchester United</team>
<name>Wayne Rooney</name>
<manager>Alex Ferguson</manager>
<price>60.000.000</price>
</footballer>
<footballer Position="Striker">
<team>Barcelona</team>
<name>Lionel Messi</name>
<manager>Pep Guardiola</manager>
<price>110.000.000</price>
</footballer>
<footballer Position="Central Defender">
<team>Chelsea</team>
<name>John Terry</name>
<manager>Carlos Anchelotti</manager>
<price>30.000.000</price>
</footballer>
<footballer Position="Striker">
<team>Manchester City</team>
<name>Carlos Tevez</name>
<manager>Roberto Manchini</manager>
<price>65.000.000</price>
</footballer>
<footballer Position="Stiker">
<team>Panathinaikos</team>
<name>Cibril Cisse</name>
<manager>Zesualdo Ferreira</manager>
<price>15.000.000</price>
</footballer>
</footballers>
16) We want to get the records displayed in the GridView control through the ObjectDataSource control and the intermediate data access layer.
We must write a static method in the data access layer class file to get the footballers data back from the xml file.We must write the code in a way that the data is cached until changes take place in the underlying xml file.
public static DataTable GetFootballers(){
DataTable myxmltable = null;if (HttpContext.Current.Cache["footballers"] == null){
string xmlfilename = HttpContext.Current.Server.MapPath("Footballers.xml");DataSet ds = new DataSet();ds.ReadXml(xmlfilename);
myxmltable = ds.Tables[0];
CacheDependency dependency = new CacheDependency(xmlfilename);HttpContext.Current.Cache.Insert("footballers", myxmltable, dependency);}
elsemyxmltable = HttpContext.Current.Cache["footballers"] as DataTable;return myxmltable;
}
The code is more or less similar with the one we wrote previously.We check if the data is in the cache and if not I read the file name, I create a dataset,fill it with data from the xml file and I return the datatable.THe most important bit of the method are these 2 lines.
CacheDependency dependency = new CacheDependency(xmlfilename); HttpContext.Current.Cache.Insert("footballers", myxmltable, dependency);I create a dependency object by passing as a parameter the xml filename. I insert then in the cache the key, the datatable and the dependency.
Set the ObjectDataSource SelectMethod attribute to the method GetFootballers and the TypeName to the Caching.DataAccess.The markup follows below
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetFootballers" TypeName="Caching.DataAcess"></asp:ObjectDataSource>Then set the DataSourceID property of the GridView control to the ObjectDataSource1 control.Run your application.You will see the xml data being displayed on the screen.
Refresh the browser window. XML data is being served from the cache.Leave the browser window open. Change something in the xml file and then refresh the page.The browser window will reflect the new changes. In that case the dependency rule comes into place. The data is cached is flushed from the cache and retrieved from the xml file as intended.
17) Add a new item on the form, a web form. Name it SQLDependencycache.aspx.We will demonstrate in this example how to use sql dependency cache with sql server databases.Add a GridView control and an ObjectDataSource control on the form.
We will need to add another static method in our data access layer.We will get data from the Categories table of the Northwind database.THe code looks like this
public static DataTable GetCategories(){
DataTable thedatatable = null;if (HttpContext.Current.Cache["categories"] == null){
string conn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(conn)){
connection.Open();
SqlCommand cmd = new SqlCommand("select CategoryName,Description from dbo.Categories", connection); SqlDataAdapter dapt = new SqlDataAdapter(cmd);SqlCacheDependency dependency = new SqlCacheDependency(cmd);thedatatable = new DataTable();dapt.Fill(thedatatable);
HttpContext.Current.Cache.Insert("categories", thedatatable, dependency, DateTime.Now.AddHours(6), Cache.NoSlidingExpiration);}
}
elsethedatatable = HttpContext.Current.Cache["categories"] as DataTable; return thedatatable;}
The code is very easy to follow. The most important bit is the following lines. We create an SQL cache Dependency that is linked with the specific command object.
Then when I insert the item in the cache I specify the dependency object.SqlCacheDependency dependency = new SqlCacheDependency(cmd);
HttpContext.Current.Cache.Insert("categories", thedatatable, dependency, DateTime.Now.AddHours(6), Cache.NoSlidingExpiration);
Set the ObjectDataSource SelectMethod attribute to the methods GetCategories and the TypeName to the Caching.DataAccess.The markup follows below
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetCategories" TypeName="Caching.DataAcess"></asp:ObjectDataSource>Then set the DataSourceID property of the GridView control to the ObjectDataSource1 control.
18) In order for this to work we must add another item in the website.Add a global application class file, Global.asax. In the Application_Start() event we should type,
<%@ Import Namespace = "System.Data.SqlClient" %>.......
void Application_Start(object sender, EventArgs e){
// Code that runs on application startup string conn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
SqlDependency.Start(conn);}
Run your application.You will see the data from the table being displayed on the screen.Refresh the browser window and you will see that the data is being served from the cache.Leave the window open.Make some changes in the Categories table and refresh the page. You will see the updated data being reflected on the screen causing the cache to flush due to the changes in the resultset and the sql cache dependency.
Email me if you need the code.
Hope it helps!!!