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