Using SqlDependency in an ASP.NET Application
The following scenario shows how to use SqlDependency indirectly by leveraging the ASP.NET SqlCacheDependency object. The SqlCacheDependency object uses the SqlDependency object and takes care of listening for notifications and correctly updating the cache.
Creating the Sample
The sample application uses a single ASP.NET Web page to display a set of records from the database in a GridView control. When the page loads, it inserts the current time into a label on the page. It then defines a SqlCacheDependency object and sets properties on the Cache object to store the cache data for up to three minutes. It then connects to the database and retrieves a set of records. The first time the page is run, the code will execute. For up to three minutes after that, the page will come from the cache, which can be verified by noting that the time displayed on the page does not change. If however, the data being monitored on the server does change, ASP.NET will invalidate the cache and repopulate it.
To create the sample
Create a new Web Site project.
Add a Label and a GridView control to the page.
Open the page's class module and add the following code to the top of the file, above the class definition:
[Visual Basic]
Option Strict On
Imports System.Data.SqlClient
[C#]
using System.Data.SqlClient;
using System.Web.Caching;
- To the page's
Page_Loadevent, add the following code:
[Visual Basic]
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
[C#]
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();
}
}
- Add two helper methods,
GetConnectionStringandGetSQL. 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. See Special Considerations When Using Query Notificationsfor more information.
[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=localhost;Integrated Security=SSPI;" & _
"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=localhost;Integrated Security=SSPI;" +
"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;";
}
The application caches the data displayed on the Web form and refreshes it every three minutes. If a change occurs to the database, the cache is refreshed immediately.
Start the application from Visual Studio, which brings up the Web site in Internet Explorer. Note the Cache Refresh time displayed. This time indicates when the cache was last updated. Wait for three minutes and reload the page; note that the time has changed.
Now update the data in the database, using either Transact-SQL commands, or the Data Updater program created in the Using SqlDependency in a Windows Application topic, and reload the page. The Cache Refresh time now indicates that the cache was automatically refreshed with the current data from the database.