First, enable the database to support SQL cache dependencies.
Second, enable the table to support SQL cache dependencies.
Third, include the SQL connection string in the Web.config file for the ASP.net application.
Use SQL cache dependencies in the following ways:
1 programming in code to create a SqlCacheDependency object.
2) Add SqlCacheDependency attribute to OutputCache instruction.
3) Add a SqlCacheDependency instance to the response object by Response.addcachedependency.
A brief description of the parameters that enable cache dependencies
-D <database>The name of the database used for SQL cache dependency. The database can use the connection string and the-C option to specify(Must be selected)
-edAllows the database to enable SQL cache dependencies.
-ddPrevents the database from enabling SQL cache dependencies.
-etAllows tables to enable SQL cache dependencies. The-t option is required.
-dtPrevents the table from enabling SQL cache dependency. The-t option is required.
-T <table>Table name that supports or disables SQL cache dependencies. -et or-DT options are required.
-ltLists all tables that enable SQL cache dependencies.
In SQL Server2000Use the cache disable feature (in 2005 You can also set the following settings)
To use the caching disable feature in SQL 2000, you must perform the following two steps:
The first step is to make the appropriate database support this feature.
The second step is to make the table that you want to use support this feature.
Cases:
Aspnet_regsql-s localhost-u sa-p sa-d northwind-ed
Note:Turn on cache dependency on the Northwind database on this computer.
Aspnet_regsql-s localhost-u sa-p sa-d northwind-dd
Note:Disables caching dependencies for the Northwind database on this computer. When you use the secondary command, the cache disabling feature for all tables in the database is automatically turned off and the AspNet_SqlCacheTablesForChangeNotification table is automatically deleted.
Turn on cache dependency on the table.
Cases:
Aspnet_regsql-s localhost-u sa-p sa-d northwind-t products-et
Note:Turn on caching-dependent functionality for the Products table in the Northwind database.
Aspnet_regsql-s localhost-u sa-p sa-d northwind-t products-dt
Note:Turn off caching dependency for the Products table in the Northwind database. This approach enables caching-dependent functionality for one or more tables in a database. After you turn on caching dependencies for databases and tables, you can discover that a aspnet_sqlcachetablesforchangenotification table has been added to the Northwind database, indicating that the configuration was successful.
Aspnet_regsql-s localhost-u sa-p sa-d northwind-lt
Use the-LT parameter to see which tables in the database have cache dependency enabled. Using this command will get an error if the database shuts down cache dependency or does not enable caching-dependent functionality.
About SQL 2005 Cache disabling feature
SQL Server 2005 supports programming and declarative technologies for caching. Use the CommandNotification string in the Oupputcache directive to create a cache based on notifications for the page. You can programmatically specify the SQL cache option, or declare the option, but you cannot use both methods at the same time. SQL2005 cache dependencies require no Web.config configuration, no caching dependencies for databases and tables, and since 05 has a notification delivery service built into it that detects data changes and implements change notification delivery, there is no need to constantly poll the database to detect data updates.
Note: Although 05 of the cache dependency is simple, but I have not found any more relevant information, has not been tested in person, the main reason is not clear how to open the broker service, and later have time to test it.
Configuring the ASP.net application
After you use the database and several of these tables to support the SQL Server cache disabling feature, configure the application for the SQL Server cache disabling feature.
To configure your application to use the SQL Server cache disabling feature, you should first modify the Web.config file. In this file, specify the database to use and let ASP.net connect to it.
Such as:
<connectionStrings>
<addName= "NorthwindConnectionString"connectionstring= "Data source=.;i Nitial Catalog=northwind; User Id=sa; Password=sa "
Providername= "System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<caching>
<sqlcachedependency enabled= "true" Polltime= ">"
<databases>
<clear/>
<add name= "Northwind" connectionStringName = "NorthwindConnectionString" polltime= ""/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
Note: First use the <connectionStrings> element in the Web.config file to establish a connection string to the database. The SQL Server cache feature is then configured with the <caching> element. If you use SQL Server cache dependencies, you must use the <sqlcachedependency enabled= "true" > node and enable this feature. Reference the database in the <databases> node. Description of the properties in the
<add> element:
Name: Provides an identifier for the SQL Server database.
connectionStringName: Specifies the name of the connection.
polltime: Specifies the interval for SQL Server rotation detection, which defaults to 5 seconds or 500 milliseconds. The SQL 2005 notification feature does not require this property. When the
Web.config file is configured correctly, you can disable features on the page using the SQL Server cache. Asp. NET puts SQL Server requests on a completely different thread in the AspNet_SqlCacheTablesForChangeNotification table to see if the Changeid number is incremented and if that number changes, ASP. NET knows that the underlying SQL Server table has been modified to retrieve a new result set. It determines whether SQL Server calls should be made, and the request to the AspNet_SqlCacheTablesForChangeNotification table gets a result, which executes very quickly after the SQL Server cache disabling feature is started.
adds multiple tables to the page, as follows (separated by semicolons):
<%@ OutputCache duration= "3600" varybyparam= "None" Sqldependency= "Northwind:employees; Northwind:customers "%>
