In .NET1.1, Cache can only be based on file system, object, etc. to make Cache's dependencies, but provide a new dependency SQLCACHEPENDENCY in .NET 2.0, this item changes our cache dependent the change of the database. Although this is similar to 1.1, there is still an essential difference between them (providing the SqlCachedependency object). (1.1 Workaround is to create a trigger for the data table, and then the trigger will modify a local file when triggering, and a cache dependency in the system is this local file, which informs the data cached by Cache. Changed)
SQLCachedependences in .NET 2.0 can be used in version after SQL Server 2000, this article describes how to use under SQL Server 2000, and SQL Server 2005 is slightly different.
First, you need to install the .NET 2.0 framework, of course, SQL Server 2000 must have. A utility is provided in .NET 2.0, Microsoft provides a utility: ASPNET_REGSQL (and ASPNET_REGIIS below the same directory), this is a command line tool, which can be obtained by ASPNET_REGSQL -? Here we care about the following parameters: -ed to start the database based on the SQL cache, -e uses the current Windows credentials to authenticate, -d to apply the database name of the application server, if no database name is specified, use the default database "Aspnetdb". This article will use Microsoft's sample database PUBS. Then we will use the following command line to create a cache dependency database:
ASPNET_REGSQL -ED -E -D PUBS (Note: The command line parameters here are sized to write)
When this command is executed, we can open the Pubs database to see what has changed, first more a table: ASPNET_SQLCACHETABLESFORCHANGENOTIFICATION, there are 3 fields in this table, TABLENAME: Table names for monitors, NOTIFICATIONCREATED: Create time. ChangeID: Change Number (accumulated field). You can also see that there are more memory procedures, which are:
ASPNET_SQLCACHEREGISTERTABLESTOREDPROCEDURE,
ASPNET_SQLCACHEUNREGISTERTABLESTOREDPROCEDURE,
ASPNET_SQLCACHEUPDATECHANGEIDSTOREDPROCEDURE,
ASPNET_SQLCACHEQUERYREGISTEREDTABLESSTOREDPROCEDURE,
ASPNET_SQLCACHEPOLLINGSTOREDPROCEDURE
Both the stored procedures are well understood, and the details of the stored procedure can be opened after the query analyzer is opened. In order to monitor a change in a table, we need to specify a table that needs to be monitored by executing the ASPNET_SQLCACHEREGISTERTABLESTOREDPROCACEREGISTERTABLESTOREDPROCEDURE stored procedure, of course, can also be specified by utility ASPNET_REGSQL, the command line is as follows:
ASPNET_REGSQL -ET -E -D PUBS -T Authors
By performing the above command, the ASPNET_REGSQL creates a trigger for Authors. Below is the trigger that I created after this command:
Set quoted_identifier ONGO
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 D'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 is executed when it is inserted, deleted, and updated, which adds 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 do any modification of the data in Authors, you will be accumulated, and other fields do not change.
Then you have to add some content to Web.config.
First add a database connection string:
Secondly add the Caching section:
Here is the test code, execute the following code when the page is loaded:
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 by the DatabaseS section, and the table name is 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 the page is opened for the first time, it will appear:
Create New Cache Value IS XXX.
Refresh the page after display:
Load Data from Cache, Value IS XXX
When you use the query analyzer to modify the data in Authors again, refresh the page will be displayed:
Create New Cache Value IS XXX.
This shows that cache works normally. When the contents of the table Authors change Cache will automatically invalid, my application will re-create a cache object.
Note: SQLCachedependency is supported by two constructor, and SQL Server2000 supports two parameters, a parameter supports SQLServer2005.
If you have incorrect, please refer to it, thank you.
