SQL Express Dependency Caching

SQL Express dependency caching is easier to set up because SQL Express natively supports change notifications. You do not need to run special command line utilities to prepare the database because notifications are automatically generated by SQL Express.

Moreover, the SqlCommand class inherently supports dependency caching. Simply instantiate the SqlCacheDependency class with a SqlCommand object and then use the Response object's AddCacheDependency method to add the SqlCacheDependency object to the page's response.

Data source objects also support SQL dependency caching. In this lesson you will set properties of the authorsObjectDataSource object to enable SQL dependency caching.

1. In the Design view for DependencyCaching.aspx, select authorsObjectDataSource and press F4.

2. Near the top is a section for cache properties. Set CacheDuration to "3600", EnableCaching to True and SqlCacheDependency to "pubs:authors".

Notice that these are essentially the same values as you set via the OutputCaching directive in the previous lesson.

In this lesson, however, you are not caching the entire page output, but only the data retrieved by the object data source. As such, you need to make a few other modifications to the page.

3. Click Source and remove the OutputCaching directive from the DependencyCaching.aspx.

4. Click Design and then double-click the ObjectDataSource control to add a handler for its Selecting event.

5. In the authorsObjectDataSource_Selecting event handler add the following code, which sets the Label control's Text property to the time and date of the last execution of the ObjectDataSource.Select method—in other words, the latest actual roundtrip to the database:

dateTimeLabel.Text = System.DateTime.Now.ToString()
Cache("LastRetrieval") = System.DateTime.Now.ToString()

6. In the Page_Load event handler, replace the existing code with the following:

If Not IsNothing(Cache("LastRetrieval")) Then
   dateTimeLabel.Text = Cache("LastRetrieval").ToString()
End If

7. Press F5 to run the application. Refresh the page a few times to see that the timestamp remains unchanged. For any row, click Edit, make a change, and then click Update. Note the timestamp has changed. After making an update do not continue to press F5 to refresh to page. Instead, use the navigation menu to return to the page. Otherwise you will have postback issues arising from just having edited the GridView data.

If at any time caching does not seem to work as expected, press ALT+Tab and stop the Web server for your application. Sometimes if you make changes with the development Web server still running, the results are unexpected.