Home>

When caching database data,The ideal state is that the data always resides in memory,Until the database has changed.In asp.net 2.0, you can use sql cache dependencies by programming or declaring code, and cooperate with sql server 2005 to achieve this function.

preface:

of The caching techniques discussed in Chapters 56 and 57 use time-based caching cycles.After a certain period of time, the cached data is cleared from memory.When the cache time is set to x seconds, the data is "new" in x seconds. Of course, as mentioned in Chapter 60,For static data,x can extend to the entire lifecycle of a web application.

Caching When caching data,Time-period-based technologies are often adopted for their ease of use,But often it's not so perfect.The ideal state is this:the database data should still be cached in memory,It is not cleared from memory until the underlying data changes.In this way, you can maximize the performance benefits brought by the cache.At the same time, the "stale data" duration is minimized.However, for this, we need to establish a mechanism to detect when the database data has changed,And clear the corresponding cache entry.

The sqlcachedependency class and necessary infrastructure provided by asp.net 2.0 can determine when the database has changed.In order to clear the corresponding cache entry from memory.There are two techniques to determine when the source data has changed:polling and notification. After discussing the differences between the two,We will create the necessary underlying infrastructure to support polling, and then explore how to use the sqlcachedependency class

Understanding notification and polling

As mentioned earlier,There are two ways to determine when the data in a database has been modified:notification and polling. When using notifications, the database alerts (asp.net) corresponding to a specific query data has changed;The corresponding cache entry will then be cleared.When using polling, the database server will contain information about the most recent changes to a table.asp.net checks the database periodically,See which tables have changed since the data was cached.If changed,The corresponding cache entry will be cleared.

Notification is to check the query (query) instead of the table (table),Relative to polling, fewer steps need to be taken.Unfortunately, however,This feature is only available in the full version of Microsoft SQL Server 2005 (that is, the non-express version).And all versions of Microsoft SQL Server,Polling can be used from 7.0 to 2005, because this series of tutorials uses the express version of SQL Server 2005. Here we will focus on the establishment and use of polling. For the notification function of SQL Server 2005, you can refer to further reading at the end of this article.

To use polling, we will set up the database to contain a table named aspnet_sqlcachetablesforchangenotification. The table has 3 columns:tablename, notificationcreated, and changeid. For the tables that are used in the sql cache dependency of the web application,The table has a record corresponding to it.tablename is the name of a specific table;notificationcreated specifies the date and time when the record was added;the type of the column changeid is int, and the initial value is 0. Whenever the corresponding table is changed,Its value is automatically increased once.

In addition to the table aspnet_sqlcachetablesforchangenotification, the database also needs to include triggers for each table that appears in the sql cache dependency. At any time,As long as the table inserts, updates, deletes a record, or the corresponding changeid value in the aspnet_sqlcachetablesforchangenotification table increases, the trigger will be executed.

When using the sqlcachedependency object (object) to cache data,asp.net will pay attention to the current (current) changeid value of a table, once it is found that its current value is different from the changeid value in the database,The sqlcachedependency object is cleared.Because the changeid does not match, it means that after the data cache is completed,The table has changed again.

Step 1:Examine the command line program aspnet_regsql.exe

As mentioned above,When using the polling method, the database must be set up to include these basic structures:a pre-defined table (aspnet_sqlcachetablesforchangenotification), some stored procedures,And triggers based on the tables to be used in sql cache dependencies.Such tables, stored procedures, triggers, etc. can be created through the command line program aspnet_regsql.exe, which is located in the $windows $/microsoft.net/framework/version folder. To create the table aspnet_sqlcachetablesforchangenotification and related stored procedures,This can be run from the command line:

/* for sql server authentication ... * /
aspnet_regsql.exe -s server -u user -p password -d database -ed
/* for windows authentication ... * /
aspnet_regsql.exe -s server -e -d database -ed

Note:To run these commands,The database must be logged in as db_securityadmin and db_ddladmin,See the author's blog for more details:

For example:in windows authentication mode,When adding infrastructure to database pubs in a database server scottsserver,At the command line, type:

aspnet_regsql.exe -s scottsserver -e -d pubs -ed

After the database-level infrastructure has been added,We need to add triggers,Use the aspnet_regsql.exe command again, but specify "table name" with -t and replace -ed with -et, as follows:

/* for sql server authentication ... * /
aspnet_regsql.exe -s<i>server</i>
-u<i>user</i>-p<i>password</i>-d<i>database</i>-t<i>tablename</i>-et
/* for windows authentication ... * /
aspnet_regsql.exe -s<i>server</i>
-e -d<i>database</i>-t<i>tablename</i>-et

To add triggers to the tables authors and titles in scottsserver,this way:

aspnet_regsql.exe -s scottsserver -e -d pubs -t authors -et
aspnet_regsql.exe -s scottsserver -e -d pubs -t titles -et

For the purposes of this article,We want to add triggers to the products, categories, and suppliers.The specific order is discussed in the third step.

Step 2:Reference a Microsoft SQL Server 2005 Express database in the app_data folder

We just said,To add the necessary infrastructure,The aspnet_regsql.exe command requires the name of the database and server.But for a microsoft sql server 2005 express database placed in the folder app_data,What is its database name and server name?Can't find out what the database name and server name are,I found the easiest way is to use sql server management studio (sql server management studio) to recognize the database as localhost/sqlexpress database database and rename it.If the full version of SQL Server 2005 is already installed on your machine, the SQL Service Manager will naturally be installed.If you have the express version installed,You can download microsoft sql server management studio express edition for free.

()

First, close visual studio, then open sql server management studio, and select connect to localhost/sqlexpress in windows authentication mode.

Figure 1:Connect to localhost/sqlexpress server

After connecting to the server,The manager will show the server,And the database, security, etc. are displayed in a folded form.Right-click on the database folder,Select the "attach" item. This will bring up the "attach databases" dialog box (see Figure 2). Click the add button and select the northwnd.mdf database in the app_data folder of our web application.

Figure 2:Selecting the northwnd.mdf database in the app_data folder

This will add the database to the databases folder, and the name of the database may be the full path of the database file. For simplicity,We renamed it to a more human-friendly name, and I named it "datatutorials".

Figure 3:Rename the newly added database

Step 3:Add polling infrastructure to northwind database

Now that we have added the northwnd.mdf database in the app_data folder, let's add the polling infrastructure.Assuming you have renamed the database to "datatutorials", run the following command:

aspnet_regsql.exe -s localhost/sqlexpress -e -d datatutorials -ed
aspnet_regsql.exe -s localhost/sqlexpress -e -d datatutorials -t products -et
aspnet_regsql.exe -s localhost/sqlexpress -e -d datatutorials -t categories -et
aspnet_regsql.exe -s localhost/sqlexpress -e -d datatutorials -t suppliers -et

After completing the above 4 commands,Right click on the database in management studio,Enter the tasks submenu and select detach. Then close management studio and reopen visual studio.

After opening visual studio, expand the database in Server Explorer,You can see that there are new tables (aspnet_sqlcachetablesforchangenotification), new stored procedures,And triggers corresponding to the products, categories, and suppliers.

Figure 4:The database contains the necessary polling infrastructure

Step 4:Set up polling service

After completing the above steps,Finally we need to set up the polling service. This requires the web.config file, which specifies the database to be used,And the detection frequency (polling frequency) in milliseconds.The following code checks the northwind database every 1 second.

<?xml version="1.0"?>
<configuration>
 <connectionstrings>
 <add name="northwndconnectionstring" connectionstring =
  "data source =./sqlexpress;attachdbfilename=| datadirectory | /northwnd.mdf;
  integrated security=true;user instance=true "
  providername="system.data.sqlclient" />
 </connectionstrings>
 <system.web>
 ...
 <!-Configure the polling service used for sql cache dependencies->
 <caching>
  <sqlcachedependency enabled="true" polltime="1000">
  <databases>
  <add name="northwinddb"
   connectionstringname="northwndconnectionstring" />
  </databases>
  </sqlcachedependency>
 </caching>
 </system.web>
</configuration>

NameThe name value ("northwinddb") in theelement is a human-readable name that corresponds to a specific database.When using sql cache dependencies, we need to reference the database name defined here.We will examine how to use the sqlcachedependency class to cache data in step 6.

Once a sql cache dependency is determined, the detection system (polling system) connects to the database in theelement every so many milliseconds of the defined polltime,And execute the stored procedure named aspnet_sqlcachepollingstoredprocedure-this stored procedure was added in the third step using the aspnet_regsql.exe command line toolIt returns the tablename and changeid values ​​of each record in the aspnet_sqlcachetablesforchangenotification table. Those "stale" SQL cache dependencies will be cleared from memory.

PoPolltime should be set on the basis of trade-offs between performance and data staleness. Although a smaller polltime value results in an increase in the number of database requestsBut it can clear "outdated" data faster;Although a larger polltime value reduces the number of requests to the database,But it increases the dead time of "stale" cache entries.Fortunately, the request for the database is just a simple stored procedure,The stored procedure returns only a few rows from a simple table.You'd better test several different polltime values ​​to find an ideal value while balancing database access and data refresh.The minimum allowed polltime value is 500.

Note:In the code above,We specified a single polltime value in theelement. In fact, you can optionally specify a polltime value in theelement. When you specify multiple databases,This is useful when i want to specify a polling frequency for each database.

Fifth:declare sql cache dependencies

In the first to fourth steps,We explored how to build the necessary database infrastructure,And set up a polling system. After completing the above steps,Now we can programmatically or declaratively,Use sql cache dependency when adding cache entries. In this section, we explore how to use sql cache dependencies in a declarative way, and then explore the programmatic way in the sixth step.

in 《Use objectdatasource to cache data》 In the tutorial, we examined the caching capabilities of the declared objectdatasource control.Just set the enablecaching property to true and set the acheduration property to a time interval, and the objectdatasource control will automatically cache the data returned from the "underlying object".The objectdatasource control can use single or multiple sql cache dependencies.

To do this, open the sqlcachedependencies.aspx page in the folder cache. In the design mode,Drag a gridview control from the toolbox onto the page,Set its id to productsdeclarative and bind it from its smart tag to an objectdatasource named productsdatasourcedeclarative.

Figure 5:Create an objectdatasource named productsdatasourcedeclarative

Set up the objectdatasource using productsbll class. In the select tab, select the getproducts () method;in the update tab, select the updateproduct overload method with three input parameters-productname, unitprice, and productidSelect "(none)" in the insert and delete tags.

Figure 6:Overload method using updateproduct with 3 input parameters

Figure 7:Select "(none)" from the drop-down lists of the insert and delete tags

After setting up,Visual Studio creates boundfields and checkboxfieldsl columns for each column in the gridview. Remove columns other than productname, categoryname, and unitprice.Any formatting can be applied to it.Enable the paging, sorting, and editing functions in the gridview's smart tags.visual studio will set the oldvaluesparameterformatstring property of the objectdatasource control to original_ {0}, in order to make the editing function of the gridview run normally,Either delete the attribute,Or set it to the default value:{0}.

Finally, add a label web control on the gridview, set its id to oddsevents, and set its enableviewstate property to false. After completing the above modification,The page declaration code should look similar to the following.Note that I have made some customizations to the appearance of the gridview columns,Although this is not necessary for the sql cache dependency function.

<asp:label runat="server" enableviewstate="false" />
<asp:gridview runat="server"
 autogeneratecolumns="false" datakeynames="productid"
 datasourceid="productsdatasourcedeclarative"
 allowpaging="true" allowsorting="true">
 <columns>
 <asp:commandfield showeditbutton="true" />
 <asp:templatefield headertext="product" sortexpression="productname">
  <edititemtemplate>
  <asp:textbox runat="server"
   text="<%#bind (" productname ")%>" />
  <asp:requiredfieldvalidator
   controltovalidate="productname" display="dynamic"
   errormessage="you must provide a name for the product."
   setfocusonerror="true"
   runat="server">*</asp:requiredfieldvalidator>
  </edititemtemplate>
  <itemtemplate>
  <asp:label runat="server"
   text="<%#bind (" productname ")%>" />
  </itemtemplate>
 </asp:templatefield>
 <asp:boundfield datafield="categoryname" headertext="category"
  readonly="true" sortexpression="categoryname" />
 <asp:templatefield headertext="price" sortexpression="unitprice">
  <edititemtemplate>
  $<asp:textbox runat="server" columns="8"
   text="<%#bind (" unitprice "," {0:n2} ")%>"></asp:textbox>
  <asp:comparevalidator runat="server"
   controltovalidate="unitprice"
   errormessage="you must enter a valid currency value with
   no currency symbols. also, the value must be greater than
   or equal to zero. "
   operator="greaterthanequal" setfocusonerror="true"
   type="currency" display="dynamic"
   valuetocompare="0"&​​gt;*</asp:comparevalidator>
  </edititemtemplate>
  <itemstyle horizontalalign="right" />
  <itemtemplate>
  <asp:label runat="server"
   text="<%#bind (" unitprice "," {0:c} ")%>" />
  </itemtemplate>
 </asp:templatefield>
 </columns>
</asp:gridview>
<asp:objectdatasource runat="server"
 selectmethod="getproducts" typename="productsbll"
 updatemethod="updateproduct">
 <updateparameters>
 <asp:parameter name="productname" type="string" />
 <asp:parameter name="unitprice" type="decimal" />
 <asp:parameter name="productid" type="int32" />
 </updateparameters>
</asp:objectdatasource>

Next, create an event handler for the selecting event of the objectdatasource control:

protected void productsdatasourcedeclarative_selecting
 (object sender, objectdatasourceselectingeventargs e)
{
 odsevents.text="-selecting event fired";
}

we know,The selection event of an objectdatasource control is triggered only when it gets data from its associated "underlying object". If objectdatasource is retrieving data from memory,The selecting event will not fire.

Now log in to this page in your browser,Because we haven't cached it yet,So whenever you paginate, sort, and edit, the page will display the text — "— selecting event fired", as shown in Figure 8:

Figure 8:The selection event of objectdatasource is triggered when pagination, sorting, and editing.

Just like in our tutorial "Use objectdatasource to cache dataThe same thing is discussed in "In addition to the enablecaching attribute,The objectdatasource control also has a sqlcachedependency property property, which can add one or more sql cache dependencies to the cached data. Like this:

databasename1:tablename1;databasename2:tablename2;...

Where databasename is the name of the database specified by the name attribute of theelement in the web.config file,The tablename is a table in the database.For example, to create an objectdatasource, it uses sql cache dependency to cache data,When we specified to use the products table in the northwind database, we set the enablecaching property of the objectdatasource to true and the sqlcachedependency property to "northwinddb:products".

Note:You can use an sql cache dependency and a time-based expiry by setting the enablecaching property to true. cacheduration corresponding time interval;sqlcachedependency corresponds to the database name and table name.Whether the cache expires or the polling system finds that the "source data" has changed,As long as one of them happens, objectdatasource will clear its data.

of The gridview control in the sqlcachedependencies.aspx page gets data from 2 tables-products and categories (the categoryname column of the product is obtained by the syntax join on categories). Therefore, we want to specify 2 sql cache dependencies:

"Northwinddb:products;northwinddb:categories".

Figure 9:Setting objectdatasource to support caching,And use sql cache dependencies based on tables products and categories

After setting sql cache dependencies to support caching,Come to the login page in the browser again.Initially, the text "—selecting event fired" will still appear on the page,But when paginating, sorting, or clicking the edit and cancel buttons,The text disappears.This is because after the data is cached,Its cache state is persistent,Until the products or categories table changes,Or we updated the data through gridview.

Do an experiment,Pagination in the first browser window,Note that the text "—selecting event fired" does not appear.Open a second browser window,Navigate to the basics.aspx page (~/editinsertdelete/basics.aspx). Update the name or price of a product. Return to the first browser window again,View the next page or sort or click the edit button for a row.This time, the text "—selecting event fired" appeared again,This is because the "source data" has changed (see Figure 10). If the text does not appear,Please wait a moment and try again.we know,The polling service checks the products table every set milliseconds.Whether it has been changed.There is therefore a delay between the update of the source data and the clearing of "stale" data.

Figure 10:Modifying the products table will cause the "stale" product cache data to be cleared

Step 6:Programmatically handle the sqlcachedependency class

In the tutorial "Cache data in a layered architecture》 We saw the benefits of using a separate caching layer. In that tutorial,We created a productscl class to handle the data cache. To use sql cache dependencies in the cache layer, use the sqlcachedependency class.

In the polling system, a sqlcachedependency object must be linked to a specific database and table.The following code,Created a sqlcachedependency object, which is based on the products table of the northwind database:

caching.sqlcachedependency productstabledependency =
 new caching.sqlcachedependency ("northwinddb", "products");

The above two parameters correspond to the database name and table name, respectively.Similar to the attribute sqlcachedependency of the objectdatasource control, the database name is the value specified by the name attribute of theelement in the web.config. File used,And the table name is the actual database table name..

To associate a sqlcachedependency with an entry added to memory,You can use an overloaded insert method that accepts dependencies. The sqlcachedependency in the code below is based on the table products, and the cache time is undecided.in other words,The data is always kept in memory,They are not cleared unless there is insufficient memory or the table products have changed.

caching.sqlcachedependency productstabledependency =
 new caching.sqlcachedependency ("northwinddb", "products");
cache.insert (key,  value,  productstabledependency,  system.web.caching.cache.noabsoluteexpiration,  system.web.caching.cache.noslidingexpiration);

Currently, the productscl class of the cache layer gets data from the table products,The cache time is 60 seconds. Let's update it,Make it use sql cache dependencies. The addcacheitem method of class productscl is used to add data to memory,Its current code is as follows:

private void addcacheitem (string rawkey, object value)
{
 system.web.caching.cache datacache=httpruntime.cache;
 //make sure mastercachekeyarray [0] is in the cache
 datacache [mastercachekeyarray [0]]=datetime.now;
 //add a cachedependency
 caching.cachedependency dependency =
 new caching.cachedependency (null, mastercachekeyarray);
 datacache.insert (getcachekey (rawkey), value, dependency, datetime.now.addseconds (cacheduration), system.web.caching.cache.noslidingexpiration);
}

Let's update it,Replace mastercachekeyarray cache dependency with a sqlcachedependency object:

Let's test it.Add a gridview under the gridview control named productsdeclarative, set its id to productsprogrammatic, bind it in its smart label to a new objectdatasource named productsdatasourceprogrammatic, set the objectdatasource to use the productscl class, in select and update Select the getproducts and updateproduct methods in the tag.

Figure 11:Setting up a new objectdatasource to use the productscl class

Figure 12:Select the getproducts method in the select tab

Figure 13:Select updateproduct method in the update tab

After setting up,Visual Studio will automatically add boundfields and checkboxfields to the gridview control. Just like the gridview control above,Remove all columns except productname, categoryname, and unitprice.In its smart tag,Enable paging, sorting, and editing functions.At the same time, in order to make the editing function of the gridview control work properly,Change the oldvaluesparameterformatstring property to the default value of {0}. Or simply delete the property in the code declaration.

After making the above changes,The final gridview and objectdatasource declaration code should look similar to the following:

<asp:gridview runat="server"
 autogeneratecolumns="false" datakeynames="productid"
 datasourceid="productsdatasourceprogrammatic" allowpaging="true"
 allowsorting="true">
 <columns>
 <asp:commandfield showeditbutton="true" />
 <asp:templatefield headertext="product" sortexpression="productname">
  <edititemtemplate>
  <asp:textbox runat="server"
   text="<%#bind (" productname ")%>" />
  <asp:requiredfieldvalidator
   controltovalidate="productname" display="dynamic"
   errormessage="you must provide a name for the product."
   setfocusonerror="true"
   runat="server">*</asp:requiredfieldvalidator>
  </edititemtemplate>
  <itemtemplate>
  <asp:label runat="server"
   text="<%#bind (" productname ")%>" />
  </itemtemplate>
 </asp:templatefield>
 <asp:boundfield datafield="categoryname" headertext="category"
  readonly="true" sortexpression="categoryname" />
 <asp:templatefield headertext="price" sortexpression="unitprice">
  <edititemtemplate>
  $<asp:textbox runat="server" columns="8"
   text="<%#bind (" unitprice "," {0:n2} ")%>"></asp:textbox>
  <asp:comparevalidator runat="server"
   controltovalidate="unitprice" display="dynamic"
   errormessage="you must enter a valid currency value with
   no currency symbols. also, the value must be greater than
   or equal to zero. "
   operator="greaterthanequal" setfocusonerror="true"
   type="currency" valuetocompare="0"&​​gt;*</asp:comparevalidator>
  </edititemtemplate>
  <itemstyle horizontalalign="right" />
  <itemtemplate>
  <asp:label runat="server"
   text="<%#bind (" unitprice "," {0:c} ")%>" />
  </itemtemplate>
 </asp:templatefield>
 </columns>
</asp:gridview>
<asp:objectdatasource runat="server"
 oldvaluesparameterformatstring="{0}" selectmethod="getproducts"
 typename="productscl" updatemethod="updateproduct">
 <updateparameters>
 <asp:parameter name="productname" type="string" />
 <asp:parameter name="unitprice" type="decimal" />
 <asp:parameter name="productid" type="int32" />
 </updateparameters>
</asp:objectdatasource>

To test the sql cache dependency in the cache layer, first set a breakpoint in the addcacheitem method of the productcl class, and then start debugging.When you log in to the sqlcachedependencies.aspx page for the first time, a breakpoint should occur.Because this is the first time requesting data,And add the data to memory.Then, in gridview, go to the next page or sort a certain column.This will cause the gridview control to query the required data,The data should still be in memory because the products of the table have not changed.If you ca n’t find the data you need in memory,Make sure you have enough memory,Then try again.

Jump Jump a few more pages in gridview,Open another browser window,Navigate to the basics.aspx page (~/editinsertdelete/basics.aspx). Update a record.Back to the first browser window,Jump to the page or implement sorting.

At this point, you will encounter one of the following two situations:either a breakpoint occurs in the program,Remind you that the data has been cleared,The original image is that the database has changed;Either the program has no breakpoints,This means that the page sqlcachedependencies.aspx is displaying "stale" data. If no breakpoint occurs,It is likely that the polling service was not triggered when the data changed. We know thatThe polling service checks the products table every set milliseconds.See if it has changed.There is therefore a delay between the update of the source data and the clearing of "stale" data.

Note:The delay is likely to occur when we edit the product information in the gridview in the sqlcachedependencies.aspx page.In the tutorial "Cache data in a layered architecture》, We add the mastercachekeyarray cache dependency to ensure that data is cleared from memory.But when we modified the addcacheitem method earlier, we replaced it.Therefore the productscl class will continue to display "outdated" data until the detection system finds that the products have changed.We will see how to reintroduce mastercachekeyarray cache dependency in step 7.

Step 7:Append multiple dependencies to cache entries

we know, The use of mastercachekeyarray cache dependency is:in all entries related to product,Whenever any of the relevant data changes,All entries will be cleared.For example, the getproductsbycategoryid (categoryid) method obtains and caches multiple product records based on the specified categoryid.As long as any of these records are cleared, The mastercachekeyarray cache dependency will ensure that the remaining records are also cleared.

possibility Without mastercachekeyarray cache dependency, this possibility exists,When an entry is changed,The remaining entries still reside in memory and appear to be "stale." Therefore, it is important to include mastercachekeyarray cache dependency when using SQL cache dependencies.However, the insert method of the data cache allows only one dependency object to exist.

In addition, when using sql cache dependencies, we may have to rely on multiple tables.For example, the productsdatatable of the productscl class also contains the category and supplier names of each product. However, in the addcacheitem method, it only depends on the table products. Assume that if the user updates the category or supplier,Then the cached product data still resides in memory,Obviously "outdated". Therefore, the product data that we want to cache depends on not only the products table, but also the categories and suppliers tables.

But the class aggregatecachedependency provides this way,Associate a cache entry with multiple dependencies.First, create an aggregatecachedependency instance;then use the add method of aggregatecachedependency to add the set dependencies. When any dependency in the aggregatecachedependency instance is changed,The cache entry is cleared.

The following code is the updated addcacheitem method of the productscl class. This method not only creates the mastercachekeyarray cache dependency, but also creates multiple sqlcachedependency objects based on the tables products, categories, and suppliers. Then combine them to form an aggregatecachedependency object named aggregatedependencies, which is passed to the insert method.

private void addcacheitem (string rawkey, object value)
{
 system.web.caching.cache datacache=httpruntime.cache;
 //make sure mastercachekeyarray [0] is in the cache and create a depedency
 datacache [mastercachekeyarray [0]]=datetime.now;
 caching.cachedependency mastercachekeydependency =
 new caching.cachedependency (null, mastercachekeyarray);
 //add the sqlcachedependency objects for products, categories, and suppliers
 caching.sqlcachedependency productstabledependency =
 new caching.sqlcachedependency ("northwinddb", "products");
 caching.sqlcachedependency categoriestabledependency =
 new caching.sqlcachedependency ("northwinddb", "categories");
 caching.sqlcachedependency supplierstabledependency =
 new caching.sqlcachedependency ("northwinddb", "suppliers");
 //create an aggregatecachedependency
 caching.aggregatecachedependency aggregatedependencies =
 new caching.aggregatecachedependency ();
 aggregatedependencies.add (mastercachekeydependency, productstabledependency, categoriestabledependency, supplierstabledependency);
 datacache.insert (getcachekey (rawkey), value, aggregatedependencies, caching.cache.noabsoluteexpiration, caching.cache.noslidingexpiration);
}

get on Test the code.Changing the table products, categories, or suppliers will now clear the cached data.In addition, when editing a product in the gridview control, the updateproduct method of the productscl class will be called. This method clears the mastercachekeyarray cache dependency, which causes a chain reaction to clear the cached productsdatatable. The final result is,The data will be retrieved from the database the next time data is requested.

Note:You can also use SQL cache dependencies through output caching. For details,Please refer to "using asp.net output caching with sql server." ()

Conclusion:

Caching When caching database data,The ideal state is that the data always resides in memory,Until the database has changed.In asp.net 2.0, you can use SQL cache dependencies programmatically or by declaring code. The challenge of this method is to detect changes in the data in a timely manner.The full version of Microsoft SQL Server 2005 provides a notification function that notifies the application that the results returned by a data query have changed.For the express version of SQL Server 2005, and older versions,Only the polling detection system is used.But okay,Setting up the necessary constructs for polling is simple.

Happy programming!

About the Author

  • Previous Summary of essential knowledge for learning GO programming
  • Next Talk about JavaScript objects in detail