Export (0) Print
Expand All

SqlCacheDependencyAdmin Class

Performs administrative tasks required on a SQL Server database to support the SqlCacheDependency class when using polling-based dependencies. This class cannot be inherited.

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

[AspNetHostingPermissionAttribute(SecurityAction.LinkDemand, Level = AspNetHostingPermissionLevel.High)]
public static class SqlCacheDependencyAdmin

You can use the methods of this class to programmatically enable and disable a SQL Server database and its tables for SqlCacheDependency object change notifications. In addition, this class obtains information about which tables in a database are enabled for change notifications using the GetTablesEnabledForNotifications method. However, you can also use the Aspnet_regsql command-line tool to manage change notifications for a SQL Server database and its tables.

NoteNote:

To call any of the methods in this class, the account you use to access the SQL Server database must have permissions to create tables and stored procedures. To enable notifications on a specific table, you must have permissions to create a SQL Server trigger on that table. For more information on how to set permissions on a database, see the SQL Server documentation. For more information about the account used by the ASP.NET process, see ASP.NET Impersonation.

The following code example is a simple ASP.NET page for enabling and disabling change notifications on SQL Server database tables. It uses the SqlCacheDependencyAdmin object to manage change notifications in the database specified in a connection string called MyConnectionString. The example includes two files, an .aspx file that defines the user interface and a code-behind file containing the source code for the ASP.NET events.

The first sample is the .aspx file that defines the user interface.

Security noteSecurity Note:

This example has a text box that accepts user input, which is a potential security threat. By default, ASP.NET Web pages validate that user input does not include script or HTML elements. For more information, see Script Exploits Overview.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="cacheDependencyAdmincs.aspx.cs"
  Inherits="cacheDependencyAdmincs" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <meta http-equiv="Content-Type" content="text/html" />
  <title>Cache Dependency Administration</title>
</head>
<body>
  <form id="form1" runat="server">
    <table>
      <tr>
        <td colspan="2">
          Database support for change notifications:
        </td>
      </tr>
      <tr>
        <td align="center">
          <asp:Button ID="enableNotification" runat="server" Text="On" OnClick="enableNotification_Click" />
        </td>
        <td align="center">
          <asp:Button ID="disableNotification" runat="server" Text="Off" OnClick="disableNotification_Click" />
        </td>
      </tr>
      <tr>
        <td colspan="2">
          <asp:Label ID="enabledTablesMsg" runat="server" Text="Tables enabled for change notification:" />
        </td>
      </tr>
      <tr>
        <td colspan="2">
          <asp:ListBox ID="enabledTables" runat="server" SelectionMode="multiple" /><br />
          <asp:Button ID="disableTable" runat="server" Text="Disable selected table(s)" OnClick="disableTable_Click" />
        </td>
      </tr>
      <tr>
        <td colspan="2">
          <asp:Label ID="tableEnableMsg" runat="server" Text="Enable change notification on table:" />
        </td>
      </tr>
      <tr>
        <td colspan="2">
          <asp:TextBox ID="tableName" runat="server" /><br />
          <asp:Button ID="enableTable" runat="server" Text="Enable table(s)" OnClick="enableTable_Click" />
          <asp:Label id="enableTableErrorMsg" runat="server" Visible="false" />
        </td>
      </tr>
    </table>
  </form>
</body>
</html>

The second sample is the code-behind file containing the source code for the page events.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Web.Caching;

public partial class cacheDependencyAdmincs : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    // Put page in default state.
    enabledTables.Visible = true;
    disableTable.Visible = true;
    enabledTablesMsg.Text = "Tables enabled for change notification:";

    tableName.Visible = true;
    enableTable.Visible = true;
    tableEnableMsg.Text = "Enable change notification on table(s):";
    enableTableErrorMsg.Visible = false;
  }

   protected void Page_PreRender(object sender, EventArgs e)
  {
    try
    {
      string[] enabledTablesList =
      SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(
        ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
      if (enabledTablesList.Length > 0)
      {
        enabledTables.DataSource = enabledTablesList;
        enabledTables.DataBind();
      }
      else
      {
        enabledTablesMsg.Text = "No tables are enabled for change notifications.";
        enabledTables.Visible = false;
        disableTable.Visible = false;
      }
    }
    catch (DatabaseNotEnabledForNotificationException ex)
    {
      enabledTables.Visible = false;
      disableTable.Visible = false;
      enabledTablesMsg.Text = "Cache notifications are not enabled in this database.";

      tableName.Visible = false;
      enableTable.Visible = false;
      tableEnableMsg.Text = "Must enable database for notifications before enabling tables";
    }
  }
  protected void enableNotification_Click(object sender, EventArgs e)
  {
    SqlCacheDependencyAdmin.EnableNotifications(
      ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
  }
  protected void disableNotification_Click(object sender, EventArgs e)
  {
    SqlCacheDependencyAdmin.DisableNotifications(
      ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
  }
  protected void disableTable_Click(object sender, EventArgs e)
  {
    foreach (ListItem item in enabledTables.Items)
    {
      if (item.Selected)
      {
        SqlCacheDependencyAdmin.DisableTableForNotifications(
          ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString,
          item.Text);
      }
    }
  }
  protected void enableTable_Click(object sender, EventArgs e)
  {
    try
    {
      if (tableName.Text.Contains(";"))
      {
        string[] tables = tableName.Text.Split(new Char[] { ';' });
        for (int i = 0; i < tables.Length; i++)
          tables[i] = tables[i].Trim();

        SqlCacheDependencyAdmin.EnableTableForNotifications(
          ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString,
          tables);
      }
      else
      {
        SqlCacheDependencyAdmin.EnableTableForNotifications(
          ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString,
          tableName.Text);
      }
    }
    catch (HttpException ex)
    {
      enableTableErrorMsg.Text = "<br />" +
        "An error occured enabling a table.<br />" +
        "The error message was: " +
        ex.Message;
      enableTableErrorMsg.Visible = true;
    }
  }
}

System.Object
  System.Web.Caching.SqlCacheDependencyAdmin

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 7, Windows Vista, Windows XP SP2, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP Starter Edition, Windows Server 2008 R2, Windows Server 2008, Windows Server 2003, Windows Server 2000 SP4, Windows Millennium Edition, Windows 98

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

.NET Framework

Supported in: 3.5, 3.0, 2.0

Community Additions

ADD
Show:
© 2014 Microsoft