Welcome Sign in
.NET Framework Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
.NET Development
Previous Versions
.NET Framework 2.0
System.Web.Caching
This page is specific to
Microsoft Visual Studio 2005/.NET Framework 2.0

Other versions are also available for the following:
.NET Framework Class Library
SqlCacheDependency Class

Note: This class is new in the .NET Framework version 2.0.

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.

Namespace: System.Web.Caching
Assembly: System.Web (in system.web.dll)

Visual Basic (Declaration)
Public NotInheritable Class SqlCacheDependency
    Inherits CacheDependency
Visual Basic (Usage)
Dim instance As SqlCacheDependency
C#
public sealed class SqlCacheDependency : CacheDependency
C++
public ref class SqlCacheDependency sealed : public CacheDependency
J#
public final class SqlCacheDependency extends CacheDependency
JScript
public final class SqlCacheDependency extends CacheDependency

On all supported versions of SQL Server (7.0, 2000, 2005) the SqlCacheDependency class monitors a specific SQL Server database table so that when the table changes, items associated with the table will be automatically removed from the Cache.

When the database table changes, the cached item is automatically deleted, 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 is used to detect changes to data that invalidate the results of an SQL query. Any cached items associated with the SQL query are removed 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 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 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.

NoteNote

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 using the methods of the SqlCacheDependencyAdmin class or the Aspnet_regsql.exe command-line tool. Also, 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. Developers should consult the SQL Server 2005 Books Online for restrictions on the types of Transact-SQL queries that are allowed when using query notification.

The following is a sample ASP.NET Web.config file that enables table-based dependencies on a SQL Server database table.

<configuration>
  <connectionStrings>
    <add name="Pubs" connectionString="Data Source=(local); Initial Catalog=pubs; Integrated Security=true"; providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <caching>
      <sqlCacheDependency enabled = "true" pollTime = "60000" >
        <databases>
          <add name="pubs" 
            connectionString = "pubs"
            pollTime = "9000000"
            />
        </databases>
      </sqlCacheDependency>
    </caching>
  </system.web>
</configuration>
TopicLocation
Walkthrough: Using ASP.NET Output Caching with SQL ServerBuilding ASP .NET Web Applications in Visual Studio
Walkthrough: Using ASP.NET Output Caching with SQL ServerBuilding ASP .NET Web Applications in Visual Studio

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. Once 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.

Visual Basic
<%@ Page Language="VB" Debug="True" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
    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

</script>
<html>
<head>
</head>
<body>
    <form 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="server='localhost';user id='bjctest';password='bjctest'; Database='Northwind'"></asp:SqlDataSource>
            <asp:GridView id="GridView1" runat="server" KeyFieldNames="CategoryID" AllowSorting="True" AllowPaging="True" DataSourceID="Source1"></asp:GridView>
        </p>
        <p>
        </p>
        <p>
            <asp:Label id="CacheMsg" runat="server"></asp:Label>
        </p>
   </form>
</body>
</html>
System.Object
   System.Web.Caching.CacheDependency
    System.Web.Caching.SqlCacheDependency
Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

Windows 98, Windows 2000 SP4, Windows Server 2003, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP SP2, Windows XP Starter Edition

The .NET Framework does not support all versions of every platform. For a list of the supported versions, see System Requirements.

.NET Framework

Supported in: 2.0
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Processing
© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker