Web Caching with SqlCacheDependency Simplified

• Download source - 7.8 KB

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 SQL 2012. It 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.