Take Advantage of ASP.NET 2.0's Data Caching Techniques, Part 2 : Page 2

Data Caching with a Data Source Control
ASP.NET 2.0 introduces a range of new data source controls that can, in many common scenarios, remove the requirement to write data access code. Most of these controls also implement caching, which plugs into the cache architecture automatically, and provides an easy way to boost the performance of your pages. As an example, the following code shows the attributes you can include when declaring a SqlDataSource control:

   <asp:SqlDataSource id="identifier" runat="server"
       ConnectionString=
          "<%$ConnectionStrings:myconnectionstring%>"
       SelectCommand="sql-statement-or-stored-proc-name"
       DataSourceMode="[DataSet|DataReader]"
       EnableCaching="[True|False]"
       CacheDuration="#seconds"
       CacheExpirationPolicy="[Absolute|Sliding]"
       SqlCacheDependency="dependency-name:table-name">
   </asp:SqlDataSource>

If the format of dates and times shown in these examples seems confusing, remember that the examples were created in England, so the date format is dd/mm/yyyy.

Simply by setting the EnableCaching attribute to True, and specifying the number of seconds to cache the data for the CacheDuration attribute, ASP.NET automatically caches the source data and reuses it when the page is refreshed during the specified duration period. However, note that this only works when the DataSourceMode is DataSet. You can also use a Sliding expiration policy (the default is Absolute), so that the cached data is invalidated only after there has been no request during the specified cache duration period.

Figure 3. Simple Finite Duration Caching in a SqlDataSource Control: The button updates the OrderDate column for the row with the OrderID 10248, causing the data in the cache to be out-of-sync with the data in the database.

To demonstrate the advantage of SQL cache invalidation, the sample code provides two examples that use a data source control. The first one, data-source-control.aspx, uses just the EnableCaching and CacheDuration attributes of a SqlDataSource control to force the data to be cached for a finite period—ten seconds in this example. The page also contains a button (see Figure 3) that causes a simple server-side routine to force an update within the source database of the OrderDate column for the first row that is shown in the page (OrderID = 10248) . Clicking the button updates the row, meaning that the data cached by the data source control is now out-of-date compared to the data in the source database row.

Notice that the SQL statement executed in Figure 3 updated the OrderDate of the first row to the current date and time, but the value displayed in the GridView control still shows the value when the rowset was originally fetched from the database. It's only when the cache duration period expires that the GridView data is refreshed and the new value appears (see Figure 4).


Configuring a Database for Caching in Web.config
You can use the SqlCacheDependency attribute in a data source control (and in an OutputCache directive) to link cached rowset data to a SQL Server change notification. This solves the issue shown in the previous example, where the code and controls in the page are not aware that the source data has changed. SQL cache invalidation, implemented through change notifications, means that the page and controls can safely cache the data for long periods—but still show the correct values—by re-executing the query only when the data changes.

Figure 4. Updates Visible Only After Cache Expires: The updated row (the first row (compare to Figure 3) becomes visible only after the cache duration period expires.

As you saw earlier, SQL cache dependencies (for SQL Server 7.0 and 2000) are configured in machine.config or Web.config, using the section. You can clear from the list any databases that might be defined higher up in the configuration file hierarchy using the element, remove individual databases with the element, and add new ones using the element. The following example adds a database cache entry named nwind-cache.

   <sqlCacheDependency enabled="true" pollTime="2000">
     <databases>
       <add name="nwind-cache" 
          connectionStringName="nwind" pollTime="500" />
     </databases>
   </sqlCacheDependency>

Notice that the default polling interval for all databases defined here is set to two seconds (2000 milliseconds), but specified as half a second for the "nwind-cache" entry (this is the minimum value you can use for the polling interval). Remember that you have to enable change notifications in the database for SQL Server 7.0 and 2000 using the aspnet_regsql.exe utility.

The connectionStringName attribute references the name of the connection string for this database, which you must declare in the <connectionStrings> section of this Web.config file, or in another configuration file higher up in the configuration hierarchy, as shown in the following example.

   <connectionStrings>
     <add name="nwind"
        connectionString="Data Source=localhost; 
        Initial Catalog=Northwind; 
        Integrated Security=True"/>
   </connectionStrings>

Previous Page

Next Page

---

• Download The Code for this Article

---