SQL Cache Dependency with SQL Server, LINQ and ASP.NET 3.5
Whenever I think of performance with ASP.NET, the first thing that comes to mind is caching. Simply put caching is an in memory storage of objects that enables the application to read and write data to memory.
ASP.NET has several different varieties of caching features. This article will shine a light on a little known feature of ASP.NET 3.5 called SQL Cache Dependency. SQL Cache Dependency has been around since SQL Server 2000, but back then ASP.NET was limited as far as it had a polling mechanism built in so it would continually poll the database for changes, and when a change was found, it was up to the developer to notify the website that a change had occurred and make the necessary changes in the cache.
With the release of SQL Server 2005 and 2008, ASP.NET has a more mature way to perform SQL Cache Dependency. The developer can now tell SQL Server to push notifications when data has changed. The biggest factor here is that the website does not have to continually poll the database.
In the following example we’ll use SQL Server 2005 and the Pubs database. If you don’t have a copy of the pubs database, you can go here to download it. In order for SQL Server to send notifications, you must first enable Microsoft SQL Server 2005 Service Broker. You can check to see whether the Service Broker is enabled on your server by executing the following SQL syntax:
SELECT name, is_broker_enabled FROM sys.databases
To enable the Service Broker on your database, you must execute the ALTER DATABASE command. The following SQL command will enable the Service Broker service on the Pubs database:
ALTER DATABASE Pubs SET ENABLE_BROKER
GO
Finally you must inform SQL Server that the user running Internet Information Services (IIS) has permission to subscribe to query notifications. Executing the following SQL command will provide the local ASPNET account on a server named TESTSERVER with the required permissions:
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "TESTSERVER\ASPNET"
The previous steps are essential before writing any ASP.NET code.
Now the database is correct, we can go ahead and create an ASP.NET website to utilise SQL Cache Dependency.
Open Visual Studio 2008 and choose File > New > Web > ASP.NET Web Application.
By default, Visual Studio 2008 creates a Default.aspx page when a new Web Application is created. We’ll use this page to query the Pubs database, use the SQL Cache Dependency to cache the data after it has been returned from the database and display that to the user.
Open the page and drag a DropDownBox control onto the page. Rename this control to cboDiscount.
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="cboDiscount" runat="server">
</asp:DropDownList>
</div>
</form>
This example will use LINQ to SQL as the data access layer. Add a new LINQ to SQL Classes file to the project:
Open the LINQ to SQL file in the designer and drag the Discounts table onto the designer.
Dragging this table onto the designer creates a new SQL connection string element in the web.config file that will look similar to the following:
<connectionStrings>
<add name="pubsConnectionString" connectionString="Data Source=dotnet-testbed;Initial Catalog=Pubs;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
For the website to be able to receive notifications, a listener must be created. The best location for this is in the Global.asax file. Add a Global.asax file to the project. Add the following code to enable a listener:
C#
protected void Application_Start(object sender, EventArgs e)
{
SqlDependency.Start(ConfigurationManager.ConnectionStrings["pubsConnectionString"].ConnectionString);
}
The following code also stops the listener:
protected void Application_End(object sender, EventArgs e)
{
SqlDependency.Stop(ConfigurationManager.ConnectionStrings["pubsConnectionString"].ConnectionString);
}
VB.NET
Protected Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
SqlDependency.Start(ConfigurationManager.ConnectionStrings("pubsConnectionString").ConnectionString)
End Sub
The following code also stops the listener:
Protected Sub Application_End(ByVal sender As Object, ByVal e As EventArgs)
SqlDependency.Stop(ConfigurationManager.ConnectionStrings("pubsConnectionString").ConnectionString)
End Sub
The next step is to create the code that sets up the cache dependency. I decided to wrap the code up into an Extension Method. Extensions Methods are new to C# 3.0 and VB.NET 9.0. They give the developer the ability to extend existing classes and create methods that can be attached to those classes. Add a new Class file to the application and name it MyExtensions. Extension methods are static methods, so you must mark the class as static. The following code sets up the cache dependency:
C#
public static class MyExtensions
{
public static List<T> LinqCache<T>(this Table<T> query) where T : class
{
string tableName = query.Context.Mapping.GetTable(typeof(T)).TableName;
List<T> result = HttpContext.Current.Cache[tableName] as List<T>;
if (result == null)
{
using (SqlConnection cn = new SqlConnection(query.Context.Connection.ConnectionString))
{
cn.Open();
SqlCommand cmd = new SqlCommand(query.Context.GetCommand(query).CommandText, cn);
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
SqlCacheDependencyAdmin.EnableNotifications(query.Context.Connection.ConnectionString);
if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(query.Context.Connection.ConnectionString).Contains(tableName))
{
SqlCacheDependencyAdmin.EnableTableForNotifications(query.Context.Connection.ConnectionString, tableName);
}
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
cmd.ExecuteNonQuery();
result = query.ToList();
HttpContext.Current.Cache.Insert(tableName, result, dependency);
}
}
return result;
}
}
VB.NET
Public Module MyExtensions
<System.Runtime.CompilerServices.Extension> _
Public Function LinqCache(Of T As Class)(ByVal query As Table(Of T)) As List(Of T)
Dim tableName As String = query.Context.Mapping.GetTable(GetType(T)).TableName
Dim result As List(Of T) = TryCast(HttpContext.Current.Cache(tableName), List(Of T))
If result Is Nothing Then
Using cn As New SqlConnection(query.Context.Connection.ConnectionString)
cn.Open()
Dim cmd As New SqlCommand(query.Context.GetCommand(query).CommandText, cn)
cmd.Notification = Nothing
cmd.NotificationAutoEnlist = True
SqlCacheDependencyAdmin.EnableNotifications(query.Context.Connection.ConnectionString)
If (Not SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(query.Context.Connection.ConnectionString).Contains(tableName)) Then
SqlCacheDependencyAdmin.EnableTableForNotifications(query.Context.Connection.ConnectionString, tableName)
End If
Dim dependency As New SqlCacheDependency(cmd)
cmd.ExecuteNonQuery()
result = query.ToList()
HttpContext.Current.Cache.Insert(tableName, result, dependency)
End Using
End If
Return result
End Function
End Module
This is an Extension Method for the System.Data.Linq.Table<> class. It can be used against any LINQ query. The main code in this method isSqlCacheDependencyAdmin.EnableNotifications. This checks that the database has Service Broker enabled. If it does then it checks if the table is already marked for notifications via SqlCacheDependencyAdmin.GetTablesEnabledForNotifications. If it is not, then executing SqlCacheDependencyAdmin.EnableTableForNotifications enables that table for notifications.
Once this has been executed, a new table named AspNet_SqlCacheTablesForChangeNotification will be created in the Pubs database.
Viewing the table data, you’ll see there is one row that has been added. This informs the Service Broker to watch that table for any modifications.
If you change a value in the table, you’ll see the ASP.NET Cache has been cleared.
Hopefully after reading this you can go ahead and start using this great technology. This will not only dramatically improve the performance on your ASP.net website, but will also solve the problem of ASP.NET distributed cache scenarios. The source code of this article in C# can be downloaded from here.