Caching SQL Server data in ASP.NET

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.
cacheSqlData_01
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

enable-sql-server-dependency
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.
cacheSqlData_02
Now add a new record in the Customers Table or update a record’s column value and finally press the reload button.
cacheSqlData_03
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!

Advertisements


Categories: ADO.NET, ASP.NET

Tags: ,

4 replies

  1. Great article man. Once again a really great walkthrough.

  2. 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.

  3. Great article! Is it possible to do that when using Entity Framework?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Chara Plessa

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Kumikoro

A Front End Developer's Blog

Muhammad Hassan

Full Stack Developer | ASP.NET | MVC | WebAPI | Advanced Javascript | AngularJS | Angular2 | C# | ES6 | SQL | TypeScript | HTML5 | NodeJS, MS candidate @LUMS, Grad & EX-Adjunct Faculty @NUCES-FAST, seasonal blogger & open-source contributor. Seattle, WA.

Software Engineering

Web development

IEvangelist

.NET, ASP.NET, C#, MVC, TypeScript, AngularJS

leastprivilege.com

Dominick Baier on Identity & Access Control

Happy DotNetting

In Love with Technology

Knoldus

Knols of experience to your advantage

knowshnet

Search - Read - Request - Share

Rahul's space

Learn, Share and Grow with me !

Dhananjay Kumar

Developer Evangelist @Infragistics | MVP @Microsoft |

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Conficient Blog

Random bits of tech from @conficient

Code! Code! Code!

SOLID & KISS

Code Wala

Designing and coding

Microsoft Mentalist

A way to start with Microsoft Technologies

Tony Sneed's Blog

A glimpse into the lives of Tony & Zuzana Sneed

Sriramjithendra Nidumolu

Personal Notes of Sriramjithendra

%d bloggers like this: