Enable SQL caching for ASP. NET Applications

Step 1:

The servicebroker service must be enabled for SQL database. First, check whether servicebroker is enabled. The check method is as follows:

Select databasepropertyex ('dbname', 'isbrokerenabled') -- 1 indicates enabled, 0 indicates disabled

If you create a new database, it is enabled by default (1 ). If the database is restored, it is disabled by default (0 ).

Step 2:

If servicebroker is not enabled, use the following statement:

Alter database dbname set enable_broker;

This statement may take a long time. You can restart SQL query editing and then execute the preceding statements.

Step 3:

If you are using a database of SQL Server 2005/2008, you only need to use the following statement in page_load or application_start to enable the SQL cache corresponding to ASP. NET:

SqlCacheDependencyAdmin.EnableNotifications(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);

You need to use aspnet_regsql to configure earlier versions.

In addition, the following statement is used at the beginning:

String[] tables = ConfigurationManager.AppSettings["CacheDataTable"].Split(',');SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString, tables);

The preceding statement always reports an exception: the database is not enabled for the SQL cache notification (I do not know why)

Step 4:

You can write the corresponding cache code through the above steps. The Code is as follows:

Public class tabledependency {protected char [] configurationseparator = new char [] {','}; protected aggregatecachedependency dependency = new aggregatecachedependency (); protected tabledependency (string configkey) {string dbname = configurationmanager. appsettings ["cachedatabasename"]; // name of the database to be cached string tableconfig = configurationmanager. appsettings [configkey]; // obtain the table to be cached from the configuration file, separated by ',' string [] tables = tableconfig. split (configurationseparator); foreach (string tablename in tables) dependency. add (New sqlcachedependency (dbname, tablename); // Add the table and corresponding database to the aggregatecachedependency object} public aggregatecachedependency getdependency () {return dependency ;}}

String phone_head_key = "User _ {0}"; string cachekey = string. format (phone_head_key, ID); // cache key string data = (string) httpruntime. cache [cachekey]; // obtain the corresponding data from the cache first if (Data = NULL) {// obtain data = DB from the database. getdata (); // get an aggregatecachedependency object. The usertabledependency stores the table aggregatecachedependency Cd = new tabledependency ("usertabledependency "). getdependency (); // Add it to the cache httpruntime. cache. add (cachekey, Data, CD, datetime. now. addhours (10), cache. noslidingexpiration, cacheitempriority. high, null);} response. write (data );