Use polling-based SQL data cache dependency

When using the SQL data cache dependency function in sqlserver2000, you have to use a polling mechanism to check whether the data in a specific database and database table has changed.

Three steps:
1. Use the aspnet_regsql command line or the sqlcachedepencyadmmin class to configure the database. The purpose is to enable the database's support for the SQL data cache dependency function.

The adpnet_regsql command line is used to configure database connections, application services, SQL data cache, and session status. It is mainly used to implement two tasks:
First, enable the SQL database cache dependency function of the specified database. Aspnet_regsql-s zgq-U sa-P Sa-D pubs-ed

Second, enable the SQL data cache dependency function for the specified table. Aspnet_regsql-s zgq-U sa-P Sa-D pubs-T titles-et
 
After the two configurations are completed, create a table named "aspnet_sqlcachetablesforchangenotification" in the database. It is a data table automatically created after the data cache dependency of the specified database is enabled. This table is used to record the updated profile information of the data table on which the data cache depends is enabled in the database. The table has three data columns. The column tablename is used to store the name of the updated data table (these tables must enable SQL data cache dependency); the column notifacationcreated is used to store the time when the data table is updated; the column changeid is the value of this field plus one when the data in the tracked data table changes. ASPnet depends on the data of changeid to determine whether to continue using the Data Objects in the cache or performing update operations. In addition, several data storage procedures, roles, and triggers are added to the database. These are automatically generated based on the SQL data cache function.

Obtain the list of data tables that have enabled SQL data caching in the current database.
Aspnet_regsql-s zgq-U sa-P Sa-D pubs-lt

Disable database data cache dependency
Aspnet_regsql-s zgq-U sa-P Sa-D pubs-dd

Disable the data cache dependency function of a data table
Aspnet_regsql-s zgq-U sa-P Sa-D pubs-T titles-dt

You can also use the sqlcachedepencyadmmin class to configure the database to implement the SQL cache dependency function.

We recommend that you use the sqlcachedepencyadmin class in the global. ascx file to manage the SQL cache dependency function in a unified manner:
<% @ Application language = "C #" %>
<% @ Import namespace = "system. Configuration" %>
<% @ Import namespace = "system. Web. Caching" %>

<SCRIPT runat = "server">

Void application_start (Object sender, eventargs E)
{
// Code that runs when the application starts
// The following describes how to enable cache dependency for databases and database tables.
// If the login user does not have the permission to manage the pubs database, an exception will occur.
// Obtain the connection string
String connectionstring = configurationmanager. connectionstrings ["sqlconnectionstring"]. connectionstring;

// The needtoinstall variable indicates whether the data cache dependency function is required.
Bool needtoinstall = true;
Try
{
// Obtain a list of data table names that have the data cache dependency function in the database.
String [] tables = sqlcachedependencyadmin. gettablesenabledfornotifications (connectionstring );

If (tables! = NULL)
{
Foreach (string table in tables)
{
// Set needtoinstall to false if the table for which the cache dependency is to be set is already included
If (table. tolower (). Equals ("titles "))
{
Needtoinstall = false;

}
}
}

}
Catch (exception ex)
{
Needtoinstall = true;

}

// If the needtoinstall value is true, the cache dependency function of database pubs and database table titles is enabled.
If (needtoinstall)
{
Try
{
// Enable the data cache dependency function of the database pubs
Sqlcachedependencyadmin. enablenotifications (connectionstring );

// Enable the titles data cache dependency function of the database table
Sqlcachedependencyadmin. disabletablefornotifications (connectionstring, "titles ");

}
Catch (exception ex)
{

}

}

}

Void application_end (Object sender, eventargs E)
{
// Code that runs when the application is closed

}

Void application_error (Object sender, eventargs E)
{
// Code that runs when an unhandled error occurs

}

Void session_start (Object sender, eventargs E)
{
// The code that runs when the new session starts

}

Void session_end (Object sender, eventargs E)
{
// The code that runs when the session ends.
// Note: Only the sessionstate mode in the web. config file is set
// The session_end event is triggered only when inproc is used. If the session mode is set to StateServer
// Or sqlserver, the event is not triggered.

}

</SCRIPT>
2. Configure the Web. config file