Sqlcachedependency uses Round Robin to invalidate Cache

Series directory:

Sqldependency Cache Usage

Asp.net uses sqldependency

Sqlcachedependency use command notifications to invalidate Cache

Cachedependency usage

Aggregatecachedependency usage

Sqlcachedependency uses Round Robin to invalidate Cache

Bytes -------------------------------------------------------------------------------------------------

In SQL Server 7 and later versions, the rotation Detection Technology (polling) can be used to invalidate the cache, but this technology is only supported by SQL Server 7 and SQL Server 2000. For example, you can use the query (command) Notification technology to query SQL server2005 and subsequent versions to invalidate the cache.

Usage

1. When configuring the rotation detection technology, you must first configure each SQL Server database to be inspected, and then configure each table to be inspected.

1) configure the database

Format:

Aspnet_regsql-S server name-e-d database name-et

For example, configure the native nhibernatesample Database

Aspnet_regsql-S.-e-d " Nhibernatesample " -Et

Note: After executing this command, a "aspnet_sqlcachetablesforchangenotification" table will be created in the specified database.

See another format on the Internet, as shown below.

"Aspnet_regsql-S server name-u login name ID-P password-D database name-ed"

 

2) configuration table
Format:

Aspnet_regsql-S server name-e-d Database Name - T table name-et

For example, configure the customer table of the native nhibernatesample Database

Aspnet_regsql-S " . " -E-d " Nhibernatesample "   - T " Customer " -Et

 

Note: After executing this command, a row will be added to the "aspnet_sqlcachetablesforchangenotification" table, and the insert, update, and delete triggers will be created on the customer table.

See another format on the Internet, as shown below.

Aspnet_regsql-S server name-u login name ID-P password-D database name-T tracking data table-et

The notification for disabling this table is:

Aspnet_regsql - S. - E - D "nhibernatesample" - T "customer" - DT

 

 

If you think it is difficult to establish manually. You can alsoProgramUsed inCode. The Code is as follows:

// Database to be detected
Sqlcachedependencyadmin. enablenotifications (
Configurationmanager. connectionstrings [ " Nhibernatesampledb " ]. Connectionstring );
// Table to be checked in the initial detection Database
Sqlcachedependencyadmin. enabletableforconfigurications (
Configurationmanager. connectionstrings [ " Nhibernatesampledb " ]. Connectionstring, " Customer " );

 

2. Write a program.

The following Program is an Asp.net program. Therefore, you need to configure web. config as follows. Note the bold section below.

< Connectionstrings >
<AddName= "Nhibernatesampledb"Providername= "System. Data. sqlclient"  
Connectionstring = "Data Source =.; initial catalog = nhibernatesample;
Persist Security info = true; user id = sa; Password = 123"/>
</ Connectionstrings >
< System. Web >
<Caching>
<SqlcachedependencyEnabled= "True">
<Databases>
<AddName= "Cachedependency_nhibernatesampledb"Connectionstringname= "Nhibernatesampledb"Polltime= "5000"/>
</Databases>
</Sqlcachedependency>
</Caching>
  </ System. Web >

 

 

Add outputcache to the page to cache the page. And depends on sqlcachedependency. The page source code is as follows.

<% @ Page Language = " C # " Autoeventwireup = " True " Codebehind = " Cachedependencypage. aspx. CS "
Inherits = " Sqldependencyinaspnet. cachedependencypage "   %>

<% @ Outputcache duration = " 9999 " Varybyparam = " None " Sqldependency = " Cachedependency_nhibernatesampledb: customer "   %>  

<! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
< Html Xmlns = "Http://www.w3.org/1999/xhtml" >
< Head Runat = "Server" >
< Title > </ Title >
</ Head >
< Body >
< Form ID = "Form1" Runat = "Server" >
< Div >
</ Div >
</ Form >
</ Body >
</ Html >

 

Write the following code. Note the bold part.

Using System;
Using System. Collections. Generic;
Using System. LINQ;
Using System. Web;
Using System. Web. UI;
Using System. Web. UI. webcontrols;

UsingSystem. configuration;
UsingSystem. Data;
UsingSystem. Web. caching;

Namespace Sqldependencyinaspnet
{
Public   Partial   Class Aggregatecachedependencyuse: system. Web. UI. Page
{
Protected   Void Page_load ( Object Sender, eventargs E)
{
If ( ! Ispostback)
{
Getdata ();
}
}

Private   Void Getdata ()
{
String Cachedata =   " Content " ;
If (Cache [ " Data " ] =   Null )
{
//Database to be detected
Sqlcachedependencyadmin. enablenotifications (
Configurationmanager. connectionstrings ["Nhibernatesampledb" ]. Connectionstring );
//Table to be checked in the initial detection Database
Sqlcachedependencyadmin. enabletableforconfigurications (
Configurationmanager. connectionstrings [ "Nhibernatesampledb"]. Connectionstring,"Customer ");
Sqlcachedependency SCD= NewSqlcachedependency ("Cachedependency_nhibernatesampledb","Customer ");
Cache. insert ("Data" , Cachedata, SCD );
// You can add other methods here, such as getting data from the database again.
Response. Write ( " Use new content " );
}
Else
{
Cachedata = ( String ) Cache [ " Data " ];
Response. Write ( " Call cache content " );
}
Response. Write (cachedata );
}
}
}

 

 

Final test. After the program is started, refresh and the program will cache data. Modify the table customer in the database and refresh it. This time, the data will be retrieved again, and the outputcache will be updated accordingly.