Click here to Skip to main content
Click here to Skip to main content
Go to top

Web Caching with SqlCacheDependency Simplified

, 20 Jun 2014
Rate this:
Please Sign up or sign in to vote.
Using SqlCacheDependency with Web caching

Introduction

This tip explains using the SqlCacheDependency with Web Caching in a simple way.

Background

I had a requirement to implement server data push to clients without clients having to poll the server. We decided to use the SignalR framework. But in the business layer with a timer, I wanted to query the database only when there are data changes. Going through many articles online on SqlCacheDependency, SqlQueryNotifications, I had difficultly understanding a proper way to implement all the layers. At last, I came out with the below solution without having to use the SqlQueryNotification.

Using the Code

The sample is developed in VS 2012 and uses the Northwind database.

The first step will be to enable broker in SQL Server with the below command:

ALTER DATABASE [<db_name>] SET ENABLE_BROKER WITH NO_WAIT

You have your connection string in web.config:

<connectionStrings>
    <add name="default" connectionString="server=.; 
    initial catalog=NorthWind; integrated security=true;" />
</connectionStrings>

Next, add the following in web.config, under <system.web>:

<caching>
      <sqlCacheDependency enabled="true">
        <databases>
          <add name="NorthwindCache" 
          connectionStringName="default" pollTime="500"/>
        </databases>
      </sqlCacheDependency>
</caching>   

The code on aspx page load is below, which has a GridView object named grvCategory and a Label named lblMessage.

protected void Page_Load(object sender, EventArgs e)
{
            bool isDataFromCache = false;

            grvCategory.DataSource = DbManager.GetCategory(out isDataFromCache);
            grvCategory.DataBind();

            lblMessage.Text = isDataFromCache ? 
            "Cache Data : " : "Data refreshed at ";
            lblMessage.Text += DateTime.Now.ToString();
}

The dbManager class retrieves data from the database. It also manages the cache objects.

public class DbManager
{
        private static object GetCacheData(string cacheItemName)
        {
            return HostingEnvironment.Cache.Get(cacheItemName);

        }

        private static void SetCacheData
        (string cacheItemName, object dataSet, string connString, string tableName)
        {
            string cacheEntryname = "NorthwindCache";

            SqlDependency.Start(connString);
            
            SqlCacheDependencyAdmin.EnableNotifications(connString);
            SqlCacheDependencyAdmin.EnableTableForNotifications(connString, tableName);

            SqlCacheDependency dependency = new SqlCacheDependency(cacheEntryname, tableName);
            HostingEnvironment.Cache.Insert(cacheItemName, dataSet, dependency);
        }

        public static DataTable GetCategory(out bool isDataFromCache)
        {
            string sqlQuery = "SELECT [CategoryID],
            [CategoryName] FROM [dbo].[Categories]";
            string tableName = "Categories";

            string connStringName = "default";
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings[connStringName].ToString();

            isDataFromCache = false;
            DataTable dtTemp = null;
            string cacheItemName = sqlQuery;

            object obj = GetCacheData(cacheItemName);
            dtTemp = (DataTable)obj;

            if (dtTemp == null)
            {
                SqlConnection cnMain = new SqlConnection(connString);
                SqlDataAdapter da = new SqlDataAdapter(sqlQuery, cnMain);

                dtTemp = new DataTable();
                da.Fill(dtTemp);

                SetCacheData(cacheItemName, dtTemp, connString, tableName);
            }
            else
            {
                isDataFromCache = true;
            }

            return dtTemp;
        }
}

After running the web page, the initial data is taken from database and subsequent page refresh fetches data from the cache as displayed in the label control of the page. The cache expires as soon as you insert or update in the Categories table.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

sazakir
Product Manager
India India
.net developer

Comments and Discussions

 
QuestionDo you need Service Broker enabled on the database to run this project? PinmemberMember 790852914-Jul-14 4:22 
AnswerRe: Do you need Service Broker enabled on the database to run this project? Pinmembersazakir15-Jul-14 0:40 
GeneralRe: Do you need Service Broker enabled on the database to run this project? PinmemberMember 790852915-Jul-14 7:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 20 Jun 2014
Article Copyright 2014 by sazakir
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid