The sample application uses a single ASP.NET Web page to display product information from the AdventureWorks SQL Server database in a GridView control. When the page loads, the code writes the current time to a Label control. It then defines a SqlCacheDependency object and sets properties on the Cache object to store the cache data for up to three minutes. The code then connects to the database and retrieves the data. When the page is loaded and the application is running ASP.NET will retrieve data from the cache, which you can verify by noting that the time on the page does not change. If the data being monitored changes, ASP.NET invalidates the cache and repopulate the GridView control with fresh data, updating the time displayed in the Label control.

Follow these steps to create and run the sample application:

1. Create a new ASP.NET Web site.

2. Add a Label and a GridView control to the Default.aspx page.

3. Open the page's class module and add the following directives:

   Visual Basic

   Option Strict On
   Option Explicit On
   
   Imports System.Data.SqlClient
   using System.Data.SqlClient;
   using System.Web.Caching;

4. Add the following code in the page's Page_Load event:

   Visual Basic

   Protected Sub Page_Load(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles Me.Load
   
       Label1.Text = "Cache Refresh: " & _
          Date.Now.ToLongTimeString()
   
       ' Create a dependency connection to the database
       SqlDependency.Start(GetConnectionString())
   
       Using connection As New SqlConnection(GetConnectionString())
           Using command As New SqlCommand(GetSQL(), connection)
               Dim dependency As New SqlCacheDependency(command)
   
               ' Refresh the cache after the number of minutes
               ' listed below if a change does not occur.
               ' This value could be stored in a configuration file.
               Dim numberOfMinutes As Integer = 3
               Dim expires As Date = _
                   DateTime.Now.AddMinutes(numberOfMinutes)
   
               Response.Cache.SetExpires(expires)
               Response.Cache.SetCacheability(HttpCacheability.Public)
               Response.Cache.SetValidUntilExpires(True)
   
               Response.AddCacheDependency(dependency)
   
               connection.Open()
   
               GridView1.DataSource = command.ExecuteReader()
               GridView1.DataBind()
           End Using
       End Using
   End Sub
   

   C#

   protected void Page_Load(object sender, EventArgs e)
   {
       Label1.Text = "Cache Refresh: " +
       DateTime.Now.ToLongTimeString();
   
       // Create a dependency connection to the database.
       SqlDependency.Start(GetConnectionString());
   
       using (SqlConnection connection =
           new SqlConnection(GetConnectionString()))
       {
           using (SqlCommand command =
               new SqlCommand(GetSQL(), connection))
           {
               SqlCacheDependency dependency =
                   new SqlCacheDependency(command);
               // Refresh the cache after the number of minutes
               // listed below if a change does not occur.
               // This value could be stored in a configuration file.
               int numberOfMinutes = 3;
               DateTime expires =
                   DateTime.Now.AddMinutes(numberOfMinutes);
   
               Response.Cache.SetExpires(expires);
               Response.Cache.SetCacheability(HttpCacheability.Public);
               Response.Cache.SetValidUntilExpires(true);
   
               Response.AddCacheDependency(dependency);
   
               connection.Open();
   
               GridView1.DataSource = command.ExecuteReader();
               GridView1.DataBind();
           }
       }
   }
   

5. Add two helper methods, GetConnectionString and GetSQL. The connection string defined uses integrated security. You will need to verify that the account you are using has the necessary database permissions and that the sample database, AdventureWorks, has notifications enabled. For more information, see Special Considerations When Using Query Notifications.

   Visual Basic

   Private Function GetConnectionString() As String
       ' To avoid storing the connection string in your code,
       ' you can retrive it from a configuration file.
   
       Return "Data Source=(local);Integrated Security=true;" & _
        "Initial Catalog=AdventureWorks;"
   End Function
   
   Private Function GetSQL() As String
       Return "SELECT Production.Product.ProductID, " & _
       "Production.Product.Name, " & _
       "Production.Location.Name AS Location, " & _
       "Production.ProductInventory.Quantity " & _
       "FROM Production.Product INNER JOIN " & _
       "Production.ProductInventory " & _
       "ON Production.Product.ProductID = " & _
       "Production.ProductInventory.ProductID " & _
       "INNER JOIN Production.Location " & _
       "ON Production.ProductInventory.LocationID = " & _
       "Production.Location.LocationID " & _
       "WHERE ( Production.ProductInventory.Quantity <= 100) " & _
       "ORDER BY Production.ProductInventory.Quantity, " & _
       "Production.Product.Name;"
   End Function
   

   C#

   private string GetConnectionString()
   {
       // To avoid storing the connection string in your code,
       // you can retrieve it from a configuration file.
       return "Data Source=(local);Integrated Security=true;" +
         "Initial Catalog=AdventureWorks;";
   }
   private string GetSQL()
   {
       return "SELECT Production.Product.ProductID, " +
       "Production.Product.Name, " +
       "Production.Location.Name AS Location, " +
       "Production.ProductInventory.Quantity " +
       "FROM Production.Product INNER JOIN " +
       "Production.ProductInventory " +
       "ON Production.Product.ProductID = " +
       "Production.ProductInventory.ProductID " +
       "INNER JOIN Production.Location " +
       "ON Production.ProductInventory.LocationID = " +
       "Production.Location.LocationID " +
       "WHERE ( Production.ProductInventory.Quantity <= 100 ) " +
       "ORDER BY Production.ProductInventory.Quantity, " +
       "Production.Product.Name;";
   }
   

Testing the Application

Other Resources