Implementation of ASP. NET MVC Database dependent cache

When information in a database changes, the application is able to get notifications of changes that are the basis for the implementation of cache dependencies. The application can poll to get information about the changes in the data, using polling words can not be re-checked once again and compared with the previous data, if so if I have 1000 rows of data in a table if I have to read 100 times is not to compare the X 100 times, obviously this method is not feasible , what then? Everyone has learned about triggers, and the polling mechanism for database-dependent caching is implemented through triggers.

Simple analysis of the implementation steps:
First, you create a table that records the monitoring information, and the fields of the table are the name of the table and the version number. Then, a trigger is added to the change that needs to be monitored and triggered when the contents of the table change. We can use the Insert Delete Update trigger if the above action is triggered by adding 1 to the version number field of that monitoring table.
The above steps make it difficult for us to do it ourselves, but Microsoft has already provided us with a tool called Aspnet_regsql.exe this tool is located in C:\Windows\Microsoft.NET\Framework\ v4.0.30319 This path (not necessarily but generally the path) Okay, how do you use the tools? Open cmd and enter the following command line to switch to the running root of aspnet_regsql.exe.

CD C:\Windows\Microsoft.NET\Framework\v4.0.30319

Specific help please execute command aspnet_regsql/? See the following list of parameters to be used in this article

• -S server
• -e authenticates with the current Windows credentials
• -U user Name
• -p password
• -d database name, default to ASPNETDB database
• -ed Open SQL cache dependency support for a database
• -DD SQL cache dependent support for shutting down the database
• -ET Specifies the table that the SQL cache relies on and needs to specify the table name using-t
• -DT disable SQL cache dependent tables, use-t to specify table names
• -t specifies the table name
• -LT List of table names that enable cache dependency

You can then enable the home table cache dependency by command against the database house, with the following specific commands:

Aspnet_regsql-s. -e-ed-d house-et-t Home

The following changes occur after the database is run:

1. Added a table called AspNet_SqlCacheTablesForChangeNotification

2. A trigger is added to the home table

SETAnsi_nulls onSETQuoted_identifier onGOALTER TRIGGER [Home_aspnet_sqlcachenotification_trigger]  on [dbo].[Home]                        for INSERT,UPDATE,DELETE  as BEGIN                       SETNOCOUNT on                       EXECDbo. Aspnet_sqlcacheupdatechangeidstoredprocedure N'Home'                       END

3. Added several stored procedures as shown in:

When you open a AspNet_SqlCacheTablesForChangeNotification table, the following fields are found:

Basically the same steps as the beginning of the description, it's a column about time, and manually modify the data in a home table to see what happens to the watch.

The result Changeid value changed! The application is to periodically query the table for changes and then determine whether the cache needs to be updated.

After the database configuration is complete, configure it in the ASP. NET MVC project and open Web. config to add the following code under the system.web node:

<!--database-based cache dependency -    <Caching>      <SqlCacheDependencyenabled= "true"Polltime= "30000">        <databases>          <Addname= "Pubs"connectionStringName= "Pubs"Polltime= "$"/>        </databases>      </SqlCacheDependency>    </Caching>

Where the Polltime attribute is how long the application interval is actively accessed once the database defaults to 1 minutes, with a minimum of 500ms, in Ms.

In the cache class, modify the following core code as required:

SqlCacheDependency dependencies =string. IsNullOrEmpty (tableName)?NULL:NewSqlCacheDependency ("postcachedependency", TableName); if(CacheTime! =0) {caches. Insert (key, data, dependencies, DateTime.UtcNow.AddMinutes (cacheTime), cache.noslidingexpiration);//System.Web.Caching.Cache.NoAbsoluteExpiration                    }                    Else{caches. Insert (key, data, dependencies, System.Web.Caching.Cache.NoAbsoluteExpiration,NewTimeSpan (0, -,0)); }

absoluteexpiration: indicates the time at which the inserted object will expire and be removed from the cache. To avoid possible local time issues, such as changing from a standard time to daylight savings, use the System.DateTime.UtcNow

Instead of System.DateTime.Now as this parameter value. If absolute expiration is used, the slidingexpiration parameter must be System.Web.Caching.Cache.NoSlidingExpiration.

slidingexpiration: represents the time interval between when the inserted object was last accessed and when the object expires. If the value is equivalent to 20 minutes, the object expires and is removed from the cache after the last 20 minutes of access. Simply put, the cache is removed from access within 20 minutes of access. If you use adjustable expiration, the absoluteexpiration parameter must be System.Web.Caching.Cache.NoAbsoluteExpiration.

Implementation of ASP. NET MVC Database dependent cache