In. in net1.1, the cache can only be used as cache dependencies based on file systems, objects, and so on. NET 2.0 provides a new dependency sqlcachedependency, which changes the cache dependency database. Although this approach looks similar to the workarounds in 1.1, there is an essential difference between them (the provision of sqlcachedependency objects ). (The work und of 1.1 is to create a trigger for the data table. When the trigger is triggered, a local file is modified. The dependency of a cache in the system is the local file, in this way, the cached data changes)
The sqlcachedependency of. NET 2.0 can be used in Versions later than sqlserver2000. this article describes how to use it in SQL Server 2000. SQL Server 2005 is slightly different.
First, install the. NET 2.0 Framework. Of course, SQL Server 2000 must be installed. In. NET 2.0, Microsoft provides a utility: aspnet_regsql (which is in the same directory as aspnet_regiis). This is a command line tool. The command line parameter can be passed through aspnet_regsql -? . Here we are concerned about the following parameters:-ed starts the database for the SQL cache dependency,-e uses the current Windows credential for authentication,-D is used for the database name of the application server, if no database name is specified, the default database "aspnetdb" is used ". This document uses Microsoft's example database pubs. Use the following command line to create a cache dependent database:
Aspnet_regsql-ed-e-d pubs
(Note: the command line parameters here are case-insensitive)
After executing this command, we can open the pubs database to see what has changed. First, we have an additional table: aspnet_sqlcachetablesforchangenotification, which contains three fields, tablename: name of the monitored table, icationicationcreated: Creation Time. Changeid: Variable ID (accumulative field ). We can also see that the database has several additional stored procedures:
Aspnet_sqlcacheregistertablestoredprocedure,
Aspnet_sqlcacheunregistertablestoredprocedure, procedure, procedure, aspnet_sqlcachepollingstoredprocedure can be viewed in the query analyzer after opening the stored procedure. To monitor the changes of a table, we need to specify a table to be monitored by executing the aspnet_sqlcacheregistertablestoredprocedure stored procedure. Of course, we can also use the utility aspnet_regsql to specify the table to be monitored. 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. below is the trigger created after I execute this command:
Set quoted_identifier on
Go
Set ansi_nulls on
Goalter trigger DBO. [authors_aspnet_sqlcachenotification_trigger] on [authors]
For insert, update, delete as begin
Set nocount on
Exec DBO. aspnet_sqlcacheupdatechangeidstoredprocedure n 'author'
End
Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go
From the trigger, we can see that the Stored Procedure aspnet_sqlcacheupdatechangeidstoredprocedure will be executed during insertion, deletion, and update of the authors table. 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
Changeid will be accumulated when you modify the data in authors, and other fields will not change. Then you can add some content in Web. config. First, add the database connection string:
<Connectionstrings>
<Add name = "pubsconstring" connectionstring = "Server = localhost; database = pubs; uid = sa; Pwd = mypassword;"/>
</Connectionstrings> Add the caching section as follows:
<System. Web>
<Caching>
<Sqlcachedependency enabled = "true">
<Databases>
<Add name = "pubs" connectionstringname = "pubsconstring" polltime = "900" type = "codeph" text = "/codeph"/>
</Databases>
</Sqlcachedependency>
</Caching>
</System. Web> The following is the test code. When the page is loaded, run the following code: 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 this page is opened for the first time, the following information is displayed:
Create new cache value is XXX. It is displayed after the page is refreshed:
Load data from cache, value is XXX when you use the query analyzer to modify the data in authors, the refresh page will display:
Create new cache value is XXX. This indicates that the cache is working normally. When the table authors changes, the content in the cache will automatically expire, and my application will create a new cache object.
Note: sqlcachedependency consists of two constructors. sqlserver2000 only supports two parameters. One Parameter supports sqlserver2005.
Please correct me if there are any errors. Thank you.
