Description of cache dependency petshop4.0

I. ASP. NET cache Overview

As the main product for developing Web applications under the. NET Framework, ASP. NET fully considers the cache mechanism. In some way, the Data Objects and web pages required by the system are stored in the memory, so that when the web site needs to obtain the data, without complex database connections, queries, and complex logic operations, you can easily and quickly get things at your fingertips.
High performance of the entire web system.

ASP. NET provides two basic caching mechanisms to provide caching functions. One is the application cache, which allows developers to put the data or report business objects generated by the program into the cache. Another caching mechanism is the page output cache, which can be used to directly obtain pages stored in the cache without complicated re-processing of the page.

The implementation principle of application cache is not surprising. It only uses ASP. NET to manage the cache space in the memory. The Application Data Objects in the cache are stored as key/value pairs, which allows you to determine whether the data items in the cache exist based on the key value when accessing the data items in the cache.

The lifecycle of a data object in the cache is restricted. Even during the entire application lifecycle, this data object cannot be valid all the time. ASP. NET can slow down applications
Store, for example, remove data items when they are invalid, expired, or out of memory. In addition, the caller can use cacheitemremovedcallback to delegate and define the callback method.
This allows users to be notified when data items are removed.

In. NET Framework, application caching is implemented through the system. Web. caching. cache class. It is a sealed class and cannot be inherited. For each application domain, a cache instance must be created, and its lifecycle must be consistent with that of the application domain. We can use the Add or insert method to add data items to the application cache, as shown in figure
As shown below:
Cache ["first"] = "first item ";
Cache. insert ("second", "second item ");

We can also add dependencies to the application cache so that when the dependencies are changed, the data items can be removed from the cache:
String [] dependencies = {"Second "};
Cache. insert ("third", "Third item ",
New system. Web. caching. cachedependency (null, dependencies ));

This corresponds to the removal of data items in the cache. As mentioned above, ASP. NET can automatically manage the removal of items in the cache, but we can also explicitly remove related data items through code writing:
Cache. Remove ("first ");

II,Sqlcachedependency

The sqlcachedependency feature is actually reflected by the system. Web. caching. sqlcachedependency class.
This class allows you to monitor specific SQL statements in all supported SQL Server versions (7.0 ,).
Server database table, and create cache items dependent on the table and data rows in the table. When the data of a data table or a specific row in a table is changed, the data items with dependencies will become invalid and automatically
This option is deleted from the cache to ensure that expired data is no longer retained in the cache.
For version reasons, SQL Server
2005 fully supports the sqlcachedependency feature, but for SQL Server 7.0 and SQL Server
In 2000, we were not so lucky. After all, these products appear in. NET Framework
Therefore, it does not automatically monitor data changes in data tables and notify ASP. NET. The solution is to use the polling mechanism to refer
Poll the SQL Server database at a specified interval to track data changes.

To make SQL 7.0 or 2000
Server supports the sqlcachedependency feature. You need to perform relevant configuration steps on the database server. There are two ways to configure SQL
Server: Use the aspnet_regsql command line tool or the sqlcachedependencyadmin class.

1. Use the aspnet_regsql Tool

The aspnet_regsql tool is located in the Windows/Microsoft. NET/framework/[version] folder. If you double-click the execution file of the tool, a wizard dialog box will pop up, prompting us to complete the corresponding operations:

Figure 4-1 aspnet_regsql Tool

The prompt information shown in 4-1 indicates that the Wizard is mainly used to configure the SQL
Server database, such as membership and profiles. to configure sqlcachedependency, run the command line. To
For example, if the database name is mspetshop4, the command is:
Aspnet_regsql-s localhost-e-d mspetshop4-ed

The command parameters of the tool are described as follows:
-? Displays the help function of the tool;
-S is followed by the database server name or IP address;
-U is followed by the login username of the database;
-P is followed by the database login password;
-E this function is used for Windows integrated verification;
-D the following parameter indicates which database adopts the sqlcachedependency function;
-T followed by the sqlcachedependency function for which table the parameter is used;
-Ed allows sqlcachedependency for databases;
-Dd prohibits the use of sqlcachedependency for databases;
-Et allows sqlcachedependency for data tables;
-DT prohibits the use of sqlcachedependency for data tables;
-Lt lists the tables in the current database that have used the sqlcachedependency function.

The preceding command is used as an example to describe how to use the sqlcachedependency function for a database named mspetshop4, and the SQL server adopts the Windows integrated verification method. We can also execute the aspnet_regsql command on the relevant data table, such:
Aspnet_regsql-s localhost-e-d mspetshop4-T item-et
Aspnet_regsql-s localhost-e-d mspetshop4-t product-et
Aspnet_regsql-s localhost-e-d mspetshop4-T category-et

After executing the preceding four commands, the aspnet_regsql tool creates a name in the mspetshop4 Database
The new database table of aspnet_sqlcachetablesforchangenotification. The data table contains three fields. Field tablename
Name of the data table to be tracked, for example, in petshop
In 4.0, the data tables to be recorded include category, item, and product. Icationicationcreated field records the start time of tracing.
As an int field, changeid is used to record the number of times data changes in the data table. 4-2:

 
Figure 4-2 aspnet_sqlcachetablesforchangenotification data table

In addition, executing this command also adds a set of Stored Procedures for the mspetshop4 database to provide query and tracing data tables for ASP. NET.
Sqlcachedependency table add triggers, corresponding to insert, update, delete and other operations related to data changes. For example, number of products
Data Table triggers:
Create trigger DBO. [product_aspnet_sqlcachenotification_trigger] on [product]
For insert, update, delete as begin
Set nocount on
Exec DBO. aspnet_sqlcacheupdatechangeidstoredprocedure n 'product'
End

Aspnet_sqlcacheupdatechangeidstoredprocedure is one of the stored procedures added by the tool. When
When the product data table performs insert, update, or delete operations, the trigger is activated and then executed
Aspnet_sqlcacheupdatechangeidstoredprocedure stored procedure. The execution process is to modify
Value of the changeid field in the aspnet_sqlcachetablesforchangenotification data table:
Create procedure DBO. aspnet_sqlcacheupdatechangeidstoredprocedure
@ Tablename nvarchar (450)
As
Begin
Update DBO. aspnet_sqlcachetablesforchangenotification with (rowlock) set changeid = changeid + 1
Where tablename = @ tablename
End
Go

2. Use the sqlcachedependencyadmin class

We can also manage the use of sqlcachedependency features by programming. This class contains five important methods:

Disablenotifications	Notification of disabling sqlcachedependency object changes for a specific database
Disabletableforpolicications	Disable sqlcachedependency object change notification for a specific table in the database
Enablenotifications	Enable sqlcachedependency object change notification for a specific database
Enabletableforpolicications	Enable sqlcachedependency object change notification for a specific table in the database
Gettablesenabledforpolicications	Returns the list of all tables with sqlcachedependency enabled.

Table 4-1 main sqlcachedependencyadmin Methods

Suppose we have defined the following database connection string:
Const string connectionstr = "Server = localhost; database = mspetshop4 ″;

To enable the sqlcachedependency object change notification for the database mspetshop4, follow these steps:
Protected void page_load (Object sender, eventargs E)
{
If (! Ispostback)
{
Sqlcachedependencyadmin. enablenotifications (connectionstr );
}
}

Enable the sqlcachedependency object change notification for the data table product:
Sqlcachedependencyadmin. enabletablefornotifications (connectionstr, "product ");

If you want to call the related methods shown in Table 4-1, note that the account accessing the SQL Server database must have the permission to create tables and stored procedures. To call the enabletablefornotifications method, you must have the permission to create an SQL Server trigger on the table.

Although programming gives programmers more flexibility, the aspnet_regsql tool provides a simpler way to configure and manage sqlcachedependency. Petshop4.0 adopts the aspnet_regsql tool, which writes a file named installdatabases. the batchcompute file of CMD contains the execution of the aspnet_regsql tool, and the file is called by the installer to configure SQL Server.