Recipe 16.9. Caching Application Data Based on Database Dependencies


Problem

Your application draws on data stored in database that is expensive to create from a performance perspective, so you want to store the data in memory, where it can be accessed by users throughout the lifetime of the application. The problem is that the data changes occasionally and you need to refresh the data when it changes.

Solution

Configure your SQL Server database and add the <sqlCacheDependency> element to web.config as described in Recipe 16.5, store the data in the Cache with a SqlCache-Dependency, and access the data in the Cache as required in your application.

The application we have implemented to demonstrate the solution is shown in Examples 16-7, 16-8 through 16-9. Example 16-7 shows the .aspx file used to display the cached data, and Examples 16-8 (VB) and 16-9 (C#) show the code-behind classes for the page.

Discussion

ASP.NET 2.0 has added a SqlCacheDependency class that can be used to create a dependency to data in a database. The SqlCacheDependency class uses the same infrastructure described in Recipe 16.5 to determine if the data in the database has changed and to cause the data to expire in the Cache when it has.

Once the configuration of SQL Server and the <sqlCacheDependency> element has been added to your web.config, as described in Recipe 16.5, adding data to the Cache with a dependency to data in the database is easy. All that is required is to create an instance of a SqlCacheDependency class defining the database (as delineated in web.config) and table for the dependency and pass this instance when inserting the data in the Cache:

 

sqlDep = New SqlCacheDependency("ASPNetCookbook", _ "Book") Context.Cache.Insert(CAC_BOOK_DATA, _ bookData, _ sqlDep)

sqlDep = new SqlCacheDependency("ASPNetCookbook", "Book"); Context.Cache.Insert(CAC_BOOK_DATA, bookData, sqlDep);

If you are using SQL Server 2005, you can create a SqlCacheDependency object by passing a SqlCommand object to the constructor. Using this approach provides a more explicit definition of the data that is monitored and used for expiring the data in the Cache because a SqlCommand can contain any SQL statement complete with a WHERE clause:

 

cmdText = "SELECT Title, PublishDate, ListPrice " & _ "FROM Book " & _ "WHERE PublishDate>='01/01/2005'" sqlCmd = New SqlCommand(cmdText, _ dbConn) sqlDep = New SqlCacheDependency(sqlCmd)

cmdText = "SELECT Title, PublishDate, ListPrice " + "FROM Book " + "WHERE PublishDate>='01/01/2005'"; sqlCmd = New SqlCommand(cmdText, dbConn); sqlDep = New SqlCacheDependency(sqlCmd);

As this recipe shows, being able to cache application data based on a database dependency has become easy in ASP.NET 2.0. At the same time, it represents an improvement in the way that ASP.NET applications can be built and deployed.

The caching of data based on database dependencies should not be used for in-memory databases, since it results in replicating the data in memory with no appreciable performance gain.


See Also

Recipe 16.5

Example 16-7. Expiring cache data with a database dependency (.aspx)

 <%@ Page Language="VB" MasterPageFile="~/ASPNetCookbookVB.master"       AutoEventWireup="false"   CodeFile="CH16ExpireDatabaseDependentCacheDataVB.aspx.vb"   Inherits="ASPNetCookbook.VBExamples.CH16ExpireDatabaseDependentCacheDataVB"   Title="Cache Data With Database Dependency" %> <asp:Content  runat="server" ContentPlaceHolder>       <div align="center" >           Cache Data With Database Dependency (VB)      </div>  <div align="center" >           <asp:Label  runat="server" />      </div>  <br />  <asp:GridView  Runat="Server"                       AllowPaging="false"   AllowSorting="false"   AutoGenerateColumns="false"   BorderColor="#000080"   BorderStyle="Solid"   BorderWidth="2px"   Caption=""   HorizontalAlign="Center"   Width="90%" >           <HeaderStyle HorizontalAlign="Center" Css />   <RowStyle css />   <AlternatingRowStyle css />   <Columns>                <asp:BoundField DataField="Title"                                   HeaderText="Title" />                <asp:BoundField HeaderText="Publish Date"                                   DataField="PublishDate"   ItemStyle-HorizontalAlign="Center"   DataFormatString="{0:MMM dd, yyyy}" />                <asp:BoundField HeaderText="List Price"                                   DataField="ListPrice"   ItemStyle-HorizontalAlign="Center"   DataFormatString="{0:C2}" />           </Columns>       </asp:GridView>  </asp:Content> 

Example 16-8. Expiring cache data with a database dependency code-behind (.vb)

 Option Explicit On  Option Strict On Imports System.Configuration  Imports System.Data  Imports System.Data.SqlClient Namespace ASPNetCookbook.VBExamples    ''' <summary>    ''' This class provides the code-behind for    ''' CH16ExpireDatabaseDependentCacheDataVB.aspx    ''' </summary>    Partial Class CH16ExpireDatabaseDependentCacheDataVB     Inherits System.Web.UI.Page     Private Const CAC_BOOK_DATA As String = "CH16BookData" '''*********************************************************************** ''' <summary> ''' This routine provides the event handler for the page load event. It ''' is responsible for initializing the controls on the page. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Private Sub Page_Load(ByVal sender As Object, _                           ByVal e As System.EventArgs) Handles Me.Load        Dim bookData As DataTable    Dim dbConn As SqlConnection = Nothing    Dim da As SqlDataAdapter = Nothing    Dim strConnection As String    Dim cmdText As String    Dim sqlDep As SqlCacheDependency = Nothing    Dim retrievedFromCache As Boolean = True   If (Not Page.IsPostBack) Then         'get the book data from the cache bookData = CType(Cache(CAC_BOOK_DATA), _                          DataTable)         'make sure the data is valid If (IsNothing(bookData)) Then           'data is not in the cache so load it   retrievedFromCache = False   strConnection = ConfigurationManager. _               ConnectionStrings("sqlConnectionString").ConnectionString            dbConn = New SqlConnection(strConnection)             dbConn.Open()   cmdText = "SELECT Title, PublishDate, ListPrice " & _              "FROM Book " & _  "ORDER BY Title" da = New SqlDataAdapter(cmdText, dbConn) bookData = New DataTable da.Fill(bookData)                     'create the SQL dependency to the database table where the  'data was retrieved  sqlDep = New SqlCacheDependency("ASPNetCookbook", _                                                     "Book")                     'store book data in cache with a SQL dependency Context.Cache.Insert(CAC_BOOK_DATA, _                                          bookData, _  sqlDep)           End If 'If (IsNothing(bookData))   'set the source of the data for the gridview control and bind it   gvBooks.DataSource = bookData           gvBooks.DataBind()   'set the label indicating where the data came from    If (retrievedFromCache) Then              labCacheStatus.Text = "Data was retrieved from the cache"            Else              labCacheStatus.Text = "Data was retrieved from the database"            End If        End If 'If (Not Page.IsPostBack)      End Sub 'Page_Load    End Class 'CH16ExpireDatabaseDependentCacheDataVB  End Namespace 

Example 16-9. Expiring cache data with a database dependency code-behind (.cs)

 using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Web.Caching; namespace ASPNetCookbook.CSExamples {    /// <summary>    /// This class provides the code-behind for    /// CH16ExpireDatabaseDependentCacheDataCS.aspx    /// </summary>    public partial class CH16ExpireDatabaseDependentCacheDataCS :     System.Web.UI.Page   {     private const String CAC_BOOK_DATA = "CH16BookData"; '''***********************************************************************  /// <summary> /// This routine provides the event handler for the page load event. /// It is responsible for initializing the controls on the page. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void Page_Load(object sender, EventArgs e) {       DataTable bookData = null;   SqlConnection dbConn = null;   SqlDataAdapter da = null;   String strConnection;   String cmdText;   SqlCacheDependency sqlDep = null;   Boolean retrievedFromCache = true;   if (!Page.IsPostBack)   {         // get the book data from the cache bookData = (DataTable)(Cache[CAC_BOOK_DATA]); // make sure the data is valid if (bookData == null) {           // data is not in the cache so load it   retrievedFromCache = false;   strConnection = ConfigurationManager.               ConnectionStrings["sqlConnectionString"].ConnectionString;            dbConn = new SqlConnection(strConnection);             dbConn.Open();   cmdText = "SELECT Title, PublishDate, ListPrice " +                     "FROM Book " + "ORDER BY Title";           da = new SqlDataAdapter(cmdText, dbConn);   bookData = new DataTable();   da.Fill(bookData);   // create the SQL dependency to the database table where the    // data was retrieved    sqlDep = new SqlCacheDependency("ASPNetCookbook",                                           "Book");           // store book data in cache with a SQL dependency   Context.Cache.Insert(CAC_BOOK_DATA,                                bookData,    sqlDep);         } // If (IsNothing(bookData)) // set the source of the data for the gridview control and bind it gvBooks.DataSource = bookData; gvBooks.DataBind();         // set the label indicating where the data came from if (retrievedFromCache) {           labCacheStatus.Text = "Data was retrieved from the cache";         } else {           labCacheStatus.Text = "Data was retrieved from the database";          }        } // If (Not Page.IsPostBack)      } // Page_Load    } // CH16ExpireDatabaseDependentCacheDataCS  } 



ASP. NET Cookbook
ASP.Net 2.0 Cookbook (Cookbooks (OReilly))
ISBN: 0596100647
EAN: 2147483647
Year: 2003
Pages: 202

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net