ASP.NET Cache - How To Use SQL Notification Services With LINQ
By Roly67
Caching in ASP.NET
Introduction
Many ASP.NET applications or web sites are driven by SQL Server databases such as in scenarios such as content management or data driven clients.
In a standard ASP.NET application, every request to a web page that has a data store (e.g. SQL Server) behind it causes a connection and query to the database. For web applications with a relatively large usage, interacting with the database can be expensive on resources and can ultimately lead to a progressive reduction in overall web application performance as usage increases.
One way of tackling scalability problems of ASP.NET / SQL applications is to use data caching. Data caching stores data in the web service (IIS) process such that subsequent requests for data are serviced from the relatively fast server memory rather than referencing the comparatively slower and more resource intensive SQL Server database.
Data Caching and Stale Data
In establishing data caching in an application, a developer is faced with various options on how set up the cache. One of the most popular methods is to create a cache in memory with a fixed expiry time (e.g. 10 minutes). Whilst a fixed duration works well in some cases, it creates the problem of what happens just after data changes are made in the database.
Using fixed time or sliding duration cache durations means that changes made in the database are not visible in the web application until the cache time elapse has expired. For some applications, this is unacceptable as data must be available immediately.
SQL Server Query Notification Services
Since SQL Server 2005, there is a feature called "Query Notification Services". This little used feature can work in partnership with the ASP.NET 2.0 or higher to deliver the ultimate in caching design:
- Server side caching for performance and scalability
- Dynamic cache invalidation (and refresh) on data change events (e.g. insert, update, delete)
To exploit this technology, there are some things to be aware of and set up.
What You Will Need
To run this sample code, you will need:
- .NET 2.0 or higher
- Visual Studio 2005 or higher / Visual Web Developer
- SQL Server 2005 Express or higher
1) Enable SQL Notifications
ALTER DATABASE yourdatabase SET ENABLE_BROKER; GO
2) Start .NET Cache Management at App Start
//Edit Global.asax
protected void Application_Start(object sender, EventArgs e)
{
SqlDependency.Start(WebConfigurationManager.ConnectionStrings["YOURCONNECTIONSTRING"].ConnectionString);
}
protected void Application_End(object sender, EventArgs e)
{
SqlDependency.Stop(
WebConfigurationManager.ConnectionStrings["YOURCONNECTIONSTRING"].ConnectionString);
}Notes On Extension Class
One of the best ways that I've found of easy and effective ASP.NET caching is by using an extension method.
The extension method below is a simplified and corrected version of other code that I found elsewhere online.
The code below:
- Does not create SQL polling objects (unnecessary in SQL 2005 or higher)
- Is compatible with IIS 7 by using the HttpRuntime cache rather than most other examples that use HttpContext (obsolete and not supported in IIS7)
3) LINQ Extension Class
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Caching;
public static class MyExtensions
{
public static List<T> LinqCache<T>(this Table<T> query) where T : class
{
var tableName = query.Context.Mapping.GetTable(typeof (T)).TableName;
var result = HttpRuntime.Cache[tableName] as List<T>;
if (result == null)
{
using (var cn = new SqlConnection(query.Context.Connection.ConnectionString))
{
cn.Open();
using (var cmd = new SqlCommand(query.Context.GetCommand(query).CommandText, cn))
{
using (var dependency = new SqlCacheDependency(cmd))
{
cmd.ExecuteNonQuery();
result = query.ToList();
HttpRuntime.Cache.Insert(tableName, result, dependency);
}
cn.Close();
}
}
}
return result;
}
}4) Typical Usage
using (var context = new YourDatbaseDataContext())
{
var results = context.Widgets.LinqCache();
//results is cached and dynamically refreshed on any data changes
}Conclusion
SQL Notification Services are a great way of delivering an effective cache management strategy to ASP.NET applications that use SQL Server as a back-end database.
There are some caveats to using this type of caching such as you can't use views or stored procs to trigger notifications. Only tables are supported. Futhermore, tables with computed columns or those that contain binary (BLOB) fields such as text or image cannot be used with notification services.
Other than the limitations on the types of database objects that can be used, SQL Notification Services are the best way to deliver ASP.NET cache based applications.
krishna rao 5 weeks ago
it is help full me, very good solution