SQL data cache dependency [SqlServer

1. Introduction and comparison of SQLSERVER7.02000 and SQLSERVER2005 1.1SQLSERVER7.02000SQLSERVER7.02000 does not provide built-in functions to support data cache dependencies. Therefore, you can only add specific database tables and triggers, check for data changes by continuously polling the database in the background. When running INS on a data table

1. SQL server 7.0/2000 and SQL server 2005 Overview and comparison 1.1 SQL server 7.0/2000 SQL SERVER 7.0/2000 do not provide built-in support for data cache dependencies, therefore, you can only check data changes by adding specific database tables and triggers and continuously polling the database in the background. When running INS on a data table

I. Introduction and comparison of SQL server 7.0/2000 and SQL SERVER 2005

1.1 SQL SERVER 7.0/2000

SQL SERVER 7.0/2000 does not provide built-in functions to support data cache dependencies. Therefore, you can only check data changes by continuously polling the database in the background by adding specific database tables and triggers. When the INSERT, UPDATE, and DELETE operations are performed on the data table, the change notification is automatically sent. Therefore, only the table level can be monitored. The specific row cannot be tracked.

Procedure:

1.1.1 use the aspnet_regsql command line or SqlCacheDependencyAdmin to configure database connection.

1.1.1.1 ALTER DATABASE SET ENABLE_BROKER;

Aspnet_regsql-S -U sa-P sa-d -Ed starts the data cache dependency function of the database.

Aspnet_regsql-S -U sa-P sa-d -T

1.1.1.2

SqlCacheDependencyAdmin. EnableNotifications (connectionString); // starts the data cache dependency function of the database.

SqlCacheDependencyAdmin. EnableTableForNotifications (connectionString, table); // enable data table caching

We recommend that you write this code in the Application_Start method of Global. asax to enable the cache dependency function of the database and data table when the application starts.
1.1.2 configure Web. config

In Under Under a node, if there is only one database, you do not have to specify the next level. Node
1.1.3 use in application data cache (it can also be used in the data source control and output cache throughout the page, which is not described here, the same below)

SqlCacheDependency scd = new SqlCacheDependency ("Database Name", "table name ");

Cache. Insert (..., scd ,...);

1.2 SQL SERVER 2005

Built-in support for SQL data cache dependencies, built-in notification delivery service, and smaller granularity of data change monitoring, easy to use and configure.

Procedure:

1.2.1 check whether Service Broker is enabled
Select DATABASEpRoPERTYEX ('database name', 'isbrokerenabled') -- 1 indicates that 0 has been enabled, indicating that it is not enabled

In this case, I see some of my friends translate "Can I enable it". This is not correct. Here I post the original English text: "This can be checked by calling" Select databasepropertyex ('db name', 'isbrokerenabled ')". A '1' means that the broker is enabled. A '0' means that the broker is not enabled. ".

Based on my experience, if a new database is directly created on SqlServer2005, it is enabled by default. If it is imported from other databases, it is disabled by default after the import. (It may be inaccurate. You can test it yourself ). If it has been enabled, it can be directly adjusted to 1.2.2.

1.2.1.1 enable Service Broker

Alter database name SET ENABLE_BROKER;
Supplement: If you have executed this statement for more than 10 seconds or are in the suspended state, restart the database. Do not execute this statement first!

1.2.2 when implementing service-based SQL data cache dependencies, you must explicitly call SqlDependency. Start to Start the listener that receives the notification of dependency changes.

SqlDependency. Start (connectionString); // we recommend that you add this code to the Application_Start method of Global. asax,
SqlDependency. Stop (connectionString); // used to disable the function. It can be added to the Application_End method of Global. asax.

1.2.3 used in application data cache

SqlCommand cmd = new SqlCommand (SQL, conn );

SqlCacheDependency scd = new SqlCacheDependency (cmd );

Cache. Insert (..., scd ,...);

Note:

A) a data table with a fully qualified name must be set. That is, the table name must be preceded by an owner, such as dbo. test.

B) The name of the accessed database column must be specified, and "*" cannot be used.

C). Make sure it is not an aggregate function. Such as COUNT and MAX.

1.3 comparison and Difference

SQL SERVER 7.0/2000
SQL SERVER 2005

Implementation Mechanism
Round Robin
Service Broker)

Enable or not
Yes
No, built-in support

Data Change Detection
Table-level change monitoring
Table-level and row-level change monitoring

Obviously, the SQL SERVER 2005 cache mechanism is more efficient. In addition, the SqlCacheDependency class is also optimized in combination with SQL SERVER 2005:

A) when SQL SERVER 2005 is used, the SqlCacheDependency class supports integration with the System. Data. SqlClient. SqlDependency class. An application can create a SqlDependency object and register it with the OnChanged event handler. In this way, the application can not only use the query notification mechanism of SQL server 2005 to monitor invalid data changes using SQL query results, but also remove the cached objects from the cache, in addition, you can easily obtain data change notifications to refresh the cache. (From this we can see that when onRemoveCallback is triggered, the data has been deleted from the cache. In this way, you can manually add the cache to the delegate, or simply set it to null, let him cache it the next time he calls it .)

B). Not only can cache dependencies be added to applications, but can also be used with the @ OutputCache command to generate pages or user controls that depend on the output cache of SQL Server database tables. For user controls, the @ OutputCache command does not support the use of SQL SERVER 2005 query notifications (that is, onRemoveCallback delegation ).

Ii. Differences between System. Web. Caching. Cache Insert and Add
2.1 Add Method

Object Add (string key, object value, CacheDependency dependencies, DateTime absoluteExpiration, TimeSpan slidingExpiration, CacheItemPriority priority, CacheItemRemovedCallback onRemoveCallback );
2.2 Insert Method

Void Insert (string key, object value );
Void Insert (string key, object value, CacheDependency dependencies );
Void Insert (string key, object value, CacheDependency dependencies, DateTime absoluteExpiration, TimeSpan slidingExpiration );
Void Insert (string key, object value, CacheDependency dependencies, DateTime absoluteExpiration, TimeSpan slidingExpiration, CacheItemUpdateCallback onUpdateCallback );
Void Insert (string key, object value, CacheDependency dependencies, DateTime absoluteExpiration, TimeSpan slidingExpiration, CacheItemPriority priority, CacheItemRemovedCallback onRemoveCallback );
2.3 comparison and Difference

A) The Insert method supports five types of overloading and is flexible to use. The Add method must provide seven parameters;

B) The Add method can return the Data Object of the cache item, and Insert returns Void;

C) when duplicate cache is added, Insert will replace this option, and the Add method will report an error.

Iii. CacheDependency, AggregateCacheDependency, and SqlCacheDependency

3.1 CacheDependency is the parent class of AggregateCacheDependency and SqlCacheDependency. It is mainly used to establish dependency between an application data cache object and an array of files, cache keys, files or cache keys, or another CacheDependency object. CacheDependency monitors the dependency ratio and automatically removes the cached object when any object changes. CacheDependency can monitor changes to a group of file paths (to files or directories.

3.2 AggregateCacheDependency is mainly used to implement aggregate cache dependencies. For example, if a piece of data is used to cache two tables at the same time, the cache becomes invalid once any of the tables changes data.
3.3 SqlCacheDependency establishes cache dependencies between application data cache objects, page output caches, data source controls, and specified SQL Server database tables or SQL Server 2005 query results, when the table is changed (at the SQL Server 2005 row level), the cache objects associated with the table are automatically deleted and re-added from the cache. Generally speaking:

SqlCacheDependency (SqlCommand) for SQL SERVER 2005

SqlCacheDependency (Database Name, table name) is used for SQL SERVER 7.0/2000

Supplement
1. Example of cache dependency source code

Note: Modify the Web. config database connection code and the startup cache code corresponding to SQL2000 and 2005 in Global. asax!

Www. shenjk. comhttp: // www.shenjk.com/details/700.html

-Et starts the data cache dependency function of the data table.