Using Sql Cache Dependency

It is often required to expire an object in Cache that is based on a data in the Sql Server database whenever the data in the database changes.

Asp.Net provides SqlDependency and SQlCacheDependency classes for this purpose. Do remember even SQL Server database must provide support for notification so that it notifies Asp.Net whenever data in the database is modified. The way SQL Server does it varies from version to version. I am using SQL Server 2005 for this blog. In case you are using older versions, please check MSDN for an article related to it.

Here are all the steps to take to ensure that Asp.Net expires an object in data cache whenever relevant data in the database is modified.

Getting Sql Server Database Ready For Change Notification

In order for SqlCacheDependency to work, first ensure the database that contains the required data is enabled for change notification. You can check whether a database is enabled for notification by using the following SQL command in Sql Server.


SELECT NAME, IS_BROKER_ENABLED
FROM   SYS.DATABASES

If it is not enabled for notification, give the following command to enable notification service for a specific database.


ALTER DATABASE <database>  SET ENABLE_BROKER

Using SQLDependency class

SQLDependency represents a query notification dependency between an application and an instance of SQL Server 2005.

The best place to register for notification is global.asax, Use the following code in global.asax to start listening for change notification from Sql Server.

<%@ Application Language="C#" %>
<script runat="server">
    void Application_Start(object sender, EventArgs e) 
    {
        // Assuming ConnectionString property in Database class contains the connection string to connect to Sql Server
        System.Data.SqlClient.SqlDependency.Start(Database.ConnectionString);
    }
    void Application_End(object sender, EventArgs e) 
    {
        System.Data.SqlClient.SqlDependency.Stop(Database.ConnectionString);
    }
    // remaining code 
</script>

Using SqlCacheDependency class

In order to establish a relationship between an object stored in an ASP.NET application's Cache and a specific SQL Server database table or the results of SQL Server 2005 query, we need to use SqlCacheDependency class.

The following Asp.Net page shows how to register a query or a table for notfication.

<%@ Page Language="C#" %>
<%@ Import Namespace = "System.Data.SqlClient" %>
<%@ Import Namespace = "System.Data" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dt = (DataTable) Cache.Get("books");
        if (dt == null)
        {
            lblMsg.Text = "Creating cache";
            using (SqlConnection cn = new SqlConnection(Database.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("select title,author,price from dbo.books", cn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                SqlCacheDependency dependency = new SqlCacheDependency(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "books");
                dt = ds.Tables[0];
                Cache.Insert("books", dt, dependency);
            }
        }
        else
            lblMsg.Text = "Using Existing Cache";

        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <p />
        <asp:Label ID="lblMsg" runat="server" Text=""  EnableViewState ="false"></asp:Label>
    
    </div>
    </form>
</body>
</html>

Unless the query satisfies the requirements for query notification, query notification doesn't work. For example, if you don't qualify tablename with owner name then query notification doesn't work. For the complete list of requirements for Sql Query in query notification refer to Creating a Query for Notification in MSDN.

Testing

Run the above page to get the list of books from Books table. As you run the page first time, you should see message Creating Cache in the label. Refresh the page to see message Using Existing Cache.

Modify the contents of BOOKS table. A change to the data that is being used in the cache object causes object in cache to be expired by Asp.Net.

Any request for the page after the change to data in database and subsequent expiration of object from cache, will cause object to be recreated with the new data in the database. This also results in message Creating Cache again.