Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Reload data into cache automatically when data in the table changes - Part 132

Suggested Videos
Part 129 - Cache dependency on files
Part 130 - Refreshing cache automatically, when cached data is removed
Part 131 - Cache dependency on sql server database table

In Part 130 of video tutorial, we discussed about reloading data into cache automatically, when the xml file from which the data was initially retrieved, has changed. 

In this video, we will discuss about reloading data into cache automatically when data in the underlying database table has changed. Please watch Part 131, before proceeding with this video.

To load data automatically into cache, when cached data is removed, we need to define a callback method. This callback method will be invoked when the respective item is removed from cache. So, the code to reload and re-cache data can be written in this method. The callback method signature should match, with the signature of "CacheItemRemovedCallback" delegate. The call back method is defined below.
public void CacheItemRemovedCallbackMethod(string key, object value, CacheItemRemovedReason reason)
    string CS = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

    SqlConnection con = new SqlConnection(CS);
    SqlDataAdapter da = new SqlDataAdapter("spGetProducts", con);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;
    DataSet ds = new DataSet();

    CacheItemRemovedCallback onCacheItemRemoved = new CacheItemRemovedCallback(CacheItemRemovedCallbackMethod);

    SqlCacheDependency sqlDependency = new SqlCacheDependency("Sample", "tblProducts");
    Cache.Insert("ProductsData", ds, sqlDependency, DateTime.Now.AddHours(24), Cache.NoSlidingExpiration,
        CacheItemPriority.Default, onCacheItemRemoved);

Now, create an instance of "CacheItemRemovedCallback" delegate, and to the constructor pass the name of the callback method, that should be executed automatically when, the cached item is removed.
CacheItemRemovedCallback onCacheItemRemoved = new CacheItemRemovedCallback(CacheItemRemovedCallbackMethod);

In Part 131, to cache data, we used cache object's, Insert() method that takes 3 parameters, as shown below. 
Cache.Insert("ProductsData", ds, sqlDependency);

Instead, let's use the overloaded version that takes 7 parameters and pass "onCacheItemRemoved" as an argument for "CacheItemRemovedCallback" parameter.
Cache.Insert("ProductsData", ds, sqlDependency, DateTime.Now.AddHours(24), Cache.NoSlidingExpiration,
    CacheItemPriority.Default, onCacheItemRemoved);

That's it. We are done. Now, please run the application. When "Get Data" button is clicked for the first time, the data is loaded from database. Once the data is loaded into cache, we always get it from cache when we click "Get Data". Now, execute an update statement on the database table. Notice that, when we click "Get Data" button now, we still get data from cache, but notice that, the updated data is loaded.


  1. Sir Please post video on WCF and other advance topic's

  2. in your tutorials you told a lot about cachying..

    i want to know is in your tutorial you told how to cache multiple instances of a single page using ouputcache but i want to know is how to do so using data cachying.

    as i have a snerio that i have a pagination that i had made my self all using server side code this pagination have multiole instances of page ..the thing i want to do is i want to cache all instances of the page and refresh cache when any change in table in database using data cachying..

    can you tell or give a hint how to do so ..
    it will be great if you do so......


It would be great if you can help share these free resources