Cache dependency on sql server database table - Part 131

Suggested Videos
Part 128 - AbsoluteExpiration, SlidingExpiration, and CacheItemPriority
Part 129 - Cache dependency on files
Part 130 - Refreshing cache automatically, when cached data is removed

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.



First create "tblProducts" table and populate with sample data using the script below
CREATE TABLE [tblProducts]
(
[Id] [int] PRIMARY KEY IDENTITY,
[Name] [nvarchar](50) NULL,
[Description] [nvarchar](250) NULL
)

Insert into tblProducts values ('Laptops', 'Dell Laptops')
Insert into tblProducts values ('iPhone', 'iPhone 4S')
Insert into tblProducts values ('LCD TV', 'Samsung LCD TV')
Insert into tblProducts values ('Desktop', 'HP Desktop Computer')



Create an asp.net web application project. Copy and paste the following HTML on WebForm1.aspx
<div style="font-family: Arial">
    <asp:Button ID="btnGetData" runat="server" Text="Get Data" 
    OnClick="btnGetData_Click" />
    <br />
    <br />
    <asp:GridView ID="gvProducts" runat="server">
    </asp:GridView>
    <br />
    <asp:Label ID="lblStatus" runat="server" Font-Bold="true">
    </asp:Label>
</div>

Copy and paste the following code in WebForm1.aspx.cs
protected 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 file
        string 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");

        SqlConnection con = new SqlConnection(CS);
        SqlDataAdapter da = new SqlDataAdapter("select * from tblProducts", con);
        DataSet ds = new DataSet();
        da.Fill(ds);

        // Build SqlCacheDependency object using the database and table names
        SqlCacheDependency sqlDependency = new SqlCacheDependency("Sample", "tblProducts");

        // Pass SqlCacheDependency object, when caching data
        Cache.Insert("ProductsData", ds, sqlDependency);

        gvProducts.DataSource = ds;
        gvProducts.DataBind();
        lblStatus.Text = "Data retrieved from database @ " + DateTime.Now.ToString();
    }
}

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

To enable SqlCacheDependency on the "Sample" database:
aspnet_regsql -ed -E -d Sample

To enable SqlCacheDependency on "tblProducts" table in "Sample" database:
aspnet_regsql -et -E -d Sample -t tblProducts

If you need to understand the purpose of -et, -E, -d, -t, use the help, by typing the following command
aspnet_regsql /?

Finally, in web.config specify database connection string and SqlCacheDependency. 
<connectionStrings>
  <add name="DBCS" connectionString="data source=.; database=Sample; integrated security=SSPI"/>
</connectionStrings>
<system.web>
  <caching>
    <sqlCacheDependency pollTime="2000" enabled="true">
      <databases>
        <add name="Sample" connectionStringName="DBCS"/>
      </databases>
    </sqlCacheDependency>
  </caching>
</system.web>

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.