SqlCacheDependency Class
Definition
Important
Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.
Establishes a relationship between an item stored in an ASP.NET application's Cache object and either a specific SQL Server database table or the results of a SQL Server 2005 query. This class cannot be inherited.
public ref class SqlCacheDependency sealed : System::Web::Caching::CacheDependencypublic sealed class SqlCacheDependency : System.Web.Caching.CacheDependencytype SqlCacheDependency = class
inherit CacheDependencyPublic NotInheritable Class SqlCacheDependency
Inherits CacheDependency- Inheritance
Examples
The following code example uses the SqlDataSource and GridView controls to display a database table. When the page is loaded, the page attempts to create a SqlCacheDependency object. After the SqlCacheDependency object is created, the page adds an item to the Cache with a dependency on the SqlCacheDependency object. You should use exception handling similar to that shown here.
<%@ Page Language="C#" Debug="true" %>
<%@ import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
// <snippet2>
public void Page_Load(object Src, EventArgs E)
{
// Declare the SqlCacheDependency instance, SqlDep.
SqlCacheDependency SqlDep = null;
// Check the Cache for the SqlSource key.
// If it isn't there, create it with a dependency
// on a SQL Server table using the SqlCacheDependency class.
if (Cache["SqlSource"] == null) {
// Because of possible exceptions thrown when this
// code runs, use Try...Catch...Finally syntax.
try {
// Instantiate SqlDep using the SqlCacheDependency constructor.
SqlDep = new SqlCacheDependency("Northwind", "Categories");
}
// Handle the DatabaseNotEnabledForNotificationException with
// a call to the SqlCacheDependencyAdmin.EnableNotifications method.
catch (DatabaseNotEnabledForNotificationException exDBDis) {
try {
SqlCacheDependencyAdmin.EnableNotifications("Northwind");
}
// If the database does not have permissions set for creating tables,
// the UnauthorizedAccessException is thrown. Handle it by redirecting
// to an error page.
catch (UnauthorizedAccessException exPerm) {
Response.Redirect(".\\ErrorPage.htm");
}
}
// Handle the TableNotEnabledForNotificationException with
// a call to the SqlCacheDependencyAdmin.EnableTableForNotifications method.
catch (TableNotEnabledForNotificationException exTabDis) {
try {
SqlCacheDependencyAdmin.EnableTableForNotifications("Northwind", "Categories");
}
// If a SqlException is thrown, redirect to an error page.
catch (SqlException exc) {
Response.Redirect(".\\ErrorPage.htm");
}
}
// If all the other code is successful, add MySource to the Cache
// with a dependency on SqlDep. If the Categories table changes,
// MySource will be removed from the Cache. Then generate a message
// that the data is newly created and added to the cache.
finally {
Cache.Insert("SqlSource", Source1, SqlDep);
CacheMsg.Text = "The data object was created explicitly.";
}
}
else {
CacheMsg.Text = "The data was retrieved from the Cache.";
}
}
// </snippet2>
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>ASP.NET Example</title>
</head>
<body>
<form id="form1" runat="server">
<p>
</p>
<p>
<asp:SqlDataSource id="Source1" runat="server" SelectCommand="SELECT * FROM [Categories]" UpdateCommand="UPDATE [Categories] SET [CategoryName]=@CategoryName,[Description]=@Description,[Picture]=@Picture WHERE [CategoryID]=@CategoryID" ConnectionString="<%$ ConnectionStrings:Northwind %>"></asp:SqlDataSource>
<asp:GridView id="GridView1" runat="server" DataKeyNames="CategoryID" AllowSorting="True" AllowPaging="True" DataSourceID="Source1"></asp:GridView>
</p>
<p>
</p>
<p>
<asp:Label id="CacheMsg" runat="server" AssociatedControlID="GridView1"></asp:Label>
</p>
</form>
</body>
</html>
<%@ Page Language="VB" Debug="True" %>
<%@ import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
' <snippet2>
Sub Page_Load(Src As Object, E As EventArgs)
' Declare the SqlCacheDependency instance, SqlDep.
Dim SqlDep As SqlCacheDependency
' Check the Cache for the SqlSource key.
' If it isn't there, create it with a dependency
' on a SQL Server table using the SqlCacheDependency class.
If Cache("SqlSource") Is Nothing
' Because of possible exceptions thrown when this
' code runs, use Try...Catch...Finally syntax.
Try
' Instantiate SqlDep using the SqlCacheDependency constructor.
SqlDep = New SqlCacheDependency("Northwind", "Categories")
' Handle the DatabaseNotEnabledForNotificationException with
' a call to the SqlCacheDependencyAdmin.EnableNotifications method.
Catch exDBDis As DatabaseNotEnabledForNotificationException
Try
SqlCacheDependencyAdmin.EnableNotifications("Northwind")
' If the database does not have permissions set for creating tables,
' the UnauthorizedAccessException is thrown. Handle it by redirecting
' to an error page.
Catch exPerm As UnauthorizedAccessException
Response.Redirect(".\ErrorPage.htm")
End Try
' Handle the TableNotEnabledForNotificationException with
' a call to the SqlCacheDependencyAdmin.EnableTableForNotifications method.
Catch exTabDis As TableNotEnabledForNotificationException
Try
SqlCacheDependencyAdmin.EnableTableForNotifications( _
"Northwind", "Categories")
' If a SqlException is thrown, redirect to an error page.
Catch exc As SqlException
Response.Redirect(".\ErrorPage.htm")
End Try
' If all the other code is successful, add MySource to the Cache
' with a dependency on SqlDep. If the Categories table changes,
' MySource will be removed from the Cache. Then generate a message
' that the data is newly created and added to the cache.
Finally
Cache.Insert("SqlSource", Source1, SqlDep)
CacheMsg.Text = "The data object was created explicitly."
End Try
Else
CacheMsg.Text = "The data was retrieved from the Cache."
End If
End Sub
' </snippet2>
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>ASP.NET Example</title>
</head>
<body>
<form id="form1" runat="server">
<p>
</p>
<p>
<asp:SqlDataSource id="Source1" runat="server" SelectCommand="SELECT * FROM [Categories]" UpdateCommand="UPDATE [Categories] SET [CategoryName]=@CategoryName,[Description]=@Description,[Picture]=@Picture WHERE [CategoryID]=@CategoryID" ConnectionString="<%$ ConnectionStrings:Northwind %>"></asp:SqlDataSource>
<asp:GridView id="GridView1" runat="server" DataKeyNames="CategoryID" AllowSorting="True" AllowPaging="True" DataSourceID="Source1"></asp:GridView>
</p>
<p>
</p>
<p>
<asp:Label id="CacheMsg" runat="server" AssociatedControlID="GridView1"></asp:Label>
</p>
</form>
</body>
</html>
Remarks
On all supported versions of SQL Server (Microsoft SQL Server 7.0, Microsoft SQL Server 2000, and SQL Server 2005) the SqlCacheDependency class monitors a specific SQL Server database table. When the table changes, items associated with the table are removed from the Cache, and a new version of the item is added to the Cache.
The SqlCacheDependency class also supports integration with the System.Data.SqlClient.SqlDependency class when using a SQL Server 2005 database. The query notification mechanism of SQL Server 2005 detects changes to data that invalidate the results of an SQL query and removes any cached items associated with the SQL query from the System.Web.Caching.Cache.
You can use the SqlCacheDependency class to add items to your application's Cache that are dependent on either a SQL Server database table or on an SQL query when using SQL Server 2005. You can also use this class with the @ OutputCache directive to make an output-cached page or a user control dependent on a SQL Server database table. Finally, you can use the SqlCacheDependency class with the @ OutputCache page directive to make an output-cached page dependent on the results of an SQL query when using SQL Server 2005. Query notification using SQL Server 2005 is not supported on the @ OutputCache directive for user controls.
Note
For this class to work correctly when using table-based notifications, the database and any tables that you want to make dependencies on must have notifications enabled. You can enable notifications by calling methods of the SqlCacheDependencyAdmin class or by using the aspnet_regsql.exe command-line tool. In addition, the proper configuration settings must be included in the application's Web.config file.
Using a SqlCacheDependency object with SQL Server 2005 query notification does not require any explicit configuration. Consult the SQL Server documentation for information about restrictions on the types of Transact-SQL queries that are allowed when using query notification.
The following example shows an ASP.NET Web.config file that enables table-based dependencies on a SQL Server database table.
<configuration>
<connectionStrings>
<add name="Northwind" connectionString="Data Source=(local); Initial Catalog=northwind; Integrated Security=true"; providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled = "true" pollTime = "60000" >
<databases>
<add name="northwind"
connectionStringName="Northwind"
pollTime="9000000"
/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>
Constructors
| SqlCacheDependency(SqlCommand) |
Initializes a new instance of the SqlCacheDependency class, using the supplied SqlCommand to create a cache-key dependency. |
| SqlCacheDependency(String, String) |
Initializes a new instance of the SqlCacheDependency class, using the supplied parameters to create a cache-key dependency. |
Properties
| HasChanged |
Gets a value indicating whether the CacheDependency object has changed. (Inherited from CacheDependency) |
| UtcLastModified |
Gets the time when the dependency was last changed. (Inherited from CacheDependency) |
Methods
| CreateOutputCacheDependency(String) |
Creates a dependency relationship between an item that is stored in an ASP.NET application's OutputCache object and a SQL Server database table. |
| DependencyDispose() |
Releases the resources used by the CacheDependency class and any classes that derive from CacheDependency. (Inherited from CacheDependency) |
| Dispose() |
Releases the resources used by the CacheDependency object. (Inherited from CacheDependency) |
| Equals(Object) |
Determines whether the specified object is equal to the current object. (Inherited from Object) |
| FinishInit() |
Completes initialization of the CacheDependency object. (Inherited from CacheDependency) |
| GetFileDependencies() |
Gets the file dependencies. (Inherited from CacheDependency) |
| GetHashCode() |
Serves as the default hash function. (Inherited from Object) |
| GetType() |
Gets the Type of the current instance. (Inherited from Object) |
| GetUniqueID() |
Retrieves a unique identifier for a SqlCacheDependency object. |
| ItemRemoved() |
Called when a monitored cache entry is removed. (Inherited from CacheDependency) |
| KeepDependenciesAlive() |
Updates the last access time of every cache item that depends on this item. (Inherited from CacheDependency) |
| MemberwiseClone() |
Creates a shallow copy of the current Object. (Inherited from Object) |
| NotifyDependencyChanged(Object, EventArgs) |
Notifies the base CacheDependency object that the dependency represented by a derived CacheDependency class has changed. (Inherited from CacheDependency) |
| SetCacheDependencyChanged(Action<Object,EventArgs>) |
Adds an Action method to handle notifying interested party in changes to this dependency. (Inherited from CacheDependency) |
| SetUtcLastModified(DateTime) |
Marks the time when a dependency last changed. (Inherited from CacheDependency) |
| TakeOwnership() |
Allows the first user to declare exclusive ownership of this dependency. (Inherited from CacheDependency) |
| ToString() |
Returns a string that represents the current object. (Inherited from Object) |
