Under .NET 1.1, Cache can only make Cache dependencies based on file systems, objects, etc., but .NET 2.0 provides a new dependency, SqlCacheDependency, which allows our Cache to depend on changes in the database. Although this approach looks similar to the workaround in 1.1, there is an essential difference between them (the provision of the SqlCacheDependency object). (The workaround for 1.1 is to create a trigger for the data table, and then the trigger will modify a local file when triggered. The dependency of a certain Cache in the system is this local file, so as to notify the Cache of the cached data. changed)
.NET 2.0's SqlCacheDependency can be used in versions after SqlServer2000. This article introduces how to use it under Sql Server 2000. Sql Server 2005 is slightly different.
First, you need to install the .NET 2.0 framework. Of course, Sql Server 2000 must be available. In .NET 2.0, Microsoft provides a utility tool: aspnet_regsql (in the same directory as aspnet_regiis). This is a command line tool. The command line parameters can be obtained through aspnet_regsql -?. Here we care about the following parameters: -ed starts the database for SQL cache dependencies, -E uses the current windows credentials for authentication, -d is used for the database name of the application server, if no database name is specified, the default database is used "aspnetdb". Microsoft's sample database pubs will be used in this article. Then we need to use the following command line to create the cache dependent database:
aspnet_regsql -ed -E -d pubs
(Note: The command line parameters here are case-sensitive)
After executing this command, we can open the pubs database to see what changes have occurred. First, there is an additional table: AspNet_SqlCacheTablesForChangeNotification. This table has 3 fields. tableName: monitored table name, notificationCreated: creation time. changeId: change number (cumulative field). You can also see that the database has several more stored procedures, they are:
AspNet_SqlCacheRegisterTableStoredProcedure,
AspNet_SqlCacheUnRegisterTableStoredProcedure,
AspNet_SqlCacheUpdateChangeIdStoredProcedure,
AspNet_SqlCacheQueryRegisteredTablesStoredProcedure,
AspNet_SqlCachePollingStoredProcedure
These stored procedures are easy to understand literally. The details of the stored procedure can be viewed after opening it in the query analyzer. In order to monitor changes in a table, we need to specify a table that needs to be monitored by executing the AspNet_SqlCacheRegisterTableStoredProcedure stored procedure. Of course, it can also be specified through the utility tool aspnet_regsql. The command line is as follows:
aspnet_regsql -et -E -d pubs -t authors
After executing the above command, aspnet_regsql will create a trigger for authors. The following is the trigger I created after executing the command:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER dbo.[authors_AspNet_SqlCacheNotification_Trigger] ON [authors]
FOR INSERT, UPDATE, DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'authors'
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
As can be seen from the trigger, the stored procedure AspNet_SqlCacheUpdateChangeIdStoredProcedure will be executed when the authors table is inserted, deleted, and updated. This stored procedure will add a record to the AspNet_SqlCacheTablesForChangeNotification table. The record is as follows:
tableName notificationCreated changeId
authors 2006-06-20 09:38:26.267 1
When you make any modifications to the data in authors, changeId will be accumulated, and other fields will not change.
Then you need to add some content in web.config and you can use it.
First add the database connection string:
<connectionStrings>
<add name="pubsConString" connectionString="server=localhost;database=pubs;uid=sa;pwd=mypassword;"/>
</connectionStrings>
Secondly add the cache section:
<system.web>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add name="pubs" connectionStringName="pubsConString" pollTime="900"/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
The following is the test code. When the page is loaded, the following code is executed:
protected void Page_Load(object sender, EventArgs e) {
if (HttpContext.Current.Cache["xxx"] == null) {
SqlCacheDependency d = new SqlCacheDependency("pubs","authors");//pubs is the pubs specified in the databases section, followed by the table name
HttpContext.Current.Cache.Insert("xxx", "xxx", d);
Response.Write("Create new cache value is xxx.");
}
else{
Response.Write("Load data from cache,value is "+HttpContext.Current.Cache["xxx"].ToString());
}
}
When first opened this page will display:
Create new cache value is xxx.
After refreshing the page, it displays:
Load data from cache, value is xxx
When using the query analyzer to modify the data in authors and refreshing the page again, it will display:
Create new cache value is xxx.
This shows that the Cache is working normally. When the authors table changes, the contents in the Cache will automatically become invalid, and my application will also re-create a Cache object.
Note: SqlCacheDependency consists of two constructors, SqlServer2000 only supports two parameters, and one parameter supports SqlServer2005.