How to implement SQL Caching in ASP.NET (Poll based SQL Cache dependency)?

Sheonarayan
Posted by in ASP.NET category on for Advance level | Points: 250 | Views : 73129 red flag
Rating: 5 out of 5  
 6 vote(s)

In this article, we are going to learn how to implement SQL Caching in ASP.NET using Poll based SQL Cache dependency. The push based SQL Cache dependency shall be covered in other article. In poll based, SQL Cache dependency checks for the updated data after every specified duration.
With the help of Caching, we can retain pages or data across HTTP requests, so that we can reuse them without recreating them.

Introduction


ASP.NET allows us to use the SqlCacheDependency class to create a cache item dependency on a table in a database. It has the ability to automatically invalidate a cached data object when the related data is modified in the database. This feature is supported in from SQL Server 2005+ versions.


To implement SQL Cache where data is cached in the SQL Server instead of server memory, we can follow this approach.

Recently, I have written a .NET How to ebook with source code and video tutorials on frequently faced .NET problems, click here to get this.

Below is my database (DemoDatabase) structure before enabling SQL Cache Dependency.

DATABASE STRUCTURE BEFORE ENABLING SQL CACHE DEPENDENCY





ENABLIING SQL CACHE DEPENDENCY FOR THE DEMODATABASE




Run the aspnet_regsql.exe command from the Visual Studio command prompt, you should open the Visual Studio Command prompt with Administrative privilege.

(Command name and options are case sensitive; you need to only change the databases servername, userid, and password and database name to enable SQL Cache dependency in your database).

Here
      i. –S = name of the server (as sunita-pc\sqlexpress in the above screen shot)
      ii. –U = database username (as demo in the above screen shot)
      iii. –P = database password (as demo in the above screen shot)
      iv. –d = database name (as DemoDatabase in the above screen shot)
      v. –ed = enable the database for SQL Cache dependency

DATABASE STRUCTURE AFTER ENABLING SQL CACHE DEPENDENCY


Notice the Stored Procedures folder. You will find many stored procedure added starting with “AspNet_SqlCache…”.



Now, we need to enable the database table for which we want to cache the data, so for that again we need to run the aspnet_regsql.exe command.

ENABLING DATABASE TABLE FOR SQL CACHE DEPENDENCY




This command is almost similar as the previous command of enabling database for SQL Cache dependency; however we need to add –t (table name to enable cached dependency on) and then –et (enable table) at the last.



Notice that a new table “AspNet_SqlCacheTablesForChangeNotification” has been added.

LISTING DATABASE TABLES THAT HAS SQL CACHE DEPDENCY ENABLED



Above command is also same as earlie, we have kept –lt (list tables) at the end after the database name. Enabling SQL Cache dependency on the table adds one record into the “AspNet_SqlCacheTablesForChangeNotification” table.



DISABLING SQL CACHE DEPENDENCY ON DATABASE TABLE


In case we want to disable SQL Cache for a particular table, we can run use below command. In this case we are disabling SQL Cache for PersonalDetail table.



Just replace –et to –dt at the last of the command.

WEB.CONFIG

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="ConnStr" connectionString="Data Source=SUNITAPC\
        SQLEXPRESS;Initial Catalog=DemoDatabase;Persist Security Info=True;User
        ID=demo;Password=demo;"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.0"/>
    <caching>
      <sqlCacheDependency enabled="true">
        <databases>
          <add name="DemoDatabase" connectionStringName="ConnStr" pollTime="500"/>
        </databases>
      </sqlCacheDependency>
    </caching>
  </system.web>
</configuration>
Once our database and table are configured for SQL Cache dependency, we need to specify Caching tag (with enabled=true) in web.config file and then add the databases on which caching needs to be enabled. Ensure that the database name attribute value (name=”DemoDatabase”) is similar to the name of the database specified into the connectionString (ConnStr) and then specify the pollTime (in millisecond). Polltime ensures that SQL Cache dependency checks for the updated data after every specified duration (in this case .5 seconds).

Let’s create a sample page to demonstrate SQL Cache dependency.

ASPX PAGE

<form id="form1" runat="server">
<div>
    <p>
        <asp:Label ID="lblMessage" runat="server" EnableViewState="false" /></p>
    <asp:GridView ID="GridView1" runat="server" EnableViewState="false" />
</div>
</form>

CODE BEHIND

protected void Page_Load(object sender, EventArgs e)
{
    lblMessage.Text = DateTime.Now.ToString();
    if (!IsPostBack)
    {
        GetData();
    }
}

private void GetData()
{
    DataTable table = new DataTable();
    // Check for cache if it is then get it from cache
    if (Cache["PersonalDetailData"] != null)
    {
        table = (DataTable)Cache["PersonalDetailData"];
        lblMessage.Text += "<br />Being loaded from the Cache";
    }
    else
    {
        // get the connection
        using (SqlConnection conn = new SqlConnection(_connStr))
        {
            // write the sql statement to execute
            string sql = "SELECT AutoId, FirstName, LastName, Age, Active FROM PersonalDetail ORDER By AutoId";
            // instantiate the command object to fire
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                // get the adapter object and attach the command object to it
                using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
                {
                    // fire Fill method to fetch the data and fill into DataTable
                    ad.Fill(table);
                }
            }
            SqlCacheDependency dependency = new SqlCacheDependency("DemoDatabase",
            "PersonalDetail");
            // store data into Cache
            Cache.Insert("PersonalDetailData", table, dependency);
            lblMessage.Text += "<br />Being loaded from the database";
        }
    }
    // specify the data source for the GridView
    GridView1.DataSource = table;
    // bind the data now
    GridView1.DataBind();
}
In the code behind, to insert the data into Cache, first instantiate the SqlCacheDependency object by passing the database entry name (this name is name of the database defined in the databases element of SqlCacheDependency in the web.config file) and table name (PersonalDetail), then add it as dependency into the Cache using Cache.Insert method.

OUTPUT


When we run the page, the first request to this page looks like this where the GridView data is coming from the database.



The second request loads the data from the cache and all remaining request to this page loads the GridView data from the Cache unless the “PersonalDetail” table data has been modified.

Request after modifying the last record’s FirstName




CONCLUSION:


When we cache data from database, the data remains in the cache until it’s source is modified in the database. With the help of this we can improve the performance of ASP.NET applications to the large extent and can also solve the problem of ASP.NET distributed cache scenarios. 
Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com

Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Chvrsri on: 6/1/2011 | Points: 25
Great Post ! 5 From me Sheo !!!
Posted by: Karthikanbarasan on: 6/1/2011 | Points: 25
Excellent article... 5 from me...
Posted by: SheoNarayan on: 6/1/2011 | Points: 25
Thanks Chvrsri and Karthik !
Posted by: Mauryavijai on: 6/1/2011 | Points: 25
Great Article ..
Posted by: Chainv on: 7/15/2011 | Points: 25
Very nice article.. Thanks a lot
Posted by: Robinhames on: 9/17/2012 | Points: 25
Good post. I have recently developed a loosely coupled Cache Provider using Dependency Injection, which supports the SQL Cache Dependency described here.

http://rhamesconsulting.com/category/cache-provider/cache-provider-part-1/
and
http://rhamesconsulting.com/category/cache-provider/cache-provider-part-2/

Hope somebody finds this useful!

Login to post response

Comment using Facebook(Author doesn't get notification)