Sqlcachedependency use query (command) Notifications 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 -------------------------------------------------------------------------------------------

 

 

Database cache dependency (Sqlcachedependency) The primary solution is to promptly notify the cache and update the data in the cache when the database content changes.

SqlcachedependencyClass in all supportedThe SQL Server version (7.0, 2000,200 5) monitors specific SQL Server database tables to automatically delete the items associated with the table from the cache when the table is changed. When the database table is changed, the cache items are automatically deleted and new versions are added to the cache. When using SQL Server 2005 databases, the sqlcachedependency class also supports integration with the system. Data. sqlclient. sqldependency class. Use the query notification mechanism of SQL Server 2005 to detect data changes that make the SQL query results invalid. Any cache items associated with SQL queries will be removed from system. Web. caching. cache. When using SQL Server 2005, you can use the sqlcachedependency class to applyProgramTo add items that depend on SQL Server database tables or SQL queries.

Usage:

1. Enable CLR
When a message arrives in a service queue,CodeSp_dispatcherproc will use a queue to send messages. Therefore, you must enable the CLR function in SQL Server. You can use the following method to enable the Service:

Use Master
Exec Sp_configure ' CLR Enabled ' , 1
Reconfigure

 

2. enable service broker. You can use the following statement to check whether the feature is enabled. Select Databasepropertyex ( ' Northwind ' , ' Isbrokerenabled ' )
-- 1 indicates true, 0 indicates false

 

The Service Broker startup statement is as follows:

Use Master
Alter   Database Northwind Set Enable_broker

 

3. grant permissions to your database access account (this step is not performed for my SA account)

Grant Subscribe query configurications To User

Note: This step is very important. If you do not have the permission, the notification of database changes will not be received, and the cache will never be refreshed. I started to use the SA account, and I will not refresh it all, it took me two days to debug this problem and I still cannot grant this permission to SA (MS forbidden). So, just change the database access account.

 

4. Add a database connection string

< Connectionstrings >
< Add Name = "Nhibernatesampledb" Providername = "System. Data. sqlclient"  
Connectionstring = "Data Source =.; initial catalog = nhibernatesample; persist Security info = true; user id = sa; Password = 123" />
</ Connectionstrings >

 

5. Start the sqldependency listener

Using System. Data. sqlclient;
Using System. Web. caching;

NamespaceSqldependencyinaspnet
{
Public ClassGlobal: system. Web. httpapplication
{

Protected   Void Application_start ( Object Sender, eventargs E)
{
// Code that runs when the application starts
Sqldependency. Start (system. configuration. configurationmanager. connectionstrings [ " Nhibernatesampledb " ]. Tostring ());
}

Protected   Void Application_end ( Object Sender, eventargs E)
{
Sqldependency. Stop (system. configuration. configurationmanager. connectionstrings [ " Nhibernatesampledb " ]. Tostring ());
}
}
}

 

6. Main Program

Put a gridview on the sqlcachedepencypage. ASPX page to display data.

Using System. Data;
Using System. configuration;
Using System. Data. sqlclient;
Using System. Web. caching;

NamespaceSqldependencyinaspnet
{
Public Partial ClassSqlcachedepencypage: system. Web. UI. Page
{

String Connectionstring = Configurationmanager. connectionstrings [ " Nhibernatesampledb " ]. Tostring ();
Protected   Void Page_load ( Object Sender, eventargs E)
{
If ( ! Ispostback)
{
Getcachedependencydata ();
}
}

///   <Summary>
/// Obtain information
///   </Summary>
Private   Void Getcachedependencydata ()
{
If (Cache [ " Tabledate " ] ! = Null )
{
System. Diagnostics. Debug. writeline ( " Call once " );
}
Else
{
Using (Sqlconnection CN =   New Sqlconnection (connectionstring ))
{
Using (Sqlcommand cmd = CN. createcommand ())
{
CN. open ();
Cmd. commandtext =   " Select [customerid], [firstname], [lastname], [version] from [DBO]. [Customer] " ;
Sqlcachedependency Dep =   New Sqlcachedependency (CMD );
// When there is a DML operation, the onchange event will receive messages sent from SQL Server to the application through the sq_dispatcherproc stored procedure.
Datatable dt =   New Datatable ();
Using (Sqldataadapter Adapter =   New Sqldataadapter ()) // Query data
{
Adapter. selectcommand = CMD;
Adapter. Fill (DT );
}
Cache. insert ( "Tabledate" , DT, DEP );
}
}
}

Gvdata. datasource=(Datatable) cache ["Tabledate"];
Gvdata. databind ();
}

}
}

Pay attention to the red and bold parts in the above Code.

 

7. Test

Run the program. Modify the content in the data table. Refresh the page and you will find that the cache is empty. When you do not modify any content, the cache will exist.