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"
|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
. 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
attributes of a SqlDataSource control to force the data to be cached for a finite periodten 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 periodsbut still show the correct valuesby 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
, 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">
connectionStringName="nwind" pollTime="500" />
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
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.