Creating and Adding a SQL Server 2005 Cache Dependency
Adding a dependency to data stored in SQL Server is similar. The following code uses the AddCacheItemDependency
method to create a dependency on a SqlCommand for a SQL Server 2005 database query, and adds it to the output cache for the page so that the page will be re-executed automatically whenever the data in the source table changes:
' display time page was executed
exectime.Text = "Page executed at " & _
DateTime.Now.ToString() & "<br />"
Dim sSQL As String = "SELECT OrderID, CustomerID, " & _
"OrderDate, " & "Freight, ShipCity FROM Orders WHERE "
& "OrderID < 10251"
Using con As New SqlConnection( _
Dim cmd As New SqlCommand(sSQL, con)
' create a SqlCacheDependency for the database
' defined in Web.config
Dim dep As New SqlCacheDependency(cmd)
grid1.DataSource = _
You've seen the primary techniques available in ASP.NET for caching values and data between user requests, as well as a discussion of the Cache
object and output caching, with particular emphasis on the new features in version 2.0. At this point, you should feel comfortable with:
- Choosing the right approach for caching data
- Using Application, Session, and ViewState data caching
- Global data caching using disk files
- The ASP.NET output caching features
- Using the ASP.NET Cache object directly
Your choice of caching technology depends mainly on the visibility required for the data, but also on the volume of data that you want to cache and the period for which it will be cached. You can store small volumes of data in the page ViewState, and larger volumes in the Session (for a specific user) or the Application (for all users of the application). Alternatively, you can use the ASP.NET Cache object and/or output caching to store large volumes of data and make it available across an application. For global caching where you want to make cached data visible across all applications and servers, however, you must implement something yourself. You saw how the .NET classes make this easy when caching data as disk files or in memory.
You can also consider taking advantage of how the combination of ASP.NET and SQL Server implement the new SQL cache invalidation feature. This feature lets you cache data for long periods without worrying about whether it will become stale. When the data in the data store changes, ASP.NET invalidates the cached data automatically and your code can obtain a fresh and up-to-date copy.
Finally, you've seen how you can create dependencies for all kinds of events, including changes to a file or folder, invalidation after a specific time period or at a specified time, and the invalidation of other cached items (to create dependency chains).
Using these techniques, you can boost performance and reduce server loading by selecting the appropriate caching technology, and implementing it in your applications. While there are no hard and fast rules on when and where you should use caching, and where it provides the greatest benefit, the usual rule of thumb is to consider caching any data that is expensive to create or retrieve, and that your application uses regularly. However, ultimately, you should be prepared to benchmark your applications to see where and how caching can provide optimum gains.