Initial encounter with the ASP. NET MVC database depends on the cache.

Issue background:

　　A very simple feature recently was to request a drop-down list of data from the server using the way Ajax was requested.

have done this function before, but this time to do this feature comes up with an idea:

The data I requested is a relatively fixed piece of data, which means it doesn't change very much, and maybe it will change only a few months. Because this kind of data change period is very long, so when this function is used, it will use the cache to optimize, can read the data directly from the cache, avoids every time receives the AJAX request to want the data to the database, reduces the server and the database interaction, alleviates the database server pressure. But the problem is, the change in the data cycle is going to change, and when the data in the database changes, you have to remove the old cache content (remove) , OK, I did this before:

 PublicActionResult Getallfjs () {//try to fetch data from the cache            varFjidnames = httpcontext.cache["Fjidnames"]; if(NULL= = Fjidnames)//no data in cache            {                //querying data from a databaseFjidnames = Fjservice.getall (). Select (FJ =New{Id = fj. Id, Name =FJ. Name}).                ToArray (); //cache data for the next use (set a cache validity time, use absolute expiration, reach the specified expiration time, cache invalidation) HttpContext.Cache.Insert("Fjidnames",//Key of the cache entry (string)Fjidnames,//the value of the cache entry (object)                                          NULL, DateTime.UtcNow.AddMinutes ( -),//Absolute Expiry time (DateTime)System.Web.Caching.Cache.NoSlidingExpiration//Sliding expiration interval (TimeSpan)                                        ); }            //Convert The resulting data into a JSON-formatted string            stringJsonresult =commonhelper.converttojsonstr (fjidnames); //The result string returned to the browser            returnContent (Jsonresult); }

Description: This is an action method that handles a request in ASP. NET MVC, which has an important method

public void Insert (

String key, the key of the cache key

Object value, the value of the cache entry

CacheDependency dependencies,--cache dependencies (not here, use later, plays)

DateTime absoluteexpiration, and Absolute expiration point in time

TimeSpan slidingexpiration--Swipe expiration time interval

);

Here is the use of the cache expiration time to update the cached data, each time the cache data after 30 minutes to re-fetch the data from the database to update the contents of the cache, to see the results of the execution ( in order to display the results, the absolute expiration time is adjusted to 30s):

first time request :

(Data at the moment in the database table):

(Execution result):

The first request is that there is no data in the cache, so you want to data to the database;

Second Request :

(Change the data in the corresponding table in the database before the request)

(Execution result):

Second request, because there is no absolute expiration time of the cache entry, there is data in the cache, not to the database to data, from the cache out of the row;

request after 30s :

(Data at the moment in the database table):

(Execution result):

The data in the cache has expired and is removed and needs to be re-requested from the database;

　　As you can see from the above three requests, it is true that the cached data was updated.

But you can also see that there is a downside to doing this: The requested data remains in the original cache for the duration of the expiration period, and the data in the database is inconsistent.

The absolute expiration point setting is set based on the tolerance of the actual data refresh, which is the least tolerable in my scenario, and it requires that when the data in the database changes, the corresponding data in the cache After the next request is over , be sure to change it immediately , or you can turn the expiration time as small as possible to one second. Of course, this is a frequent request to the database, it is not a departure from the original use of cache optimization purposes?

So the question now is: is there a way to make a connection between a database and a server program, which is like a "telepathic", when the data in a database table changes, the corresponding cache entry in the server can be "sensed" to this change, so that the original cache entries are invalidated? the answer is, of course, yes.

the database relies on caching, yes, it is, that's it.

　　There are 3 types of dependencies for asp:

• Cache Item Dependencies
• File or folder dependencies
• Database dependencies

This article is about database dependencies: Database cache dependency is a technique that automatically invalidates cached data objects when related data in the database is modified.

Previously did not know that there is a database dependency , have thought of using file or folder dependencies to achieve similar to database-dependent caching functions, the approximate idea is: Use a file as a medium, after the database table data changes, and then change the file to trigger the cache invalidation. Fortunately, I saw the great God wrote the blog post, immediately stop my "stupid" idea, that is actually superfluous, and can not be very good to achieve, there is a database dependent on the existing do not do.

　

Let's take a look at how to use it:

1. Configuration:

1) Add the Web. config file under the current Web site MVC Project

<!--(database connection string) <configuration> node configuration--

<connectionStrings>   <add name="connstr" connectionstring= " server=127.0.0.1; user id=sa; password=root; DATABASE=LZXF  "providername="System.Data.SqlClient "/></connectionstrings >

<!--(Cache database dependency configuration) <system.web> node configuration    <caching>      <sqlcachedependency enabled="true">        <databases>          <add name="lzxf " polltime=" the" Connectionstringname="connstr" />        </databases>      </ Sqlcachedependency>    </caching>    

　　

2) in Application_Start (), add

            // matching Cache database dependencies            string connectionString = system.configuration.configurationmanager.connectionstrings["  ConnStr"]. ToString ();             // data cache dependency capabilities      for starting a database             sqlcachedependencyadmin.enablenotifications (connectionString);             // Enable data table caching              //The second parameter can be an array of single table names or table names

2. Code part

         PublicActionResult Getallfjs () {//try to fetch data from the cache            varFjidnames = httpcontext.cache["Fjidnames"]; if(NULL= = Fjidnames)//no data in cache            {                //querying data from a databaseFjidnames = Fjservice.getall (). Select (FJ =New{Id = fj. Id, Name =FJ. Name}).                ToArray (); //cache data for the next use (using database-dependent caches, cache invalidation when data for corresponding tables in the database changes)HttpContext.Cache.Insert ("Fjidnames", Fjidnames,New SqlCacheDependency("LZXF","T_fjs")); }            //Convert The resulting data into a JSON-formatted string            stringJsonresult =commonhelper.converttojsonstr (fjidnames); //The result string returned to the browser            returnContent (Jsonresult); }

The SqlCacheDependency (Database Cache dependency Class) is one of the most important classes, that is, it establishes a "bridge of communication" between the database and the server program,

Use one of its construction methods:

Public SqlCacheDependency (string databaseentryname, string tableName) to create a database cache dependency class object and pass the method insert to create the cache entry so that the database dependency on the cache entry is established, and the cache entry is destroyed whenever data changes occur in the specified table.

Take a look at the results:

Before changing the database table data:

(Execution result):

After changing the database table data:

(Execution result):

Changed database table data to request data later, request to the latest data, indicating that the old cache was removed

since it will be used, then the next thing to see is the principle, (principle, the most important principle)

After the use of it will be very confused, how it is realized?

Think: The first is to change the data in the database table, this step must be triggered by some kind of operation. Does changing the data in a table in a database trigger an action? It's not about the trigger.

Take a look at what's more in the database:

Open sqlservermanagement View Database discovery

More than one aspnet_sqlcachetablesforchangenotification table, the contents of the table:

The trigger to find is here:

Look at the contents of the trigger:

That is: When the INSERT, UPDATE, DELETE operations occur in the T_FJS table, the dbo is executed. Aspnet_sqlcacheupdatechangeidstoredprocedure This stored procedure, this stored procedure is automatically generated by the cache dependency that we turn on SQL Server

Look for the stored procedure dbo. Aspnet_sqlcacheupdatechangeidstoredprocedure, here:

The content is:

So the final summary of the principle, I quote a great God blog (https://www.lanhusoft.com/Article/290.html) in the summary:

　　When SQL Server enables cache dependencies, the corresponding tables, triggers, and some stored procedures are added to the corresponding database. It is the use of triggers to monitor the table data changes, if there is an increase, delete, change to insert data into the notification table, and then notify the subscription to this notification site This cache entry is invalid.

Finally, there are limitations to using cache dependencies: You must use ASP. NET and SQL Server to develop the application, that is, SqlCacheDependency is a Microsoft-based system.

Reference Post address:

Https://www.cnblogs.com/SkySoot/archive/2012/08/15/2640559.html

Https://www.lanhusoft.com/Article/290.html

This is my first blog post, have you ever wondered why you should write a blog post? Now any search on the Internet has its own content, and some of the great God wrote very well. Later slowly found that every time encountered problems to surf the internet to look at the blog, and many times encountered is the same problem, may have been thinking before, and now also waste time to look at the beginning to see what has been seen, then why I do not write a good collation of the store. Of course, the convenience of their own at the same time, hope can also be convenient to be reading this article of you. Thank you!

Initial encounter with the ASP. NET MVC database depends on the cache.