Using SQL Cache Dependencies

September 29, 2007 — Mikkel Ovesen

Original post here: http://www.asp.net/learn/data-access/tutorial-61-cs.aspx

—

Introduction

The caching techniques examined in the Caching Data with the ObjectDataSource and Caching Data in the Architecture tutorials used a time-based expiry to evict the data from the cache after a specified period. This approach is the simplest way to balance the performance gains of caching against data staleness. By selecting a time expiry of x seconds, a page developer concedes to enjoy the performance benefits of caching for only x seconds, but can rest easy that her data will never be stale longer than a maximum of x seconds. Of course, for static data, x can be extended to the lifetime of the web application, as was examined in the Caching Data at Application Startup tutorial.

When caching database data, a time-based expiry is often chosen for its ease of use but is frequently an inadequate solution. Ideally, the database data would remain cached until the underlying data has been modified in the database; only then would the cache be evicted. This approach maximizes the performance benefits of caching and minimizes the duration of stale data. However, in order to enjoy these benefits there must be some system in place that knows when the underlying database data has been modified and evicts the corresponding items from the cache. Prior to ASP.NET 2.0, page developers were responsible for implementing this system.

ASP.NET 2.0 provides a SqlCacheDependency class and the necessary infrastructure to determine when a change has occurred in the database so that the corresponding cached items can be evicted. There are two techniques for determining when the underlying data has changed: notification and polling. After discussing the differences between notification and polling, we’ll create the infrastructure necessary to support polling and then explore how to use the SqlCacheDependency class in declarative and programmatically scenarios.

Understanding Notification and Polling

There are two techniques that can be used to determine when the data in a database has been modified: notification and polling. With notification, the database automatically alerts the ASP.NET runtime when the results of a particular query have been changed since the query was last executed, at which point the cached items associated with the query are evicted. With polling, the database server maintains information about when particular tables have last been updated. The ASP.NET runtime periodically polls the database to check what tables have changed since they were entered into the cache. Those tables whose data has been modified have their associated cache items evicted.

The notification option requires less setup than polling and is more granular since it tracks changes at the query level rather than at the table level. Unfortunately, notifications are only available in the full editions of Microsoft SQL Server 2005 (i.e., the non-Express editions). However, the polling option can be used for all versions of Microsoft SQL Server from 7.0 to 2005. Since these tutorials use the Express edition of SQL Server 2005, we will focus on setting up and using the polling option. Consult the Further Reading section at the end of this tutorial for further resources on SQL Server 2005’s notification capabilities.

With polling, the database must be configured to include a table named AspNet_SqlCacheTablesForChangeNotification that has three columns – tableName, notificationCreated, and changeId. This table contains a row for each table that has data that might need to be used in a SQL cache dependency in the web application. The tableName column specifies the name of the table while notificationCreated indicates the date and time the row was added to the table. The changeId column is of type int and has an initial value of 0. Its value is incremented with each modification to the table.

In addition to the AspNet_SqlCacheTablesForChangeNotification table, the database also needs to include triggers on each of the tables that may appear in a SQL cache dependency. These triggers are executed whenever a row is inserted, updated, or deleted and increment the table’s changeId value in AspNet_SqlCacheTablesForChangeNotification.

The ASP.NET runtime tracks the current changeId for a table when caching data using a SqlCacheDependency object. The database is periodically checked and any SqlCacheDependency objects whose changeId differs from the value in the database are evicted since a differing changeId value indicates that there has been a change to the table since the data was cached.

Step 1: Exploring the aspnet_regsql.exe Command Line Program

With the polling approach the database must be setup to contain the infrastructure described above: a predefined table (AspNet_SqlCacheTablesForChangeNotification), a handful of stored procedures, and triggers on each of the tables that may be used in SQL cache dependencies in the web application. These tables, stored procedures, and triggers can be created through the command line program aspnet_regsql.exe, which is found in the $WINDOWS$\Microsoft.NET\Framework\version folder. To create the AspNet_SqlCacheTablesForChangeNotification table and associated stored procedures, run the following 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 execute these commands the specified database login must be in the db_securityadmin and db_ddladmin roles. To examine the T-SQL sent to the database by the aspnet_regsql.exe command line program, refer to this blog entry.

For example, to add the infrastructure for polling to a Microsoft SQL Server database named pubs on a database server named ScottsServer using Windows Authentication, navigate to the appropriate directory and, from the command line, enter:

aspnet_regsql.exe -S ScottsServer -E -d pubs -ed

After the database-level infrastructure has been added, we need to add the triggers to those tables that will be used in SQL cache dependencies. Use the aspnet_regsql.exe command line program again, but specify the table name using the -t switch and instead of using the -ed switch use -et, like so:

/* 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 the triggers to the authors and titles tables on the pubs database on ScottsServer, use:

aspnet_regsql.exe -S ScottsServer -E -d pubs -t authors -et aspnet_regsql.exe -S ScottsServer -E -d pubs -t titles -et

For this tutorial add the triggers to the Products, Categories, and Suppliers tables. We’ll look at the particular command line syntax in Step 3.

Step 2: Referencing a Microsoft SQL Server 2005 Express Edition Database in App_Data

The aspnet_regsql.exe command line program requires the database and server name in order to add the necessary polling infrastructure. But what is the database and server name for a Microsoft SQL Server 2005 Express database that resides in the App_Data folder? Rather than having to discover what the database and server names are, I’ve found that the simplest approach is to attach the database to the localhost\SQLExpress database instance and rename the data using SQL Server Management Studio. If you have one of the full versions of SQL Server 2005 installed on your machine, then you likely already have SQL Server Management Studio installed on your computer. If you only have the Express edition, you can download the free Microsoft SQL Server Management Studio Express Edition.

Start by closing Visual Studio. Next, open SQL Server Management Studio and choose to connect to the localhost\SQLExpress server using Windows Authentication.

Attach to the localhost\SQLExpress Server

Figure 1: Attach to the localhost\SQLExpress Server

After connecting to the server, Management Studio will show the server and have subfolders for the databases, security, and so forth. Right-click on the Databases folder and choose the Attach option. This will bring up the Attach Databases dialog box (see Figure 2). Click the Add button and select the NORTHWND.MDF database folder in your web application’s App_Data folder.

Attach the NORTHWND.MDF Database from the App_Data Folder

Figure 2: Attach the NORTHWND.MDF Database from the App_Data Folder (Click to view full-size image)

This will add the database to the Databases folder. The database name might be the full path to the database file or the full path prepended with a GUID. To avoid having to type in this lengthy database name when using the aspnet_regsql.exe command line tool, rename the database to a more human-friendly name by right-clicking on database just attached and choosing Rename. I’ve renamed my database to “DataTutorials”.

Rename the Attached Database to a More Human-Friendly Name

Figure 3: Rename the Attached Database to a More Human-Friendly Name

Step 3: Adding the Polling Infrastructure to the Northwind Database

Now that we have attached the NORTHWND.MDF database from the App_Data folder, we’re ready to add the polling infrastructure. Assuming that you’ve renamed the database to “DataTutorials”, run the following four commands:

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 running these four commands, right-click on the database name in Management Studio, go to the Tasks submenu, and choose Detach. Then close Management Studio and reopen Visual Studio.

Once Visual Studio has reopened, drill into the database through the Server Explorer. Note the new table (AspNet_SqlCacheTablesForChangeNotification), the new stored procedures, and the triggers on the Products, Categories, and Suppliers tables.

The Database Now Includes the Necessary Polling Infrastructure

Figure 4: The Database Now Includes the Necessary Polling Infrastructure

Step 4: Configuring the Polling Service

After creating the needed tables, triggers, and stored procedures in the database, the final step is to configure the polling service, which is done through Web.config by specifying the databases to use and the polling frequency in milliseconds. The following markup polls the Northwind database once every 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>

The name value in the <add> element (“NorthwindDB”) associates a human-readable name with a particular database. When working with SQL cache dependencies, we’ll need to refer to the database name defined here as well as the table that the cached data is based on. We’ll see how to use the SqlCacheDependency class to programmatically associate SQL cache dependencies with cached data in Step 6.

Once a SQL cache dependency has been established, the polling system will connect to the databases defined in the <databases> elements every pollTime milliseconds and execute the AspNet_SqlCachePollingStoredProcedure stored procedure. This stored procedure – which was added back in Step 3 using the aspnet_regsql.exe command line tool – returns the tableName and changeId values for each record in AspNet_SqlCacheTablesForChangeNotification. Outdated SQL cache dependencies are evicted from the cache.

The pollTime setting introduces a tradeoff between performance and data staleness. A small pollTime value increases the number of requests to the database, but more quickly evicts stale data from the cache. A larger pollTime value reduces the number of database requests, but increases the delay between when the backend data changes and when the related cache items are evicted. Fortunately, the database request is executing a simple stored procedure that’s returning just a few rows from a simple, lightweight table. But do experiment with different pollTime values to find an ideal balance between database access and data staleness for your application. The smallest pollTime value allowed is 500.

Note: The above example provides a single pollTime value in the <sqlCacheDependency> element, but you can optionally specify the pollTime value in the <add> element. This is useful if you have multiple databases specified and want to customize the polling frequency per database.

Step 5: Declaratively Working with SQL Cache Dependencies

In Steps 1 through 4 we looked at how to setup the necessary database infrastructure and configure the polling system. With this infrastructure in place, we can now add items to the data cache with an associated SQL cache dependency using either programmatic or declarative techniques. In this step we’ll examine how to declaratively work with SQL cache dependencies. In Step 6 we’ll look at the programmatic approach.

The Caching Data with the ObjectDataSource tutorial explored the declarative caching capabilities of the ObjectDataSource. By simply setting the EnableCaching property to true and the CacheDuration property to some time interval, the ObjectDataSource will automatically cache the data returned from its underlying object for the specified interval. The ObjectDataSource can also use one or more SQL cache dependencies.

To demonstrate using SQL cache dependencies declaratively, open the SqlCacheDependencies.aspx page in the Caching folder and drag a GridView from the Toolbox onto the Designer. Set the GridView’s ID to ProductsDeclarative and, from its smart tag, choose to bind it to a new ObjectDataSource named ProductsDataSourceDeclarative.

Create a New ObjectDataSource Named ProductsDataSourceDeclarative

Figure 5: Create a New ObjectDataSource Named ProductsDataSourceDeclarative (Click to view full-size image)

Configure the ObjectDataSource to use the ProductsBLL class and set the drop-down list in the SELECT tab to GetProducts(). In the UPDATE tab, choose the UpdateProduct overload with three input parameters – productName, unitPrice, and productID. Set the drop-down lists to “(None)” in the INSERT and DELETE tabs.

Use the UpdateProduct Overload with Three Input Parameters

Figure 6: Use the UpdateProduct Overload with Three Input Parameters (Click to view full-size image)

Set the Drop-Down List to “(None)” for the INSERT and DELETE Tabs

Figure 7: Set the Drop-Down List to “(None)” for the INSERT and DELETE Tabs (Click to view full-size image)

After completing the Configure Data Source wizard, Visual Studio will create BoundFields and CheckBoxFields in the GridView for each of the data fields. Remove all fields but ProductName, CategoryName, and UnitPrice, and format these fields as you see fit. From the GridView’s smart tag, check the Enable Paging, Enable Sorting, and Enable Editing checkboxes. Visual Studio will set the ObjectDataSource’s OldValuesParameterFormatString property to original_{0}. In order for the GridView’s edit feature to work properly, either remove this property entirely from the declarative syntax or set it back to its default value, {0}.

Finally, add a Label Web control above the GridView and set its ID property to ODSEvents and its EnableViewState property to false. After making these changes, your page’s declarative markup should look similar to the following. Note that I’ve made a number of aesthetic customizations to the GridView fields that are not necessary to demonstrate the SQL cache dependency functionality.

<asp:Label ID="ODSEvents" runat="server" EnableViewState="False" /> <asp:GridView ID="ProductsDeclarative" 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 ID="ProductName" runat="server" Text='<%# Bind("ProductName") %>' /> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="ProductName" Display="Dynamic" ErrorMessage="You must provide a name for the product." SetFocusOnError="True" runat="server">*</asp:RequiredFieldValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" 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 ID="UnitPrice" runat="server" Columns="8" Text='<%# Bind("UnitPrice", "{0:N2}") %>'></asp:TextBox> <asp:CompareValidator ID="CompareValidator1" 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">*</asp:CompareValidator> </EditItemTemplate> <ItemStyle HorizontalAlign="Right" /> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("UnitPrice", "{0:c}") %>' /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:ObjectDataSource ID="ProductsDataSourceDeclarative" 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 ObjectDataSource’s Selecting event and in it add the following code:

protected void ProductsDataSourceDeclarative_Selecting (object sender, ObjectDataSourceSelectingEventArgs e) { ODSEvents.Text = "-- Selecting event fired"; }

Recall that the ObjectDataSource’s Selecting event fires only when retrieving data from its underlying object. If the ObjectDataSource accesses the data from its own cache, this event is not fired.

Now, visit this page through a browser. Since we’ve yet to implement any caching, each time you page, sort, or edit the grid the page should display the text, “�Selecting event fired”, as Figure 8 shows.

The ObjectDataSource’s Selecting Event Fires Each Time the GridView is Paged, Edited, or Sorted

Figure 8: The ObjectDataSource’s Selecting Event Fires Each Time the GridView is Paged, Edited, or Sorted (Click to view full-size image)

As we saw in the Caching Data with the ObjectDataSource tutorial, setting the EnableCaching property to true causes the ObjectDataSource to cache its data for the duration specified by its CacheDuration property. The ObjectDataSource also has a SqlCacheDependency property, which adds one or more SQL cache dependencies to the cached data using the pattern:

databaseName1:tableName1;databaseName2:tableName2;...

Where databaseName is the name of the database as specified in the name attribute of the <add> element in Web.config, and tableName is the name of the database table. For example, to create an ObjectDataSource that caches data indefinitely based on a SQL cache dependency against the Northwind’s Products table, set the ObjectDataSource’s EnableCaching property to true and its SqlCacheDependency property to “NorthwindDB:Products”.

Note: You can use a SQL cache dependency and a time-based expiry by setting EnableCaching to true, CacheDuration to the time interval, and SqlCacheDependency to the database and table name(s). The ObjectDataSource will evict its data when the time-based expiry is reached or when the polling system notes that the underlying database data has changed, whichever happens first.

The GridView in SqlCacheDependencies.aspx displays data from two tables – Products and Categories (the product’s CategoryName field is retrieved via a JOIN on Categories). Therefore, we want to specify two SQL cache dependencies: “NorthwindDB:Products;NorthwindDB:Categories”.

Configure the ObjectDataSource to Support Caching Using SQL Cache Dependencies on Products and Categories

Figure 9: Configure the ObjectDataSource to Support Caching Using SQL Cache Dependencies on Products and Categories (Click to view full-size image)

After configuring the ObjectDataSource to support caching, revisit the page through a browser. Again, the text “�Selecting event fired” should appear on the first page visit, but should go away when paging, sorting, or clicking the Edit or Cancel buttons. This is because after the data is loaded into the ObjectDataSource’s cache, it remains there until the Products or Categories tables are modified or the data is updated through the GridView.

After paging through the grid and noting the lack of the “�Selecting event fired” text, open a new browser window and navigate to the Basics tutorial in the Editing, Inserting, and Deleting section (~/EditInsertDelete/Basics.aspx). Update the name or price of a product. Then, from to the first browser window, view a different page of data, sort the grid, or click a row’s Edit button. This time, the “�Selecting event fired” should reappear, as the underlying database data has been modified (see Figure 10). If the text does not appear, wait a few moments and try again. Remember that the polling service is checking for changes to the Products table every pollTime milliseconds, so there is a delay between when the underlying data is updated and when the cached data is evicted.

Modifying the Products Table Evicts the Cached Product Data

Figure 10: Modifying the Products Table Evicts the Cached Product Data (Click to view full-size image)

Step 6: Programmatically Working with the SqlCacheDependency Class

The Caching Data in the Architecture tutorial looked at the benefits of using a separate Caching Layer in the architecture as opposed to tightly coupling the caching with the ObjectDataSource. In that tutorial we created a ProductsCL class to demonstrate programmatically working with the data cache. To utilize SQL cache dependencies in the Caching Layer, use the SqlCacheDependency class.

With the polling system, a SqlCacheDependency object must be associated with a particular database and table pair. The following code, for example, creates a SqlCacheDependency object based on the Northwind database’s Products table:

Caching.SqlCacheDependency productsTableDependency = new Caching.SqlCacheDependency("NorthwindDB", "Products");

The two input parameters to the SqlCacheDependency’s constructor are the database and table names, respectively. Like with the ObjectDataSource’s SqlCacheDependency property, the database name used is the same as the value specified in the name attribute of the <add> element in Web.config. The table name is the actual name of the database table.

To associate a SqlCacheDependency with an item added to the data cache, use one of the Insert method overloads that accepts a dependency. The following code adds value to the data cache for an indefinite duration, but associates it with a SqlCacheDependency on the Products table. In short, value will remain in the cache until it is evicted due to memory constraints or because the polling system has detected that the Products table has changed since it was cached.

Caching.SqlCacheDependency productsTableDependency = new Caching.SqlCacheDependency("NorthwindDB", "Products"); Cache.Insert(key, value, productsTableDependency, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration);

The Caching Layer’s ProductsCL class currently caches data from the Products table using a time-based expiry of 60 seconds. Let’s update this class so that it uses SQL cache dependencies instead. The ProductsCL class’s AddCacheItem method, which is responsible for adding the data to the cache, currently contains the following code:

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); }

Update this code to use a SqlCacheDependency object instead of the MasterCacheKeyArray cache dependency:

private void AddCacheItem(string rawKey, object value) { System.Web.Caching.Cache DataCache = HttpRuntime.Cache; // Add the SqlCacheDependency objects for Products Caching.SqlCacheDependency productsTableDependency = new Caching.SqlCacheDependency("NorthwindDB", "Products"); // Add the item to the data cache using productsTableDependency DataCache.Insert(GetCacheKey(rawKey), value, productsTableDependency, Caching.Cache.NoAbsoluteExpiration, Caching.Cache.NoSlidingExpiration); }

To test this functionality, add a GridView to the page beneath the existing ProductsDeclarative GridView. Set this new GridView’s ID to ProductsProgrammatic and, through its smart tag, bind it to a new ObjectDataSource named ProductsDataSourceProgrammatic. Configure the ObjectDataSource to use the ProductsCL class, setting the drop-down lists in the SELECT and UPDATE tabs to GetProducts and UpdateProduct, respectively.

Configure the ObjectDataSource to Use the ProductsCL Class

Figure 11: Configure the ObjectDataSource to Use the ProductsCL Class (Click to view full-size image)

Select the GetProducts Method from the SELECT Tab’s Drop-Down List

Figure 12: Select the GetProducts Method from the SELECT Tab’s Drop-Down List (Click to view full-size image)

Choose the UpdateProduct Method from the UPDATE Tab’s Drop-Down List

Figure 13: Choose the UpdateProduct Method from the UPDATE Tab’s Drop-Down List (Click to view full-size image)

After completing the Configure Data Source wizard, Visual Studio will create BoundFields and CheckBoxFields in the GridView for each of the data fields. Like with the first GridView added to this page, remove all fields but ProductName, CategoryName, and UnitPrice, and format these fields as you see fit. From the GridView’s smart tag, check the Enable Paging, Enable Sorting, and Enable Editing checkboxes. As with the ProductsDataSourceDeclarative ObjectDataSource, Visual Studio will set the ProductsDataSourceProgrammatic ObjectDataSource’s OldValuesParameterFormatString property to original_{0}. In order for the GridView’s edit feature to work properly, set this property back to {0} (or remove the property assignment from the declarative syntax altogether).

After completing these tasks, the resulting GridView and ObjectDataSource declarative markup should look like the following:

<asp:GridView ID="ProductsProgrammatic" 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 ID="ProductName" runat="server" Text='<%# Bind("ProductName") %>' /> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="ProductName" Display="Dynamic" ErrorMessage="You must provide a name for the product." SetFocusOnError="True" runat="server">*</asp:RequiredFieldValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" 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 ID="UnitPrice" runat="server" Columns="8" Text='<%# Bind("UnitPrice", "{0:N2}") %>'></asp:TextBox> <asp:CompareValidator ID="CompareValidator1" 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">*</asp:CompareValidator> </EditItemTemplate> <ItemStyle HorizontalAlign="Right" /> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("UnitPrice", "{0:c}") %>' /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:ObjectDataSource ID="ProductsDataSourceProgrammatic" 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 Caching Layer set a breakpoint in the ProductCL class’s AddCacheItem method and then start debugging. When you first visit SqlCacheDependencies.aspx, the breakpoint should be hit as the data is requested for the first time and placed into the cache. Next, move to another page in the GridView or sort one of the columns. This causes the GridView to requery its data, but the data should be found in the cache since the Products database table has not been modified. If the data is repeatedly not found in the cache, make sure there is sufficient memory available on your computer and try again.

After paging through a few pages of the GridView, open a second browser window and navigate to the Basics tutorial in the Editing, Inserting, and Deleting section (~/EditInsertDelete/Basics.aspx). Update a record from the Products table and then, from the first browser window, view a new page or click on one of the sorting headers.

In this scenario you will see one of two things: either the breakpoint will be hit, indicating that the cached data was evicted due to the change in the database; or, the breakpoint will not be hit, meaning that SqlCacheDependencies.aspx is now showing stale data. If the breakpoint is not hit, it is likely because the polling service has not yet fired since the data was changed. Remember that the polling service is checking for changes to the Products table every pollTime milliseconds, so there is a delay between when the underlying data is updated and when the cached data is evicted.

Note: This delay is more likely to appear when editing one of the products through the GridView in SqlCacheDependencies.aspx. In the Caching Data in the Architecture tutorial we added the MasterCacheKeyArray cache dependency to ensure that the data being edited through the ProductsCL class’s UpdateProduct method was evicted from the cache. However, we replaced this cache dependency when modifying the AddCacheItem method earlier in this step and therefore the ProductsCL class will continue to show the cached data until the polling system notes the change to the Products table. We’ll see how to reintroduce the MasterCacheKeyArray cache dependency in Step 7.

Step 7: Associating Multiple Dependencies with a Cached Item

Recall that the MasterCacheKeyArray cache dependency is used to ensure that all product-related data is evicted from the cache when any single item associated within it is updated. For example, the GetProductsByCategoryID(categoryID) method caches ProductsDataTables instances for each unique categoryID value. If one of these objects is evicted, the MasterCacheKeyArray cache dependency ensures that the others are also removed. Without this cache dependency, when the cached data is modified the possibility exists that other cached product data may be out of date. Consequently, it’s important that we maintain the MasterCacheKeyArray cache dependency when using SQL cache dependencies. However, the data cache’s Insert method only allows for a single dependency object.

Furthermore, when working with SQL cache dependencies we may need to associate multiple database tables as dependencies. For example, the ProductsDataTable cached in the ProductsCL class contains the category and supplier names for each product, but the AddCacheItem method only uses a dependency on Products. In this situation, if the user updates the name of a category or supplier, the cached product data will remain in the cache and be out of date. Therefore, we want to make the cached product data dependent on not only the Products table, but on the Categories and Suppliers tables as well.

The AggregateCacheDependency class provides a means for associating multiple dependencies with a cache item. Start by creating an AggregateCacheDependency instance. Next, add the set of dependencies using the AggregateCacheDependency’s Add method. When inserting the item into the data cache thereafter, pass in the AggregateCacheDependency instance. When any of the AggregateCacheDependency instance’s dependencies change, the cached item will be evicted.

The following shows the updated code for the ProductsCL class’s AddCacheItem method. The method creates the MasterCacheKeyArray cache dependency along with SqlCacheDependency objects for the Products, Categories, and Suppliers tables. These are all combined into one AggregateCacheDependency object named aggregateDependencies, which is then passed into 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); }

Test this new code out. Now changes to the Products, Categories, or Suppliers tables cause the cached data to be evicted. Moreover, the ProductsCL class’s UpdateProduct method, which is called when editing a product through the GridView, evicts the MasterCacheKeyArray cache dependency, which causes the cached ProductsDataTable to be evicted and the data to be re-retrieved on the next request.

Note: SQL cache dependencies can also be used with output caching. For a demonstration of this functionality, see: Using ASP.NET Output Caching with SQL Server.

Summary

When caching database data, the data will ideally remain in the cache until it is modified in the database. With ASP.NET 2.0, SQL cache dependencies can be created and used in both declarative and programmatic scenarios. One of the challenges with this approach is in discovering when the data has been modified. The full versions of Microsoft SQL Server 2005 provide notification capabilities that can alert an application when a query result has changed. For the Express Edition of SQL Server 2005 and older versions of SQL Server, a polling system must be used instead. Fortunately, setting up the necessary polling infrastructure is fairly straightforward.

Happy Programming!

Further Reading

For more information on the topics discussed in this tutorial, refer to the following resources:

About the Author

Scott Mitchell, author of seven ASP/ASP.NET books and founder of 4GuysFromRolla.com, has been working with Microsoft Web technologies since 1998. Scott works as an independent consultant, trainer, and writer. His latest book is Sams Teach Yourself ASP.NET 2.0 in 24 Hours. He can be reached at mitchell@4GuysFromRolla.com. or via his blog, which can be found at http://ScottOnWriting.NET.

Special Thanks To…

This tutorial series was reviewed by many helpful reviewers. Lead reviewers for this tutorial were Marko Rangel, Teresa Murphy, and Hilton Giesenow. Interested in reviewing my upcoming MSDN articles? If so, drop me a line at mitchell@4GuysFromRolla.com.

Posted in Development. Tags: , , , , . No Comments »

Caching Data in the Architecture

September 29, 2007 — Mikkel Ovesen

Original post here: http://www.asp.net/learn/data-access/tutorial-59-cs.aspx

—

Introduction

As we saw in the preceding tutorial, caching the ObjectDataSource’s data is as simple as setting a couple of properties. Unfortunately, the ObjectDataSource applies caching at the Presentation Layer, which tightly couples the caching policies with the ASP.NET page. One of the reasons for creating a layered architecture is to allow such couplings to be broken. The Business Logic Layer, for instance, decouples the business logic from the ASP.NET pages, while the Data Access Layer decouples the data access details. This decoupling of business logic and data access details is preferred, in part, because it makes the system more readable, more maintainable, and more flexible to change. It also allows for domain knowledge and division of labor – a developer working on the Presentation Layer doesn’t need to be familiar with the database’s details in order to do her job. Decoupling the caching policy from the Presentation Layer offers similar benefits.

In this tutorial we will augment our architecture to include a Caching Layer (or CL for short) that employs our caching policy. The Caching Layer will include a ProductsCL class that provides access to product information with methods like GetProducts(), GetProductsByCategoryID(categoryID), and so forth, that, when invoked, will first attempt to retrieve the data from the cache. If the cache is empty, these methods will invoke the appropriate ProductsBLL method in the BLL, which would in turn get the data from the DAL. The ProductsCL methods cache the data retrieved from the BLL before returning it.

As Figure 1 shows, the CL resides between the Presentation and Business Logic Layers.

The Caching Layer (CL) is Another Layer in Our Architecture

Figure 1: The Caching Layer (CL) is Another Layer in Our Architecture

Step 1: Creating the Caching Layer Classes

In this tutorial we will create a very simple CL with a single class – ProductsCL – that has only a handful of methods. Building a complete Caching Layer for the entire application would require creating CategoriesCL, EmployeesCL, and SuppliersCL classes, and providing a method in these Caching Layer classes for each data access or modification method in the BLL. As with the BLL and DAL, the Caching Layer should ideally be implemented as a separate Class Library project; however, we will implement it as a class in the App_Code folder.

To more cleanly separate the CL classes from the DAL and BLL classes, let’s create a new subfolder in the App_Code folder. Right-click on the App_Code folder in the Solution Explorer, choose New Folder, and name the new folder CL. After creating this folder, add to it a new class named ProductsCL.cs.

Add a New Folder Named CL and a Class Named ProductsCL.cs

Figure 2: Add a New Folder Named CL and a Class Named ProductsCL.cs

The ProductsCL class should include the same set of data access and modification methods as found in its corresponding Business Logic Layer class (ProductsBLL). Rather than creating all of these methods, let’s just build a couple here to get a feel for the patterns used by the CL. In particular, we’ll add the GetProducts() and GetProductsByCategoryID(categoryID) methods in Step 3 and an UpdateProduct overload in Step 4. You can add the remaining ProductsCL methods and CategoriesCL, EmployeesCL, and SuppliersCL classes at your leisure.

Step 2: Reading and Writing to the Data Cache

The ObjectDataSource caching feature explored in the preceding tutorial internally uses the ASP.NET data cache to store the data retrieved from the BLL. The data cache can also be accessed programmatically from ASP.NET pages’ code-behind classes or from the classes in the web application’s architecture. To read and write to the data cache from an ASP.NET page’s code-behind class, use the following pattern:

// Read from the cache object value = Cache["key"]; // Add a new item to the cache Cache["key"] = value; Cache.Insert(key, value); Cache.Insert(key, value, CacheDependency); Cache.Insert(key, value, CacheDependency, DateTime, TimeSpan);

The Cache class’s Insert method has a number of overloads. Cache["key"] = value and Cache.Insert(key, value) are synonymous and both add an item to the cache using the specified key without a defined expiry. Typically, we want to specify an expiry when adding an item to the cache, either as a dependency, a time-based expiry, or both. Use one of the other Insert method’s overloads to provide dependency- or time-based expiry information.

The Caching Layer’s methods need to first check if the requested data is in the cache and, if so, return it from there. If the requested data is not in the cache, the appropriate BLL method needs to be invoked. Its return value should be cached and then returned, as the following sequence diagram illustrates.

The Caching Layer’s Methods Return Data from the Cache if it’s Available

Figure 3: The Caching Layer’s Methods Return Data from the Cache if it’s Available

The sequence depicted in Figure 3 is accomplished in the CL classes using the following pattern:

Type instance = Cache["key"] as Type; if (instance == null) { instance = BllMethodToGetInstance(); Cache.Insert(key, instance, ...); } return instance;

Here, Type is the type of data being stored in the cache – Northwind.ProductsDataTable, for example – while key is the key that uniquely identifies the cache item. If the item with the specified key is not in the cache, then instance will be null and the data will be retrieved from the appropriate BLL method and added to the cache. By the time return instance is reached, instance contains a reference to the data, either from the cache or pulled from the BLL.

Be sure to use the above pattern when accessing data from the cache. The following pattern, which, at first glance, looks equivalent, contains a subtle difference that introduces a race condition. Race conditions are difficult to debug because they reveal themselves sporadically and are difficult to reproduce.

if (Cache["key"] == null) { Cache.Insert(key, BllMethodToGetInstance(), ...); } return Cache["key"];

The difference in this second, incorrect code snippet is that rather than storing a reference to the cached item in a local variable, the data cache is accessed directly in the conditional statement and in the return. Imagine that when this code is reached, Cache["key"] is non-null, but before the return statement is reached, the system evicts key from the cache. In this rare case, the code will return a null value rather than an object of the expected type. For an anecdote on how using the incorrect caching pattern can lead to sporadic unexpected behavior, see this blog entry by Scott Cate.

Note: The data cache is thread-safe, so you don’t need to synchronize thread access for simple reads or writes. However, if you need to perform multiple operations on data in the cache that need to be atomic, you are responsible for implementing a lock or some other mechanism to ensure thread safety. See Synchronizing Access to the ASP.NET Cache for more information.

An item can be programmatically evicted from the data cache using the Remove method like so:

Cache.Remove(key);

Step 3: Returning Product Information from the ProductsCL Class

For this tutorial let’s implement two methods for returning product information from the ProductsCL class: GetProducts() and GetProductsByCategoryID(categoryID). Like with the ProductsBL class in the Business Logic Layer, the GetProducts() method in the CL returns information about all of the products as a Northwind.ProductsDataTable object, while GetProductsByCategoryID(categoryID) returns all of the products from a specified category.

The following code shows a portion of the methods in the ProductsCL class:

[System.ComponentModel.DataObject] public class ProductsCL { private ProductsBLL _productsAPI = null; protected ProductsBLL API { get { if (_productsAPI == null) _productsAPI = new ProductsBLL(); return _productsAPI; } } [System.ComponentModel.DataObjectMethodAttribute(DataObjectMethodType.Select, true)] public Northwind.ProductsDataTable GetProducts() { const string rawKey = "Products"; // See if the item is in the cache Northwind.ProductsDataTable products = _ GetCacheItem(rawKey) as Northwind.ProductsDataTable; if (products == null) { // Item not found in cache - retrieve it and insert it into the cache products = API.GetProducts(); AddCacheItem(rawKey, products); } return products; } [System.ComponentModel.DataObjectMethodAttribute(DataObjectMethodType.Select, false)] public Northwind.ProductsDataTable GetProductsByCategoryID(int categoryID) { if (categoryID < 0) return GetProducts(); else { string rawKey = string.Concat("ProductsByCategory-", categoryID); // See if the item is in the cache Northwind.ProductsDataTable products = _ GetCacheItem(rawKey) as Northwind.ProductsDataTable; if (products == null) { // Item not found in cache - retrieve it and insert it into the cache products = API.GetProductsByCategoryID(categoryID); AddCacheItem(rawKey, products); } return products; } } }

First, note the DataObject and DataObjectMethodAttribute attributes applied to the class and methods. These attributes provide information to the ObjectDataSource’s wizard, indicating what classes and methods should appear in the wizard’s steps. Since the CL classes and methods will be accessed from an ObjectDataSource in the Presentation Layer, I added these attributes to enhance the design-time experience. Refer back to the Creating a Business Logic Layer tutorial for a more thorough description on these attributes and their effects.

In the GetProducts() and GetProductsByCategoryID(categoryID) methods, the data returned from the GetCacheItem(key) method is assigned to a local variable. The GetCacheItem(key) method, which we’ll examine shortly, returns a particular item from the cache based on the specified key. If no such data is found in cache, it is retrieved from the corresponding ProductsBLL class method and then added to the cache using the AddCacheItem(key, value) method.

The GetCacheItem(key) and AddCacheItem(key, value) methods interface with the data cache, reading and writing values, respectively. The GetCacheItem(key) method is the simpler of the two. It simply returns the value from the Cache class using the passed-in key:

private object GetCacheItem(string rawKey) { return HttpRuntime.Cache[GetCacheKey(rawKey)]; } private readonly string[] MasterCacheKeyArray = {"ProductsCache"}; private string GetCacheKey(string cacheKey) { return string.Concat(MasterCacheKeyArray[0], "-", cacheKey); }

GetCacheItem(key) does not use key value as supplied, but instead calls the GetCacheKey(key) method, which returns the key prepended with “ProductsCache-”. The MasterCacheKeyArray, which holds the string “ProductsCache”, is also used by the AddCacheItem(key, value) method, as we’ll see momentarily.

From an ASP.NET page’s code-behind class, the data cache can be accessed using the Page class’s Cache property, and allows for syntax like Cache["key"] = value, as discussed in Step 2. From a class within the architecture, the data cache can be accessed using either HttpRuntime.Cache or HttpContext.Current.Cache. Peter Johnson’s blog entry HttpRuntime.Cache vs. HttpContext.Current.Cache notes the slight performance advantage in using HttpRuntime instead of HttpContext.Current; consequently, ProductsCL uses HttpRuntime.

Note: If your architecture is implemented using Class Library projects then you will need to add a reference to the System.Web assembly in order to use the HttpRuntime and HttpContext classes.

If the item is not found in the cache, the ProductsCL class’s methods get the data from the BLL and add it to the cache using the AddCacheItem(key, value) method. To add value to the cache we could use the following code, which uses a 60 second time expiry:

const double CacheDuration = 60.0; private void AddCacheItem(string rawKey, object value) { HttpRuntime.Cache.Insert(GetCacheKey(rawKey), value, null, DateTime.Now.AddSeconds(CacheDuration), Caching.Cache.NoSlidingExpiration); }

DateTime.Now.AddSeconds(CacheDuration) specifies the time-based expiry – 60 seconds in the future – while System.Web.Caching.Cache.NoSlidingExpiration indicates that there’s no sliding expiration. While this Insert method overload has input parameters for both an absolute and sliding expiry, you can only provide one of the two. If you attempt to specify both an absolute time and a time span, the Insert method will throw an ArgumentException exception.

Note: This implementation of the AddCacheItem(key, value) method currently has some shortcomings. We’ll address and overcome these issues in Step 4.

Step 4: Invalidating the Cache When the Data is Modified Through the Architecture

Along with data retrieval methods, the Caching Layer needs to provide the same methods as the BLL for inserting, updating, and deleting data. The CL’s data modification methods do not modify the cached data, but rather call the BLL’s corresponding data modification method and then invalidate the cache. As we saw in the preceding tutorial, this is the same behavior that the ObjectDataSource applies when its caching features are enabled and its Insert, Update, or Delete methods are invoked.

The following UpdateProduct overload illustrates how to implement the data modification methods in the CL:

[System.ComponentModel.DataObjectMethodAttribute(DataObjectMethodType.Update, false)] public bool UpdateProduct(string productName, decimal? unitPrice, int productID) { bool result = API.UpdateProduct(productName, unitPrice, productID); // TODO: Invalidate the cache return result; }

The appropriate data modification Business Logic Layer method is invoked, but before its response is returned we need to invalidate the cache. Unfortunately, invalidating the cache is not straightforward because the ProductsCL class’s GetProducts() and GetProductsByCategoryID(categoryID) methods each add items to the cache with different keys, and the GetProductsByCategoryID(categoryID) method adds a different cache item for each unique categoryID.

When invalidating the cache, we need to remove all of the items that may have been added by the ProductsCL class. This can be accomplished by associating a cache dependency with the each item added to the cache in the AddCacheItem(key, value) method. In general, a cache dependency can be another item in the cache, a file on the file system, or data from a Microsoft SQL Server database. When the dependency changes or is removed from the cache, the cache items it is associated with are automatically evicted from the cache. For this tutorial, we want to create an additional item in the cache that serves as a cache dependency for all items added through the ProductsCL class. That way, all of these items can be removed from the cache by simply removing the cache dependency.

Let’s update the AddCacheItem(key, value) method so that each item added to the cache through this method is associated with a single cache dependency:

private void AddCacheItem(string rawKey, object value) { System.Web.Caching.Cache DataCache = HttpRuntime.Cache; // Make sure MasterCacheKeyArray[0] is in the cache - if not, add it if (DataCache[MasterCacheKeyArray[0]] == null) DataCache[MasterCacheKeyArray[0]] = DateTime.Now; // Add a CacheDependency System.Web.Caching.CacheDependency dependency = new CacheDependency(null, MasterCacheKeyArray); DataCache.Insert(GetCacheKey(rawKey), value, dependency, DateTime.Now.AddSeconds(CacheDuration), System.Web.Caching.Cache.NoSlidingExpiration); }

MasterCacheKeyArray is a string array that holds a single value, “ProductsCache”. First, a cache item is added to the cache and assigned the current date and time. If the cache item already exists, it is updated. Next, a cache dependency is created. The CacheDependency class’s constructor has a number of overloads, but the one being used in here expects two string array inputs. The first one specifies the set of files to be used as dependencies. Since we don’t want to use any file-based dependencies, a value of null is used for the first input parameter. The second input parameter specifies the set of cache keys to use as dependencies. Here we specify our single dependency, MasterCacheKeyArray. The CacheDependency is then passed into the Insert method.

With this modification to AddCacheItem(key, value), invaliding the cache is as simple as removing the dependency.

[System.ComponentModel.DataObjectMethodAttribute(DataObjectMethodType.Update, false)] public bool UpdateProduct(string productName, decimal? unitPrice, int productID) { bool result = API.UpdateProduct(productName, unitPrice, productID); // Invalidate the cache InvalidateCache(); return result; } public void InvalidateCache() { // Remove the cache dependency HttpRuntime.Cache.Remove(MasterCacheKeyArray[0]); }

Step 5: Calling the Caching Layer from the Presentation Layer

The Caching Layer’s classes and methods can be used to work with data using the techniques we’ve examined throughout these tutorials. To illustrate working with cached data, save your changes to the ProductsCL class and then open the FromTheArchitecture.aspx page in the Caching folder and add a GridView. From the GridView’s smart tag, create a new ObjectDataSource. In the wizard’s first step you should see the ProductsCL class as one of the options from the drop-down list.

The ProductsCL Class is Included in the Business Object Drop-Down List

Figure 4: The ProductsCL Class is Included in the Business Object Drop-Down List (Click to view full-size image)

After selecting ProductsCL, click Next. The drop-down list in the SELECT tab has two items – GetProducts() and GetProductsByCategoryID(categoryID) – and the UPDATE tab has the sole UpdateProduct overload. Choose the GetProducts() method from the SELECT tab and the UpdateProducts method from the UPDATE tab and click Finish.

The ProductsCL Class’s Methods are Listed in the Drop-Down Lists

Figure 5: The ProductsCL Class’s Methods are Listed in the Drop-Down Lists (Click to view full-size image)

After completing the wizard, Visual Studio will set the ObjectDataSource’s OldValuesParameterFormatString property to original_{0} and add the appropriate fields to the GridView. Change the OldValuesParameterFormatString property back to its default value, {0}, and configure the GridView to support paging, sorting, and editing. Since the UploadProducts overload used by the CL accepts only the edited product’s name and price, limit the GridView so that only these fields are editable.

In the preceding tutorial we defined a GridView to include fields for the ProductName, CategoryName, and UnitPrice fields. Feel free to replicate this formatting and structure, in which case your GridView and ObjectDataSource’s declarative markup should look similar to the following:

<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductID" DataSourceID="ProductsDataSource" AllowPaging="True" AllowSorting="True"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:TemplateField HeaderText="Product" SortExpression="ProductName"> <EditItemTemplate> <asp:TextBox ID="ProductName" runat="server" Text='<%# Bind("ProductName") %>' /> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="ProductName" Display="Dynamic" ErrorMessage="You must provide a name for the product." SetFocusOnError="True" runat="server">*</asp:RequiredFieldValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("ProductName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:BoundField DataField="CategoryName" HeaderText="Category" ReadOnly="True" SortExpression="CategoryName" /> <asp:TemplateField HeaderText="Price" SortExpression="UnitPrice"> <EditItemTemplate> $<asp:TextBox ID="UnitPrice" runat="server" Columns="8" Text='<%# Bind("UnitPrice", "{0:N2}") %>'></asp:TextBox> <asp:CompareValidator ID="CompareValidator1" 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">*</asp:CompareValidator> </EditItemTemplate> <ItemStyle HorizontalAlign="Right" /> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("UnitPrice", "{0:c}") %>' /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:ObjectDataSource ID="ProductsDataSource" 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>

At this point we have a page that uses the Caching Layer. To see the cache in action, set breakpoints in the ProductsCL class’s GetProducts() and UpdateProduct methods. Visit the page in a browser and step through the code when sorting and paging in order to see the data pulled from the cache. Then update a record and note that the cache is invalidated and, consequently, it is retrieved from the BLL when the data is rebound to the GridView.

Note: The Caching Layer provided in the download accompanying this article is not complete. It contains only one class, ProductsCL, which only sports a handful of methods. Moreover, only a single ASP.NET page uses the CL (~/Caching/FromTheArchitecture.aspx) – all others still reference the BLL directly. If you plan on using a CL in your application, all calls from the Presentation Layer should go to the CL, which would require that the CL’s classes and methods covered those classes and methods in the BLL currently used by the Presentation Layer.

Summary

While caching can be applied at the Presentation Layer with ASP.NET 2.0’s SqlDataSource and ObjectDataSource controls, ideally caching responsibilities would be delegated to a separate layer in the architecture. In this tutorial we created a Caching Layer that resides between the Presentation Layer and the Business Logic Layer. The Caching Layer needs to provide the same set of classes and methods that exist in the BLL and are called from the Presentation Layer.

The Caching Layer examples we explored in this and the preceding tutorials exhibited reactive loading. With reactive loading, the data is loaded into the cache only when a request for the data is made and that data is missing from the cache. Data can also be proactively loaded into the cache, a technique that loads the data into the cache before it is actually needed. In the next tutorial we’ll see an example of proactive loading when we look at how to store static values into the cache at application startup.

Happy Programming!

About the Author

Scott Mitchell, author of seven ASP/ASP.NET books and founder of 4GuysFromRolla.com, has been working with Microsoft Web technologies since 1998. Scott works as an independent consultant, trainer, and writer. His latest book is Sams Teach Yourself ASP.NET 2.0 in 24 Hours. He can be reached at mitchell@4GuysFromRolla.com. or via his blog, which can be found at http://ScottOnWriting.NET.

Special Thanks To…

This tutorial series was reviewed by many helpful reviewers. Lead reviewer for this tutorial was Teresa Murph. Interested in reviewing my upcoming MSDN articles? If so, drop me a line at mitchell@4GuysFromRolla.com.

Posted in Development. Tags: , , , . No Comments »

Caching Data with the ObjectDataSource

September 29, 2007 — Mikkel Ovesen

Original post here: http://www.asp.net/learn/data-access/tutorial-58-cs.aspx

—

Introduction

In computer science, caching is the process of taking data or information that is expensive to obtain and storing a copy of it in a location that is quicker to access. For data-driven applications, large and complex queries commonly consume the majority of the application’s execution time. Such an application’s performance, then, can often be improved by storing the results of expensive database queries in the application’s memory.

ASP.NET 2.0 offers a variety of caching options. An entire web page or User Control’s rendered markup can be cached through output caching. The ObjectDataSource and SqlDataSource controls provide caching capabilities as well, thereby allowing data to be cached at the control level. And ASP.NET’s data cache provides a rich caching API that enables page developers to programmatically cache objects. In this tutorial and the next three we’ll examine using the ObjectDataSource’s caching features as well as the data cache. We’ll also explore how to cache application-wide data at startup and how to keep cached data fresh through the use of SQL cache dependencies. These tutorials do not explore output caching. For a detailed look at output caching, see Output Caching in ASP.NET 2.0.

Caching can be applied at any place in the architecture, from the Data Access Layer up through the Presentation Layer. In this tutorial we’ll look at applying caching to the Presentation Layer through the ObjectDataSource control. In the next tutorial we’ll examine caching data at the Business Logic Layer.

Key Caching Concepts

Caching can greatly improve an application’s overall performance and scalability by taking data that is expensive to generate and storing a copy of it in a location that can be more efficiently accessed. Since the cache holds just a copy of the actual, underlying data, it can become outdated, or stale, if the underlying data changes. To combat this, a page developer can indicate criteria by which the cache item will be evicted from the cache, using either:

  • Time-based criteria – an item may be added to the cache for an absolute or sliding duration. For example, a page developer may indicate a duration of, say, 60 seconds. With an absolute duration, the cached item is evicted 60 seconds after it was added to cache, regardless of how frequently it was accessed. With a sliding duration, the cached item is evicted 60 seconds after the last access.
  • Dependency-based criteria – a dependency can be associated with an item when added to the cache. When the item’s dependency changes it is evicted from the cache. The dependency may be a file, another cache item, or a combination of the two. ASP.NET 2.0 also allows SQL cache dependencies, which enable developers to add an item to the cache and have it evicted when the underlying database data changes. We will examine SQL cache dependencies in the upcoming Using SQL Cache Dependencies tutorial.

Regardless of the eviction criteria specified, an item in the cache may be scavenged before the time-based or dependency-based criteria has been met. If the cache has reached its capacity, existing items must be removed before new ones can be added. Consequently, when programmatically working with cached data it’s vital that you always assume that the cached data may not be present. We’ll look at the pattern to use when accessing data from the cache programmatically in our next tutorial, Caching Data in the Architecture.

Caching provides an economical means for squeezing more performance from an application. As Steven Smith articulates in his article ASP.NET Caching: Techniques and Best Practices:

“Caching can be a good way to get ‘good enough’ performance without requiring a lot of time and analysis. … Memory is cheap, so if you can get the performance you need by caching the output for 30 seconds instead of spending a day or a week trying to optimize your code or database, do the caching solution (assuming 30-second old data is ok) and move on. … Eventually, poor design will probably catch up to you, so of course you should try to design your applications correctly. But if you just need to get good enough performance today, caching can be an excellent [approach], buying you time to refactor your application at a later date when you have the time to do so.”

While caching can provide appreciable performance enhancements, it is not applicable in all situations, such as with applications that use real-time, frequently-updating data, or where even shortly-lived stale data is unacceptable. But for the majority of applications, caching should be used. For more background on caching in ASP.NET 2.0, refer to the Caching for Performance section of the ASP.NET 2.0 QuickStart Tutorials.

Step 1: Creating the Caching Web Pages

Before we start our exploration of the ObjectDataSource’s caching features, let’s first take a moment to create the ASP.NET pages in our website project that we’ll need for this tutorial and the next three. Start by adding a new folder named Caching. Next, add the following ASP.NET pages to that folder, making sure to associate each page with the Site.master master page:

  • Default.aspx
  • ObjectDataSource.aspx
  • FromTheArchitecture.aspx
  • AtApplicationStartup.aspx
  • SqlCacheDependencies.aspx

Add the ASP.NET Pages for the Caching-Related Tutorials

Figure 1: Add the ASP.NET Pages for the Caching-Related Tutorials

Like in the other folders, Default.aspx in the Caching folder will list the tutorials in its section. Recall that the SectionLevelTutorialListing.ascx User Control provides this functionality. Therefore, add this User Control to Default.aspx by dragging it from the Solution Explorer onto the page’s Design view.

Figure 2: Add the SectionLevelTutorialListing.ascx User Control to Default.aspx

Figure 2: Figure 2: Add the SectionLevelTutorialListing.ascx User Control to Default.aspx (Click to view full-size image)

Lastly, add these pages as entries to the Web.sitemap file. Specifically, add the following markup after the “Working with Binary Data” <siteMapNode>:

<siteMapNode title="Caching" url="~/Caching/Default.aspx" description="Learn how to use the caching features of ASP.NET 2.0."> <siteMapNode url="~/Caching/ObjectDataSource.aspx" title="ObjectDataSource Caching" description="Explore how to cache data directly from the ObjectDataSource control." /> <siteMapNode url="~/Caching/FromTheArchitecture.aspx" title="Caching in the Architecture" description="See how to cache data from within the architecture." /> <siteMapNode url="~/Caching/AtApplicationStartup.aspx" title="Caching Data at Application Startup" description="Learn how to cache expensive or infrequently-changing queries at the start of the application." /> <siteMapNode url="~/Caching/SqlCacheDependencies.aspx" title="Using SQL Cache Dependencies" description="Examine how to have data automatically expire from the cache when its underlying database data is modified." /> </siteMapNode>

After updating Web.sitemap, take a moment to view the tutorials website through a browser. The menu on the left now includes items for the caching tutorials.

The Site Map Now Includes Entries for the Caching Tutorials

Figure 3: The Site Map Now Includes Entries for the Caching Tutorials

Step 2: Displaying a List of Products in a Web Page

This tutorial explores how to use the ObjectDataSource control’s built-in caching features. Before we can look at these features, though, we first need a page to work from. Let’s create a web page that uses a GridView to list product information retrieved by an ObjectDataSource from the ProductsBLL class.

Start by opening the ObjectDataSource.aspx page in the Caching folder. Drag a GridView from the Toolbox onto the Designer, set its ID property to Products, and, from its smart tag, choose to bind it to a new ObjectDataSource control named ProductsDataSource. Configure the ObjectDataSource to work with the ProductsBLL class.

Configure the ObjectDataSource to Use the ProductsBLL Class

Figure 4: Configure the ObjectDataSource to Use the ProductsBLL Class (Click to view full-size image)

For this page, let’s create an editable GridView so that we can examine what happens when data cached in the ObjectDataSource is modified through the GridView’s interface. Leave the drop-down list in the SELECT tab set to its default, GetProducts(), but change the selected item in the UPDATE tab to the UpdateProduct overload that accepts productName, unitPrice, and productID as its input parameters.

Set the UPDATE Tab’s Drop-Down List to the Appropriate UpdateProduct Overload

Figure 5: Set the UPDATE Tab’s Drop-Down List to the Appropriate UpdateProduct Overload (Click to view full-size image)

Finally, set the drop-down lists in the INSERT and DELETE tabs to “(None)” and click Finish. Upon completing the Configure Data Source wizard, Visual Studio sets the ObjectDataSource’s OldValuesParameterFormatString property to original_{0}. As discussed in the An Overview of Inserting, Updating, and Deleting Data tutorial, this property needs to be removed from the declarative syntax or set back to its default value, {0}, in order for our update workflow to proceed without error.

Furthermore, at the completion of the wizard Visual Studio adds a field to the GridView for each of the product data fields. Remove all but the ProductName, CategoryName, and UnitPrice BoundFields. Next, update the HeaderText properties of each of these BoundFields to “Product”, “Category”, and “Price”, respectively. Since the ProductName field is required, convert the BoundField into a TemplateField and add a RequiredFieldValidator to the EditItemTemplate. Similarly, convert the UnitPrice BoundField into a TemplateField and add a CompareValidator to ensure that the value entered by the user is a valid currency value that’s greater than or equal to zero. In addition to these modifications, feel free to perform any aesthetic changes, such as right-aligning the UnitPrice value, or specifying the formatting for the UnitPrice text in its read-only and editing interfaces.

Make the GridView editable by checking the “Enable Editing” checkbox in the GridView’s smart tag. Also check the “Enable Paging” and “Enable Sorting” checkboxes.

Note: Need a review of how to customize the GridView’s editing interface? If so, refer back to the Customizing the Data Modification Interface tutorial.

Enable GridView Support for Editing, Sorting, and Paging

Figure 6: Enable GridView Support for Editing, Sorting, and Paging (Click to view full-size image)

After making these GridView modifications, the GridView and ObjectDataSource’s declarative markup should look similar to the following:

<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductID" DataSourceID="ProductsDataSource" AllowPaging="True" AllowSorting="True"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:TemplateField HeaderText="Product" SortExpression="ProductName"> <EditItemTemplate> <asp:TextBox ID="ProductName" runat="server" Text='<%# Bind("ProductName") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" Display="Dynamic" ControlToValidate="ProductName" SetFocusOnError="True" ErrorMessage="You must provide a name for the product." runat="server">*</asp:RequiredFieldValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("ProductName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:BoundField DataField="CategoryName" HeaderText="Category" ReadOnly="True" SortExpression="CategoryName" /> <asp:TemplateField HeaderText="Price" SortExpression="UnitPrice"> <EditItemTemplate> $<asp:TextBox ID="UnitPrice" runat="server" Columns="8" Text='<%# Bind("UnitPrice", "{0:N2}") %>'></asp:TextBox> <asp:CompareValidator ID="CompareValidator1" 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" runat="server" ValueToCompare="0">*</asp:CompareValidator> </EditItemTemplate> <ItemStyle HorizontalAlign="Right" /> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("UnitPrice", "{0:c}") %>' /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:ObjectDataSource ID="ProductsDataSource" runat="server" OldValuesParameterFormatString="{0}" 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>

As Figure 7 shows, the editable GridView lists the name, category, and price of each of the products in the database. Take a moment to test out the page’s functionality – sort the results, page through them, and edit a record.

Each Product’s Name, Category, and Price is Listed in a Sortable, Pageable, Editable GridView

Figure 7: Each Product’s Name, Category, and Price is Listed in a Sortable, Pageable, Editable GridView (Click to view full-size image)

Step 3: Examining When the ObjectDataSource is Requesting Data

The Products GridView retrieves its data to display by invoking the Select method of the ProductsDataSource ObjectDataSource. This ObjectDataSource creates an instance of the Business Logic Layer’s ProductsBLL class and calls its GetProducts() method, which in turn calls the Data Access Layer’s ProductsTableAdapter’s GetProducts() method. The DAL method connects to the Northwind database and issues the configured SELECT query. This data is then returned to the DAL, which packages it up in a NorthwindDataTable. The DataTable object is returned to the BLL, which returns it to the ObjectDataSource, which returns it to the GridView. The GridView then creates a GridViewRow object for each DataRow in the DataTable, and each GridViewRow is eventually rendered into the HTML that is returned to the client and displayed on the visitor’s browser.

This sequence of events happens each and every time the GridView needs to bind to its underlying data. That happens when the page is first visited, when moving from one page of data to another, when sorting the GridView, or when modifying the GridView’s data through its built-in editing or deleting interfaces. If the GridView’s view state is disabled, the GridView will be rebound on each and every postback as well. The GridView can also be explicitly rebound to its data by calling its DataBind() method.

To fully appreciate the frequency with which the data is retrieved from the database, let’s display a message indicating when the data is being re-retrieved. Add a Label Web control above the GridView named ODSEvents. Clear out its Text property and set its EnableViewState property to false. Underneath the Label, add a Button Web control and set its Text property to “Postback”.

Add a Label and Button to the Page Above the GridView

Figure 8: Add a Label and Button to the Page Above the GridView (Click to view full-size image)

During the data access workflow, the ObjectDataSource’s Selecting event fires before the underlying object is created and its configured method invoked. Create an event handler for this event and add the following code:

protected void ProductsDataSource_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { ODSEvents.Text = "-- Selecting event fired"; }

Each time the ObjectDataSource makes a request to the architecture for data, the Label will display the text “Selecting event fired”.

Visit this page in a browser. When the page is first visited, the text “Selecting event fired” is shown. Click the “Postback” button and note that the text disappears (assuming that the GridView’s EnableViewState property is set to true, the default). This is because, on postback, the GridView is reconstructed from its view state and therefore doesn’t turn to the ObjectDataSource for its data. Sorting, paging, or editing the data, however, causes the GridView to rebind to its data source, and therefore the “Selecting event fired” text reappears.

Whenever the GridView is Rebound to its Data Source, “Selecting event fired” is Displayed

Figure 9: Whenever the GridView is Rebound to its Data Source, “Selecting event fired” is Displayed (Click to view full-size image)

Clicking the Postback Button Causes the GridView to be Reconstructed from its View State

Figure 10: Clicking the Postback Button Causes the GridView to be Reconstructed from its View State (Click to view full-size image)

It may seem wasteful to retrieve the database data each time the data is paged through or sorted. After all, since we’re using default paging, the ObjectDataSource has retrieved all of the records when displaying the first page. Even if the GridView does not provide sorting and paging support, the data must be retrieved from the database each time the page is first visited by any user (and on every postback, if view state is disabled). But if the GridView is showing the same data to all users, these extra database requests are superfluous. Why not cache the results returned from the GetProducts() method and bind the GridView to those cached results?

Step 4: Caching the Data Using the ObjectDataSource

By simply setting a few properties, the ObjectDataSource can be configured to automatically cache its retrieved data in the ASP.NET data cache. The following list summarizes the cache-related properties of the ObjectDataSource:

  • EnableCaching – must be set to true to enable caching. The default is false.
  • CacheDuration – the amount of time, in seconds, that the data is cached. The default is 0. The ObjectDataSource will only cache data if EnableCaching is true and CacheDuration is set to a value greater than zero.
  • CacheExpirationPolicy – can be set to Absolute or Sliding. If Absolute, the ObjectDataSource caches its retrieved data for CacheDuration seconds; if Sliding, the data expires only after it has not been accessed for CacheDuration seconds. The default is Absolute.
  • CacheKeyDependency – use this property to associate the ObjectDataSource’s cache entries with an existing cache dependency. The ObjectDataSource’s data entries can be prematurely evicted from the cache by expiring its associated CacheKeyDependency. This property is most commonly used to associate a SQL cache dependency with the ObjectDataSource’s cache, a topic we’ll explore in the future Using SQL Cache Dependencies tutorial.

Let’s configure the ProductsDataSource ObjectDataSource to cache its data for 30 seconds on an absolute scale. Set the ObjectDataSource’s EnableCaching property to true and its CacheDuration property to 30. Leave the CacheExpirationPolicy property set to its default, Absolute.

Configure the ObjectDataSource to Cache its Data for 30 Seconds

Figure 11: Configure the ObjectDataSource to Cache its Data for 30 Seconds (Click to view full-size image)

Save your changes and revisit this page in a browser. The “Selecting event fired” text will appear when you first visit the page, as initially the data is not in the cache. But subsequent postbacks triggered by clicking the “Postback” button, sorting, paging, or clicking the Edit or Cancel buttons does not redisplay the “Selecting event fired” text. This is because the Selecting event only fires when the ObjectDataSource gets its data from its underlying object; the Selecting event does not fire if the data is pulled from the data cache.

After 30 seconds, the data will be evicted from the cache. The data will also be evicted from the cache if the ObjectDataSource’s Insert, Update, or Delete methods are invoked. Consequently, after 30 seconds have passed or the Update button has been clicked, sorting, paging, or clicking the Edit or Cancel buttons will cause the ObjectDataSource to get its data from its underlying object, displaying the “Selecting event fired” text when the Selecting event fires. These returned results are placed back into the data cache.

Note: If you see the “Selecting event fired” text frequently, even when you expect the ObjectDataSource to be working with cached data, it may be due to memory constraints. If there is not enough free memory, the data added to the cache by the ObjectDataSource may have been scavenged. If the ObjectDataSource doesn’t appear to be correctly caching the data or only caches the data sporadically, close some applications to free memory and try again.

Figure 12 illustrates the ObjectDataSource’s caching workflow. When the “Selecting event fired” text appears on your screen, it is because the data was not in the cache and had to be retrieved from the underlying object. When this text is missing, however, it’s because the data was available from the cache. When the data is returned from the cache there’s no call to the underlying object and, therefore, no database query executed.

The ObjectDataSource Stores and Retrieves its Data from the Data Cache

Figure 12: The ObjectDataSource Stores and Retrieves its Data from the Data Cache

Each ASP.NET application has its own data cache instance that’s shared across all pages and visitors. That means that the data stored in the data cache by the ObjectDataSource is likewise shared across all users who visit the page. To verify this, open the ObjectDataSource.aspx page in a browser. When first visiting the page, the “Selecting event fired” text will appear (assuming that the data added to the cache by previous tests has, by now, been evicted). Open a second browser instance and copy and paste the URL from the first browser instance to the second. In the second browser instance, the “Selecting event fired” text is not shown because it’s using the same cached data as the first.

When inserting its retrieved data into the cache, the ObjectDataSource uses a cache key value that includes: the CacheDuration and CacheExpirationPolicy property values; the type of the underlying business object being used by the ObjectDataSource, which is specified via the TypeName property (ProductsBLL, in this example); the value of the SelectMethod property and the name and values of the parameters in the SelectParameters collection; and the values of its StartRowIndex and MaximumRows properties, which are used when implementing custom paging.

Crafting the cache key value as a combination of these properties ensures a unique cache entry as these values change. For example, in past tutorials we’ve looked at using the ProductsBLL class’s GetProductsByCategoryID(categoryID), which returns all products for a specified category. One user might come to the page and view beverages, which has a CategoryID of 1. If the ObjectDataSource cached its results without regard for the SelectParameters’ values, when another user came to the page to view condiments while the beverages products were in the cache, they’d see the cached beverage products rather than condiments. By varying the cache key by these properties, which include the values of the SelectParameters, the ObjectDataSource maintains a separate cache entry for beverages and condiments.

Stale Data Concerns

The ObjectDataSource automatically evicts its items from the cache when any one of its Insert, Update, or Delete methods is invoked. This helps protect against stale data by clearing out the cache entries when the data is modified through the page. However, it is possible for an ObjectDataSource using caching to still display stale data. In the simplest case, it can be due to the data changing directly within the database. Perhaps a database administrator just ran a script that modifies some of the records in the database.

This scenario could also unfold in a more subtle way. While the ObjectDataSource evicts its items from the cache when one of its data modification methods is called, the cached items removed are for the ObjectDataSource’s particular combination of property values (CacheDuration, TypeName, SelectMethod, and so on). If you have two ObjectDataSources that use different SelectMethods or SelectParameters, but still can update the same data, then one ObjectDataSource may update a row and invalidate its own cache entries, but the corresponding row for the second ObjectDataSource will still be served from the cached. I encourage you to create pages to exhibit this functionality. Create a page that displays an editable GridView that pulls its data from an ObjectDataSource that uses caching and is configured to get data from the ProductsBLL class’s GetProducts() method. Add another editable GridView and ObjectDataSource to this page (or another one), but for this second ObjectDataSource have it use the GetProductsByCategoryID(categoryID) method. Since the two ObjectDataSources’ SelectMethod properties differ, they’ll each have their own cached values. If you edit a product in one grid, the next time you bind the data back to the other grid (by paging, sorting, and so forth), it will still serve the old, cached data and not reflect the change that was made from the other grid.

In short, only use time-based expiries if you are willing to have the potential of stale data, and use shorter expiries for scenarios where the freshness of data is important. If stale data is not acceptable, either forgo caching or use SQL cache dependencies (assuming it is database data you’re caching). We’ll explore SQL cache dependencies in a future tutorial.

Summary

In this tutorial we examined the ObjectDataSource’s built-in caching capabilities. By simply setting a few properties, we can instruct the ObjectDataSource to cache the results returned from the specified SelectMethod into the ASP.NET data cache. The CacheDuration and CacheExpirationPolicy properties indicate the duration the item is cached and whether it is an absolute or sliding expiration. The CacheKeyDependency property associates all of the ObjectDataSource’s cache entries with an existing cache dependency. This can be used to evict the ObjectDataSource’s entries from the cache before the time-based expiration is reached, and is typically used with SQL cache dependencies.

Since the ObjectDataSource simply caches its values to the data cache, we could replicate the ObjectDataSource’s built-in functionality programmatically. It doesn’t make sense to do this at the Presentation Layer, since the ObjectDataSource offers this functionality out of the box, but we can implement caching capabilities in a separate layer of the architecture. To do so, we’ll need to repeat the same logic used by the ObjectDataSource. We’ll explore how to programmatically work with the data cache from within the architecture in our next tutorial.

Happy Programming!

Further Reading

For more information on the topics discussed in this tutorial, refer to the following resources:

About the Author

Scott Mitchell, author of seven ASP/ASP.NET books and founder of 4GuysFromRolla.com, has been working with Microsoft Web technologies since 1998. Scott works as an independent consultant, trainer, and writer. His latest book is Sams Teach Yourself ASP.NET 2.0 in 24 Hours. He can be reached at mitchell@4GuysFromRolla.com. or via his blog, which can be found at http://ScottOnWriting.NET.

Special Thanks To…

This tutorial series was reviewed by many helpful reviewers. Lead reviewer for this tutorial was Teresa Murphy. Interested in reviewing my upcoming MSDN articles? If so, drop me a line at mitchell@4GuysFromRolla.com.

Posted in Development. Tags: , , . No Comments »