Section 17.2. Data Caching
17.2. Data CachingData caching is (surprise!) the caching of data from a data source. As long as the cache is not expired, a request for data will be fulfilled from the cache rather than the original data source. If the cache is expired , for whatever reason, then fresh data will be obtained by the data source and the cache will be refreshed. There are many reasons the cache can expire, as you will see. These can include time outs, changed data, or changes to other objects. There are two kinds of data caching : DataSourceControl caching and SQL cache dependency . Both are very useful. 17.2.1. DataSourceControl CachingData source controls, covered in Chapter 9, represent data in a data source, such as a database or an XML file. The abstract DataSourceControl class has two controls derived from it: ObjectDataSource and SqlDataSource , from which AccessDataSource is derived. Each of these derived classes (though not the base class) has a number of properties for implementing caching, listed in Table 17-1. Table 17-1. Data source control properties for caching
To show this in action, copy the web site called WebNorthwind , created back in Chapter 9 where data controls are first covered, to a new web site called WebNorth-windCache . To simplify the demonstration, remove the UpdateCommand , DeleteCommand , and InsertCommand attributes from the SqlDataSource declaration, the associated Delete , Update , and Insert parameters, the bound columns from Region on, and the command field at the beginning of the Columns collection in the GridView declaration. Also, since you will not be updating data in this example, you can remove the OnRowUpdated attribute from the GridView declaration and the GridView1_RowUpdated method from the code-behind file, as well. Finally, to simplify the example more, remove the OnRowDataBound attribute from the GridView control in the content file and the associated event handler from the code-behind file. To differentiate between when the page has been rendered from the server and when it has been retrieved from cache, add a Label control to the page and some code to the Page_Load in the code-behind file to populate the label with the current time. The complete content file, with the Label control and associated caption highlighted, is listed in Example 17-1. Example 17-1. Default.aspx for WebNorthwindCache <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Customers.aspx.cs" Inherits="Customers_aspx" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Data Caching</title> </head> <body> <form id="form1" runat="server"> <div> <h1>Data Caching</h1> Page Posted: <asp:Label ID="lblPostTime" runat="server" /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString= "<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="SELECT * FROM [Customers]" > </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID" DataSourceID="SqlDataSource1" AllowSorting="True" CellPadding="4" AllowPaging="True" ForeColor="#333333" GridLines="None"> <Columns> <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" /> <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" /> <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" /> <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle" SortExpression="ContactTitle" /> <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" /> <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" /> </Columns> <FooterStyle BackColor="#5D7B9D" ForeColor="White" Font-Bold="True" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <PagerStyle ForeColor="White" HorizontalAlign="Center" BackColor="#284775" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle Font-Bold="False" Font-Italic="False" BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </div> </form> </body> </html> The complete code-behind file is listed in Example 17-2, with the highlighted line of code added to the Page_Load method. Example 17-2. default.aspx.cs for WebNorthwindCache using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class Customers_aspx : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { lblPostTime.Text = DateTime.Now.ToLongTimeString( ); } } When you run this web site, you will get something like that shown in Figure 17-1. At this point, this web site has no caching enabled. You can see this by refreshing the browser window and seeing the displayed timestamp update. You can change the data in the underlying database (use SQL Server Enterprise Manager, Query Analyzer, or the Server Explorer in VS2005) and see the new values appear as soon as you refresh the page. Now implement data caching in the SqlDataSource by adding the EnableCaching and CacheDuration attributes to the declaration from Example 17-1, as shown highlighted in Example 17-3. Figure 17-1. WebNorthwindCache Example 17-3. SqlDataSource with caching implemented <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="SELECT * FROM [Customers]" EnableCaching=true CacheDuration=60> </asp:SqlDataSource> Setting EnableCaching to TRue implements caching, and setting the CacheDuration to 60 means the cache will expire every minute. You can verify this by changing some data in the Customers table and refreshing the web page in the browser. Though the timestamp displayed on the page will update every time, the data will not refresh until the 60 seconds have passed. The CacheDuration is subject to the value of the CacheExpirationPolicy property, which is covered in detail in the section below on Time Dependency under Object Caching. In short, the default value for CacheExpirationPolicy is Absolute , which causes the cache to expire at the end of the CacheDuration setting whether or not data access has occurred during that time. 17.2.2. SQL Cache DependencySQL cache dependency is the caching of data from a SQL Server database, specifically , SQL Server 7.0 or later, and expiring the cache whenever data in the database changes. It is not supported for other databases, including Access and Oracle (surprise!). There are two different mechanisms used to support SQL cache dependency. Polling-based invalidation is used for SQL Server 7.0 and 2000. Notification-based invalidation is used for SQL Server 2005. Both mechanisms will be described in the following sections. 17.2.2.1. Polling-based cache invalidationSQL cache dependency in SQL Server 7.0 and 2000 works by inserting a special table, five stored procedures (all with names beginning with AspNet_SqlCache ), and a trigger for each table being watched ( tableName _AspNet_SqlCacheNotification_Trigger ) into the database to monitor if any data has changed. One or more tables in the database can be watched. When data is modified in a table under watch, the special table for keeping track, called AspNet_SqlCacheTablesForChangeNotification , is updated. Periodically, the ASP.NET application polls the database to see if any changes have occurred. If they have, then the data cache is invalidated and fresh data is retrieved from the database. The actual implementation of SQL cache dependency is a three-part process. In the first part, the SQL Server database is prepared to support data caching or SQL cache dependency . In the second step, the cache is set up in the web.config file for the application. (Configuration is covered in Chapter 18.) Both of those steps will be covered in this section. Finally, the data cache is actually used, typically in the context of an output cache. Output caching will be covered in the next section. Preparing the database . To prepare SQL Server to support SQL cache dependency, you must first run the SQL Server administrative command line tool, aspnet_regsql .
You can see all the options available to this tool by running the following command: aspnet_regsql -? There is a wizard mode to this utility, entered by running the command from the command line without any options, but the wizard mode does not handle setting up the database for SQL cache dependency, so you must do it all from the command line. This is easy if you either have a working connection string to the database or are using Windows integrated security on the database. The command-line switches relevant to SQL cache dependency are listed in Table 17-2. Note that this utility serves many functions, including configuration of SQL Server Express, session state options, and other ASP.NET application services. Table 17-2. aspnet_regsql command line switches relevant to SQL cache dependency
First, you must prepare the database by creating the table AspNet_SqlCacheTablesFor-ChangeNotification for keeping track of changes and by creating the stored procedures. If you are using Windows integrated security, enter the following command: aspnet_regsql -E -d Northwind -ed The first argument, -E , says to use Windows authentication. -d specifies the database, and -ed says enable data caching. If you are using a connection string, you do not specify the database since that is in the connection string, as in the following example: aspnet_regsql -C "Data Source=MyServer;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=secret" -ed Alternatively, you could use the -S , -U , and -P options to provide the credentials individually. Adding a table for SQL cache dependency depends on your security method. If you are using Windows integrated security, use the following line: aspnet_regsql -E -d Northwind -t Customers -et or, with a connection string: aspnet_regsql -C "Data Source=MyServer;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=secret" -t Customers -et This adds a trigger called Customers_AspNet_SqlCacheNotification_Trigger to the Customers table, which calls the stored procedure AspNet_SqlCacheUpdateChangeId-StoredProcedure every time there is a change to the data in the Customers table. To list all the tables enabled, use the following command: aspnet_regsql -C "Data Source=MyServer;Initial Catalog=Northwind;Persist Security Info =True;User ID=sa;Password=secret" -lt An alternative to using the above command to list all the tables being monitored is to query the table AspNet_SqlCacheTablesForChangeNotification directly. The database is now ready to support SQL cache dependency . All of the above functionality can also be accomplished in code, using the SqlCacheDependencyAdmin class. This class has five methods for setting up and administering a SQL Server database for SQL cache dependency:
Usage of these SqlCacheDependencyAdmin classes is beyond the scope of this book. Edit web.config . The second step in implementing SQL cache dependency is to edit the web.config file of the application. Continue working with the web site called WebNorthwindCache copied from WebNorthwind in the section "DataSourceControl Caching." Open web.config in VS2005. It should have a <connectionStrings> section under <configuration> shown in the partial web.config listed in Example 17-4. Add the highlighted <caching> section under <system.web> from Example 17-4. Example 17-4. Partial web.config for WebNorthwindCache <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <appSettings/> <connectionStrings> <add name="NorthwindConnectionString" connectionString="Data Source=myServer; Initial Catalog=Northwind;Persist Security Info=True; User ID=sa;Password=secret" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <caching> <sqlCacheDependency enabled="true" > <databases> <add name="Northwind" connectionStringName="NorthwindConnectionString" pollTime="1000" /> </databases> </sqlCacheDependency> </caching> . . . </system.web> The <sqlCacheDependency> element has two possible attributes. enabled can be true or false . pollTime is the number of milliseconds between polls that SQL Server does to determine if the data has changed. If the latter is omitted from this element, the value in the <add> sections for individual databases will apply. Within the <sqlCacheDependency> element are one or more <databases> sections. This contains <add> , <clear> , or <remove> sections. To add a database, as in Example 17-4, you provide the name , connectionString , and pollTime properties as attributes. To remove a database, you need only provide the name attribute. <clear> takes no attributes. With the SQL cache dependency configured in web.config , you are ready to cache the data and have the cache expire when the data changes. 17.2.2.2. Notification-based cache invalidationSQL cache dependency in SQL Server 2005 works by using the query change notification mechanism built into the database. It requires much less setup than polling-based cache invalidation used in earlier versions of SQL Server. There is no need to configure the database with aspnet_regsql.exe , and there is no need to add any <sqlCacheDependency> element to web.config . To enable notification-based cache invalidation for a page, add a SqlDependency attribute to the OutputCache directive, described below, with a value of CommandNotification : <%@ OutputCache Duration="999999" SqlDependency="CommandNotification" VaryByParam="none" %> To enable notification-based cache invalidation for a DataSource control, add a SqlCacheDependency attribute to the control declaration, again with a value of CommandNotification : <asp:SqlDataSource ID="SqlDataSource1" runat="server" . . . SqlCacheDependency="CommandNotification" EnableCaching="true" CacheDuration="Infinite"> </asp:SqlDataSource> In either case, ASP.NET and ADO.NET will work together to create a cache dependency that detects change notifications sent from SQL Server and invalidates the cache when the data is changed. |
Programs 
EAN: 2147483647
Pages: 173