In nowadays, you won’t find any large scale application that doesn’t make use of some kind of Caching technique. Caching application data can boost application performance and of course improves User interface experience. In ASP.NET there are several kind of caching techniques such as Web Page caching or specific data caching. In other words, you can cache and serve the whole Page itself or only particular application data. Caching though can be quite tricky, considering the fact that cached data can be out of date. This is actually the biggest problem you are called to solve when using caching. In this post, we are going to see how to cache data, retrieved from an SQL Server table and how to handle certain changes on this particular table. The scenario is this: We are going to create a GridView control in a “.aspx” Page and fill this control with some data retrieved from a Customers table using a Stored Procedure. We will cache these data and every time we reload the page, a cached copy will be returned. But what if a “Customers” record is updated or a new record is inserted in this table? Well, the ideal solution would be to let SQL Server itself, instantly notify the ASP.NET Application Server that the current cached copy is out of date and let him reload a fresh copy of the records into the cache. We have just described an automated Cache reloading technique. When the client requests again the page, he will receive the updated cached copy. Let’s start.
First of all, let’s create our database. Open SQL Server Management Studio and create a new database, named “Store”. Create a new table named “Customers” and make sure you add some records as follow.
We need to configure both the Store database and it’s Customers table so that the ASP.NET will get notified when a change happens on this particular table. To do this, open Developer Command Prompt for VS2012 and typed the following commands.
aspnet_regsql -ed -E -d Store aspnet_regsql -et -E -d Store-t Customers
Now, create a Stored Procedure that fetches all Customers records. On purpose, we will make this procedure last at least 3 seconds, so that later we are able to see clearer where our data came from, the database itself or the cache.
CREATE PROCEDURE spGetCustomers AS BEGIN WAITFOR DELAY '00:00:03' SELECT * FROM Customers END
Open Visual Studio and create a new Empty ASP.NET Web Application. Add a Web Form page named “Default.aspx” and paste the following code.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="AspNetCaching.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<fieldset>
<legend>
<asp:Label ID="lblCustomers" runat="server" Text="Customers"></asp:Label></legend>
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
<div id="divInfo" style="background-color:beige;width:416px">
Number of Customers:
<asp:Label ID="lblNumberOfCustomers" ForeColor="Red" runat="server" Text=""></asp:Label>
<br />
Data retrieved from:
<asp:Label ID="lblDataRetrievedFrom" ForeColor="Red" runat="server" Text=""></asp:Label>
<br />
Time elapsed:
<asp:Label ID="lblTimeElapsed" runat="server" ForeColor="Red" Text=""></asp:Label>
</div>
<br />
<asp:Button ID="btnReload" runat="server" Text="Reload" OnClick="btnReload_Click" />
</fieldset>
<br />
</div>
</form>
</body>
</html>
It’s a very simple Page, having a GridView control, some labels and a button. Label controls will show how many customers exist on the Customers table, where data came from and the time elapsed to reload the page. Before writing the code behind file, let us add some configuration into the Web.config file. We need to add a connectionString element pointing to our database and the necessary sqlCacheDependency settings.
<configuration>
<connectionStrings>
<add name="StoreConnectionString" connectionString="Data Source=.; database=Store; Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency pollTime="2000" enabled="true">
<databases>
<add name="Store" connectionStringName="StoreConnectionString"/>
</databases>
</sqlCacheDependency>
</caching>
<compilation debug="true"/>
</system.web>
</configuration>
Switch to the code behind file and add the following method first.
public void CacheItemRemovedCallbackMethod(string key, object value, CacheItemRemovedReason reason)
{
string CS = ConfigurationManager.ConnectionStrings["StoreConnectionString"].ConnectionString;
lock (thisLock)
{
using (SqlConnection con = new SqlConnection(CS))
{
SqlDataAdapter da = new SqlDataAdapter("spGetCustomers", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds);
CacheItemRemovedCallback onCacheItemRemoved = new CacheItemRemovedCallback(CacheItemRemovedCallbackMethod);
SqlCacheDependency sqlDependency = new SqlCacheDependency("Store", "Customers");
Cache.Insert("CustomersData", ds, sqlDependency, DateTime.Now.AddHours(24), Cache.NoSlidingExpiration,
CacheItemPriority.Default, onCacheItemRemoved);
return;
}
}
This method is the Callback Function that the SQL Server will notify, when a change on the Customers table occur. I highlighted the most important lines. The method is pretty straight forward. It reloads the data from the table by executing the stored procedure we previously wrote and then sets the SqlCacheDependency for our cached data. In the Cache.Insert method, we passed the name of our cached item (“CustomersData”), our data object we want to cache (ds), the SqlCacheDependency object pointing to the Customers table, the duration we want to cache our data for, some other settings and last but not least the Callback function again. The actual method that reloads and cache all Customers records is the following. You will notice that it’s pretty much the same code.
private void ReloadAndCacheCustomers()
{
// Read connection string from web.config file
string CS = ConfigurationManager.ConnectionStrings["StoreConnectionString"].ConnectionString;
lock (thisLock)
{
using (SqlConnection con = new SqlConnection(CS))
{
SqlDataAdapter da = new SqlDataAdapter("spGetCustomers", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds);
CacheItemRemovedCallback onCacheItemRemoved = new CacheItemRemovedCallback(CacheItemRemovedCallbackMethod);
// Build SqlCacheDependency object using the database and table names
SqlCacheDependency sqlDependency = new SqlCacheDependency("Store", "Customers");
// Pass SqlCacheDependency object, when caching data
Cache.Insert("CustomersData", ds, sqlDependency, DateTime.Now.AddHours(24), Cache.NoSlidingExpiration,
CacheItemPriority.Default, onCacheItemRemoved);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}
Paste the following code in the Page_Load event so that we can see the GridView control filled when we start the application.
public partial class Default : System.Web.UI.Page
{
public static Object thisLock = new Object();
protected void Page_Load(object sender, EventArgs e)
{
DateTime timeStart = DateTime.Now;
if (!IsPostBack)
{
ReloadAndCacheCustomers();
DateTime timeLoaded = DateTime.Now;
TimeSpan timeElapsed = timeLoaded.Subtract(timeStart);
lblDataRetrievedFrom.Text = " database..";
lblNumberOfCustomers.Text = GridView1.Rows.Count.ToString();
lblTimeElapsed.Text = timeElapsed.TotalMilliseconds.ToString() + " milliseconds" +
" (" + timeElapsed.Seconds.ToString() + " sec)";
}
}
// Code omitted
There is only one method left to write, the Click event handler for the button control. This method will check if there is a cached copy of our data and if so, it will bind it’s data to the DataSource property of the GridView control. Otherwise, it will reload and re-cache the data.
protected void btnReload_Click(object sender, EventArgs e)
{
DateTime timeStart = DateTime.Now;
// Check if the DataSet is present in cache
if (Cache["CustomersData"] != null)
{
// If data available in cache, retrieve and bind it to gridview control
GridView1.DataSource = (DataSet)Cache["CustomersData"];
GridView1.DataBind();
DateTime timeLoaded = DateTime.Now;
TimeSpan timeElapsed = timeLoaded.Subtract(timeStart);
lblDataRetrievedFrom.Text = " cache..";
lblNumberOfCustomers.Text = GridView1.Rows.Count.ToString();
lblTimeElapsed.Text = timeElapsed.TotalMilliseconds.ToString() + " milliseconds" +
" (" + timeElapsed.Seconds.ToString() + " sec)";
}
else
{
ReloadAndCacheCustomers();
DateTime timeLoaded = DateTime.Now;
TimeSpan timeElapsed = timeLoaded.Subtract(timeStart);
lblDataRetrievedFrom.Text = " database..";
lblNumberOfCustomers.Text = GridView1.Rows.Count.ToString();
lblTimeElapsed.Text = timeElapsed.TotalMilliseconds.ToString() + " milliseconds" +
" (" + timeElapsed.Seconds.ToString() + " sec)";
}
}
We are ready to go. Build and run your application. The first time you load the page you will notice a 3 seconds delay, duo to the spGetCustomers stored procedure delay.
Now add a new record in the Customers Table or update a record’s column value and finally press the reload button.
Almost instantly! You may see some milliseconds in your solution, but the fact is that cache were already updated from your SQL Server and hence, when you reloaded the page you didn’t have to call the stored procedure at that particular moment (waiting for 3 seconds) but it has been already done for you from the Callback function. That’s it, we finished. I hope you understood how SQL Server caching and automated cache reloading works. You can download the project we built from here. I hope you enjoyed the post! Make sure you follow this blog to get notified for new posts!
chsakell's Blog 
Great article man. Once again a really great walkthrough.
hello.
Why not to place the: if (Cache[“CustomersData”] != null) in the ReloadAndCacheCustomers()?
it seems like everytime the page load it goes to the ReloadAndCacheCustomers() and make a travel to the SQL Server doesn’t matter if the Cache[“CustomersData”] was already created for a previous user.
Hi Pablo,
You made a good point there, thank you very much.
Chris S.
Great article! Is it possible to do that when using Entity Framework?
hi, following your steps. from web.config you said “Switch to the code behind file and add the following method first.” where particularly code behind? is web config have a code behind? and additionally, with this line of syntax “aspnet_regsql -ed -E -d Store
aspnet_regsql -et -E -d Store-t Customers”, Im wondering what if you are fetching from different table a inner join perhaps what should i do with that syntax? and how can i implement cache from different table? Thank you.