In this video, we will discuss about removing data from cache, when the data in the table from which it has come, has changed. Let us understand cache dependency on sql server database table, with an example.

Copy and paste the following code in WebForm1.aspx.csprotected void btnGetData_Click(object sender, EventArgs e){ // Check if the DataSet is present in cache if (Cache["ProductsData"] != null) {// If data available in cache, retrieve and bind it to gridview control gvProducts.DataSource = Cache["ProductsData"]; gvProducts.DataBind();

lblStatus.Text = "Data retrieved from cache @ " + DateTime.Now.ToString(); }else { // Read connection string from web.config filestring CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;// Enable change notifications on the database, // so that when the data changes the cached item will be removed System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(CS);// Enable change notifications on the database table, // so that when the data changes the cached item will be removed System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(CS, "tblProducts");

In the above code, notice that, to enable SqlCacheDependency on the database and table, we are using "EnableNotifications()" and "EnableTableForNotifications()" methods, as shown below.System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(CS)System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(CS, "tblProducts");

Alternatively, to enable SqlCacheDependency on the database and table, we can use a command line tool, aspnet_regsql.exe. Open visual studio command prompt and execute the following 2 commands

Notice that, we have set pollTime="2000". pollTime attribute specifies the frequency, at which, asp.net is going to check the database for changes. This time is in milli-seconds. Since, we have specified 2000 milli-seconds, asp.net is going to check the database for changes every 2 seconds. The default is 500 milli-seconds.

Run the application and when we click "Get Products" button for the first time, data is loaded from database. Click again, the data should be loaded from cache. Now, execute the following UPDATE query.Update tblProducts set Name='Laptops' where Id = 1

Now, click the button again, and notice that the data is loaded from the database, as existing data is removed from cache.