注: 以下は .NET Framework 版 ASP.NET Web Forms アプリの話です。ASP.NET Core アプリの場合は「ASP.NET Core で SQL キャッシュ依存関係」に書きましたのでそちらを見てください。
SQL キャッシュ依存関係 (SQL Cache Dependency) について調べて、いろいろわかったことがありますので、備忘録として書いておきます。自分なりに解釈したところもありますので、ひょっとしたら間違っているところがあるかもしれませんが。（汗）
まず、キャッシュ全般に関する予備知識として、Microsoft のドキュメント ASP.NET Caching Overview（日本語のドキュメントはリンク切れ）に一通り目を通しておくことをお勧めします。
ASP.NET のキャッシュは、ファイル、データベースなどの他の要素に依存するように構成でき、依存する要素が変更されると、ASP.NET は関係する項目をキャッシュから削除するように設定できます。そうすることで、最新のデータをユーザーに提供するとともに、キャッシュの内容も最新に保つことができます。
SQL キャッシュ依存関係とは、ASP.NET のキャッシュと SQL Server DB のテーブルやレコードとの間に依存関係を持たせ、当該テーブル／レコードが変更されたら ASP.NET のキャッシュを削除し、次のリクエストでは新しいデータを DB から取得してユーザーに提供するとともに、新しいデータをキャッシュできるようにするための機能です。
そのために、SQL Server 側で依存する項目が変更されたら、その通知を SQL Server から ASP.NET に渡してやる必要があります。通知を渡す仕組みに以下の 2 種類があります。
-
テーブルポーリング (table polling)
-
クエリ通知 (query notification)
ここでは、前者の「ポーリング」を利用した場合について、その仕組みとアプリケーションの作成例を書きます。実は、「クエリ通知」の方は、SQL Server 関係の設定が難しく、まだうまくいってませんので。（笑）
ポーリングは、事前に設定したインターバルで自動的に ASP.NET から SQL Server に変更の有無を問い合わせる仕組みです。
ポーリングを行うためには、SQL Server 側と ASP.NET 側の両方に準備が必要です。
まず、SQL Server 側の準備です。SQL Server 側でポーリングによる通知を有効にするには、aspnet_regsql.exe コマンドラインツールを使用し、コマンドラインから以下のように実行します。
aspnet_regsql -S <サーバー名> -E -d <DB 名> -ed
aspnet_regsql -S <サーバー名> -E -t <テーブル名> -d <DB 名> -et
オプションのパラメータの設定方法詳細については ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe) （日本語のドキュメントはリンク切れ）を参照してください。SQL キャッシュ依存関係オプションは SQL Server 7.0, 2000, 2005 と書いてありますが、SQL Server 2008 も同じです。
サーバー名のところは、例えば SQL Server をインストールしてあるコンピュータ名が papiko-pc で SQL Server が Express Edition のときは papiko-pc\sqlexpress とします。成功すると左の画像に示したようになります（クリックすると拡大画像が表示されます）。
開発環境で SQL Server Developer Eidtion と Express Edition の両方をインストールしてある場合など、インスタンスが複数ある場合、インスタンス名（上記の場合 sqlexpress）も指定するところがミソです。
省略すると既定のインスタンス（MSSQLSERVER）を指定したとみなされるらしく、Developer Edition の方に設定されてしまいます。自分はこれに気づかず、半日ぐらいハマってしまいました。（笑）
aspnet_regsql.exe を実行することによって、SQL Server にポーリング先のテーブル、ポーリングに使うストアドプロシージャ、更新の検出のためのトリガを生成するストアドプロシージャなどが自動生成されます。
ポーリング先のテーブルは AspNet_SqlCacheTablesForChangeNotification という名前で、tableName, notificationCreated, changedId という 3 つのフィールドを持ちます。左の画像（クリックすると拡大表示されます）は、SQL Server Management Studio でそのテーブルを表示したものです。
ポーリング先のテーブルに加えて、左の画像（クリックすると拡大表示されます）に示す 5 つのストアドプロシージャが自動生成されます。
これらは SQL Server に更新状況を問い合わせたり、更新の検出のためのトリガを生成するために使用されます。
ポーリングに使うのは SqlCachePollingStoredProcedure という名前のストアドプロシージャで、以下のような内容になっています。これが web.config で設定したポーリング間隔で呼び出されます。
ALTER PROCEDURE [dbo].[AspNet_SqlCachePollingStoredProcedure] AS
SELECT tableName, changeId
FROM dbo.AspNet_SqlCacheTablesForChangeNotification
RETURN 0
次に、ASP.NET 側の準備です。まず、web.config で caching 要素の設定を行います。以下の例では、SQL キャッシュ依存関係を構築するためにポーリングを有効にし、ポーリング間隔を 10 秒に設定しています。さらに、依存関係を持たせるデータベースを接続文字列によって指定し、その名前を Northwind に設定しています。
<caching>
<sqlCacheDependency
enabled = "true"
pollTime = "10000" >
<databases>
<add
name="Northwind"
connectionStringName="Northwind2"
pollTime = "10000" />
</databases>
</sqlCacheDependency>
</caching>
ASP.NET のキャッシュには、アプリケーションキャッシュ（Cache クラス 参照）、ページ出力キャッシュ（@ OutputCache 参照）およびデータソースコントロール（SqlDataSource, ObjectDataSource など）の持つキャッシュがありますが、いずれも SQL キャッシュ依存関係を構築することが可能です。
以下の例は、Microsoft のドキュメント「SqlCacheDependency クラス
」の説明に記載されていたサンプルコードに手を加えたものです。この記事の一番上の画像を表示させたものです。
Northwind データベースの Employees テーブルから DataTable を作成し、それをアプリケーションキャッシュに格納するとともに GridView で表示しています。キャッシュと SQL Server との間に依存関係を持たせ、Employees テーブルが更新されたらキャッシュを廃棄するようにしています。
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="EmployeesDataSetTableAdapters" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
// SqlCacheDependency クラスのサンプル。SqlDataSource
// をキャッシュしてもうまくいかないので DataTable を
// キャッシュするように変更。
protected void Page_Load(object sender, EventArgs e)
{
string dateTimeNow = DateTime.Now.ToString();
Label1.Text = dateTimeNow;
Label2.Text = dateTimeNow;
SqlCacheDependency dependency = null;
string connectionString =
ConfigurationManager.
ConnectionStrings["Northwind2"].ConnectionString;
if (Cache["EmployeesDataTable"] == null)
{
try
{
dependency =
new SqlCacheDependency("Northwind", "Employees");
}
catch (DatabaseNotEnabledForNotificationException)
{
try
{
SqlCacheDependencyAdmin.
EnableNotifications(connectionString);
}
catch (UnauthorizedAccessException)
{
Response.Redirect("ErrorPage.htm");
}
}
catch (TableNotEnabledForNotificationException)
{
try
{
SqlCacheDependencyAdmin.
EnableTableForNotifications(
connectionString, "Employees");
}
catch (SqlException)
{
Response.Redirect("ErrorPage.htm");
}
}
finally
{
EmployeesTableAdapter adapter =
new EmployeesTableAdapter();
EmployeesDataSet.EmployeesDataTable table =
adapter.GetData();
GridView1.DataSource = table;
GridView1.DataBind();
Cache.Insert("EmployeesDataTable",
table, dependency,
DateTime.Now.AddMinutes(60),
Cache.NoSlidingExpiration);
}
}
else
{
GridView1.DataSource =
(EmployeesDataSet.EmployeesDataTable)
Cache["EmployeesDataTable"];
GridView1.DataBind();
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>SQL Cache Dependency</title>
<style type="text/css">
#UpdatePanel1
{
width: 250px;
height: 370px;
border: gray 1px solid;
margin-left: 10px;
margin-top: 10px;
padding: 0 5px 0 5px;
background-color: #eeeeee;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<h1>SQL Cache Dependency</h1>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:Label ID="Label1" runat="server">
</asp:Label>
<asp:UpdatePanel ID="UpdatePanel1" runat="server" >
<ContentTemplate>
<p>UpdatePanel</p>
<hr />
<p><asp:Label ID="Label2" runat="server">
</asp:Label></p>
<asp:GridView ID="GridView1"
runat="server"
AutoGenerateColumns="False"
DataKeyNames="EmployeeID"
EnableModelValidation="True"
EnableViewState="False"
BackColor="White">
<Columns>
<asp:BoundField
DataField="EmployeeID"
HeaderText="EmployeeID"
InsertVisible="False"
ReadOnly="True"
SortExpression="EmployeeID" />
<asp:BoundField
DataField="LastName"
HeaderText="LastName"
SortExpression="LastName" />
<asp:BoundField
DataField="FirstName"
HeaderText="FirstName"
SortExpression="FirstName" />
</Columns>
</asp:GridView>
<asp:Button ID="Button1"
runat="server"
Text="Refresh Panel" />
</ContentTemplate>
</asp:UpdatePanel>
</form>
</body>
</html>
aspnet_regsql.exe を使わなくても、SqlCacheDependencyAdmin クラスのメソッドを呼び出して SQL キャッシュ依存関係を動的に設定することができるそうですが、上記にはそのためのコードが含まれています。
ただし、SQL Server データベースへのアクセスで使用するアカウント（ワーカープロセスのアカウント。IIS6, 7 のデフォルトで NETWORK SERVICE）に、テーブルおよびストアドプロシージャを作成できる権限が必要です。さらに、通知を有効にするテーブルでトリガを作成できる権限が必要です。その設定には SQL Server の管理者特権が必要で、SQL Server の設定に詳しくないと難しそうです。