WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
The ASP.NET 1.x Cache API was a revolutionary feature that provided capabilities such as declarative output caching, programmatic output caching, and invalidation of cached items when the contents of an XML file or another cached item change. Even though all these increased the performance of Web applications, ASP.NET 1.x did not provide a mechanism for invalidating the data in a cache object when the data in a database changes. This much sought after feature will finally ship with ASP.NET 2.0. Apart from this, ASP.NET 2.0 will also provide functionalities to cache the output of a SqlDataSource control, which enable you to take advantage of caching without writing a single line of code. This article discusses some of these new caching features and demonstrates how you can employ them in your Web applications.
In ASP.NET 2.0, caching has improved in a couple of notable ways. Probably the most interesting improvement is the introduction of database-triggered cache invalidation. In ASP.NET 1.x, you can invalidate a cached item based on some pre-defined conditions, such as change in an XML file, change in another cache item, and so on. By using this feature, you can remove or invalidate an item from the cache when the data or another cached item changes. However, the ASP.NET 1.x Cache API does not allow you to invalidate an item in the cache when data in a SQL Server database changes, even though most applications require this capability. ASP.NET 2.0 addresses this by providing the database-triggered cache invalidation capability that allows you to ensure that items in the cache are kept up to date with the changes in the database.
Another important caching feature in ASP.NET 2.0 is the ability to enable caching at the SqlDataSource level. The SqlDataSource control is designed to work with SQL Server, OLE DB, ODBC, and Oracle databases. As the name suggests, this control enables you to select, update, delete, and insert data using SQL commands. With the ability to set caching attributes at the SqlDataSource control level, you now have a finer level of control over the cached data.
ASP.NET 2.0 also provides a new control named Substitution, which you can use to inject dynamic content in an otherwise cached Web page. If you have a page with output-caching but still want to display dynamic content (that needs to be generated every time the page is requested), consider using the Substitution control.
The following sections provide examples of the above features.
Time-Based Cache Invalidation in a SqlDataSource Control
Caching in ASP.NET is a powerful feature that can increase the performance of a Web application. In fact, the most dramatic way to improve the performance of a database-driven Web application is through caching. Retrieving data from a database is one of the slowest Web site operations that you can perform. However, if you can cache the database data in memory and avoid accessing the database with every page request, you can dramatically increase the performance of your application.
ASP.NET 2.0 provides a number of enhancements to the caching feature set in ASP.NET 1.x. One new feature is the ability to specify the caching attributes as part of the data source control declarations. The new data source controls in ASP.NET 2.0 work seamlessly with the new caching features of ASP.NET 2.0, enabling you to set the caching attributes as part of the SqlDataSource control declaration.
You can set the following two properties in the SqlDataSource control to enable caching:
- EnableCaching—By setting this attribute to true, you enable caching in a SqlDataSource control.
- CacheDuration—This property allows you to set or get the duration of the cached data in the SqlDataSource control. This attribute is specified in terms of seconds.
For the purposes of this example, consider a categories and products table in the Northwind database. It displays all the categories in a DropDownList and the products that belong to a specific category in a GridView control. Start by creating a new Web site named Caching in Visual Studio 2005. Next, add a Web page named TimeBasedCaching.aspx to it. Modify the code in the TimeBasedCaching.aspx file to look like the following:
<%@ Page Language="C#" %> <html> <head> <title>SqlDataSource Control Caching And Parameters</title> </head> <body> <form id="form1" runat="server"> <asp:DropDownList DataValueField="CategoryID" DataTextField="CategoryName" DataSourceID="CategoriesDataSource" ID="DropDownList1" Runat="server" AutoPostBack="True"> </asp:DropDownList> <br/><br/> <asp:GridView ID="GridView1" Runat="server" DataSourceID="ProductsDataSource" DataKeyNames="ProductID" AutoGenerateColumns="False"> <Columns> <asp:BoundField HeaderText="ProductID" DataField="ProductID" SortExpression="ProductID" /> <asp:BoundField HeaderText="Timestamp" DataField="Timestamp" SortExpression="Timestamp" /> <asp:BoundField HeaderText="ProductName" DataField="ProductName" SortExpression="ProductName" /> <asp:BoundField HeaderText="QuantityPerUnit" DataField="QuantityPerUnit" SortExpression="QuantityPerUnit" /> <asp:BoundField HeaderText="UnitPrice" DataField="UnitPrice" SortExpression="UnitPrice" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="ProductsDataSource" Runat="server" SelectCommand="SELECT DatePart(second, GetDate()) As Timestamp, * FROM [Products] where CategoryID = @CategoryID" ConnectionString="<%$ ConnectionStrings:Northwind %>" EnableCaching="True" CacheDuration="10"> <SelectParameters> <asp:ControlParameter Name="CategoryID" ControlID="DropDownList1" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="CategoriesDataSource" Runat="server" SelectCommand="SELECT * FROM [Categories]" ConnectionString="<%$ ConnectionStrings:Northwind %>" EnableCaching="True" CacheDuration="10"/> </form> </body> </html>
In the preceding code, the connection string to the database is retrieved from the web.config file. The web.config file contains the following connectionStrings element:
<connectionStrings> <add name="Pubs" connectionString="server=localhost;database=Pubs; trusted_connection=true"/> <add name="Northwind" connectionString="server=localhost;database=Pubs; trusted_connection=true"/> </connectionStrings>
Now that the required connection string is defined in the web.config file, the SqlDataSource control can use that connection string by using the following declaration:
<%$ ConnectionStrings:Northwind %>
The above code retrieves the connection string value defined in the connectionString attribute of the Northwind connection string element.
The SqlDataSource control also has the EnableCaching property set to true, which results in the SqlDataSource automatically caching the data retrieved by the SelectCommand. The CacheDuration property enables you to specify (in seconds) how long the data should be cached before it is refreshed from the database. By default, the SqlDataSource will cache data using an absolute expiration policy, meaning that the data will be refreshed every so many seconds as specified in the CacheDuration property.
You also have the option of configuring the SqlDataSource to use a sliding expiration policy, by which the data is not dropped as long as it continues to be accessed. Employing a sliding expiration policy is useful whenever you have a large number of items that need to be cached, because the policy enables you to keep only the most frequently accessed items in memory. In the above example, you cached the results of the SQL query for 10 seconds by setting the EnableCaching and CacheDuration attributes to True and 10, respectively (see the output screenshot below).
As you can see, the values in the timestamp column remain the same for 10 seconds. After that, they will be refreshed with the new timestamp values from the database.