Click here to Skip to main content
13,197,796 members (43,565 online)
Click here to Skip to main content
Add your own
alternative version


15 bookmarked
Posted 20 Jun 2014

Web Caching with SqlCacheDependency Simplified

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


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


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:


You have your connection string in web.config:

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

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

      <sqlCacheDependency enabled="true">
          <add name="NorthwindCache" 

          connectionStringName="default" pollTime="500"/>

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);

            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";

            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();

                SetCacheData(cacheItemName, dtTemp, connString, tableName);
                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.


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


About the Author

Technical Lead Airtel Broadband
India India
.net developer

You may also be interested in...


Comments and Discussions

Questionany DB changes needed for Northwind Pin
prams_hi19-May-17 13:11
memberprams_hi19-May-17 13:11 
im trying to implement this code but the table changes are not reflected back on the browser page unless im manually refreshing the page then its reflected. what did i miss doing ? are there any database changes other than service broker enable.. any help plzz
QuestionSQL Server version Pin
tharnendil25-Sep-15 0:37
membertharnendil25-Sep-15 0:37 
AnswerRe: SQL Server version Pin
sazakir26-Oct-15 1:38
membersazakir26-Oct-15 1:38 
QuestionYour code is using extremely outdated methods Pin
sevin76-Jan-15 7:59
membersevin76-Jan-15 7:59 
AnswerRe: Your code is using extremely outdated methods Pin
PIEBALDconsult6-Jan-15 8:21
protectorPIEBALDconsult6-Jan-15 8:21 
GeneralRe: Your code is using extremely outdated methods Pin
sevin76-Jan-15 22:56
membersevin76-Jan-15 22:56 
GeneralRe: Your code is using extremely outdated methods Pin
sazakir6-Jan-15 23:54
membersazakir6-Jan-15 23:54 
GeneralRe: Your code is using extremely outdated methods Pin
sevin77-Jan-15 3:52
membersevin77-Jan-15 3:52 
SuggestionRead-through to avoid cache miss and reloading data Pin
sameer@alachisoft.com28-Dec-14 1:35
membersameer@alachisoft.com28-Dec-14 1:35 
GeneralRe: Read-through to avoid cache miss and reloading data Pin
sazakir7-Jan-15 0:04
membersazakir7-Jan-15 0:04 
QuestionHi, very clear and simple, thanks! Pin
Sigourney_Weaver24-Nov-14 10:54
memberSigourney_Weaver24-Nov-14 10:54 
AnswerRe: Hi, very clear and simple, thanks! Pin
Sigourney_Weaver25-Nov-14 3:59
memberSigourney_Weaver25-Nov-14 3:59 
AnswerRe: Hi, very clear and simple, thanks! Pin
slishnevsky26-Nov-14 6:18
memberslishnevsky26-Nov-14 6:18 
QuestionDo you need Service Broker enabled on the database to run this project? Pin
Member 790852914-Jul-14 4:22
memberMember 790852914-Jul-14 4:22 
AnswerRe: Do you need Service Broker enabled on the database to run this project? Pin
sazakir15-Jul-14 0:40
membersazakir15-Jul-14 0:40 
GeneralRe: Do you need Service Broker enabled on the database to run this project? Pin
Member 790852915-Jul-14 7:51
memberMember 790852915-Jul-14 7:51 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171020.1 | Last Updated 20 Jun 2014
Article Copyright 2014 by sazakir
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid