SQL Cache Dependency with SQL Server, LINQ and ASP.NET 3.5

Posted by: Malcolm Sheridan , on 1/27/2009, in Category ASP.NET
Views: 757742
Abstract: The following article demonstrates how to use SQL Cache Dependency with SQL Server 2005/2008, ASP.NET 3.5 and Extension Methods.
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.
 
Project Dialog
 
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:
 
LINQ Dialog
 
Open the LINQ to SQL file in the designer and drag the Discounts table onto the designer.
 
SQL 2
 
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. 
 
SQL
 
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.
  
SQL 3
 
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.
 
If you liked the article,  Subscribe to the RSS Feed or Subscribe Via Email 

Malcolm Sheridan is an independent contractor who has been working with Microsoft technologies since VB4. Malcolm has worked with .NET since its inception and thoroughly enjoys ASP.NET. 

 

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

What Others Are Reading!
The History of ASP.NET – Part III (Covers ASP.NET Core)

The History of ASP.NET – Part II (Covers ASP.NET MVC)

The History of ASP.NET – Part I

ASP.NET Web API, RestSharp and Model Error Messages

Broadcast Real-Time Notifications using SignalR, KnockoutJS and SqlTableDependency

ASP.NET Web API: Passing Multiple Objects as an Input Parameters to Action Method

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Malcolm Sheridan is a Microsoft awarded MVP in ASP.NET, a Telerik Insider and a regular presenter at conferences and user groups throughout Australia and New Zealand. Being an ASP.NET guy, his focus is on web technologies and has been for the past 10 years. He loves working with ASP.NET MVC these days and also loves getting his hands dirty with jQuery and JavaScript. He also writes technical articles on ASP.NET for SitePoint and other various websites. Follow him on twitter @malcolmsheridan


Page copy protected against web site content infringement by Copyscape



Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Slott on Tuesday, January 27, 2009 7:30 AM
sweet and simple!
Comment posted by frankaholic on Tuesday, January 27, 2009 12:34 PM
Will this work on SQL 2008?
Comment posted by Malcolm Sheridan on Wednesday, January 28, 2009 9:45 PM
@frankaholic
It certianly will work with SQL 2008.
Comment posted by Kezzer on Thursday, January 29, 2009 10:09 PM
Thank you so much for showing a LINQ example.
Comment posted by Malcolm Sheridan on Friday, January 30, 2009 10:39 PM
@Kezzer
No problem.  I think LINQ to SQL is not dead and will live on for a while.
Comment posted by Avira on Monday, February 2, 2009 10:57 AM
Is this solution scalable? I have an application which is to be accessed by around 1000 users simultaneously?
Comment posted by Malcolm Sheridan on Monday, February 2, 2009 7:49 PM
@Avira
It is scalable.  The number one problem with ASP.NET Cache is that its stored on the web server.  If you have a web farm, each web server has its own copy of the cache, so data can get out of sync.  By utilizing SQL Server to verify the cache, this eliminates this problem.
Comment posted by Monika on Tuesday, February 3, 2009 1:03 AM
I have SQL server and IIS on two different machines. I am able to execure grant for ASPNET account on SQL server but not for ASPNET account on my development machine having IIS.I get error when doing
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "MyMachineName\ASPNET"
Error Text: Cannot find the user 'MyMachineName\ASPNET', because it does not exist or you do not have permission

Please help.
Comment posted by Monika on Tuesday, February 3, 2009 2:11 AM
I have SQL server and IIS on two different machines. I am able to execure grant for ASPNET account on SQL server but not for ASPNET account on my development machine having IIS.I get error when doing
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "MyMachineName\ASPNET"
Error Text: Cannot find the user 'MyMachineName\ASPNET', because it does not exist or you do not have permission

Please help.
Comment posted by Malcolm Sheridan on Tuesday, February 3, 2009 5:25 AM
@Monika
The user running IIS would need to be a domain account that both the web server and the SQL server could authenticate against.  Create a domain account and set that user to run IIS.  That will solve your issue.
Comment posted by Avira on Wednesday, February 4, 2009 7:58 AM
Thank You for reply. Can you suggest article on webfarm and asp.net.
Comment posted by Malcolm Sheridan on Wednesday, February 4, 2009 6:32 PM
@Avira
I don't know of any specific articles on setting ASP.NET and web farms.  The things to remember with web farms is to use load balancing and to set the ASP.NET session to be out of process.  So either be stored in SQL Server or the State Service.  

Here is an article on ASP.NET session state:http://blogs.msdn.com/tims/archive/2003/11/21/57453.aspx

Comment posted by ehsan on Thursday, March 12, 2009 5:43 PM
Wow.  I didn't even know this was possible!  Thanks for the great article.  Very clear and easy to understand.
Comment posted by DickB on Thursday, March 12, 2009 6:38 PM
Brilliant!! I love asp.net SqlCacheDependency, but feared I had to give up on it when using Linq-to-SQL.
I have yet to try out your solution, but I'm really excited already!
Comment posted by ICSharp on Friday, March 13, 2009 5:37 AM
Looks alot like an article posted here
http://code.msdn.microsoft.com/linqtosqlcache
a year ago... But I´m not saying that you´ve seen it though... Since the code in the other article handles parameters in the query... And I dont think that this article covers that or am I wrong?
Anyway.. It´s still a nice article.
Comment posted by ICSharp again :) on Friday, March 13, 2009 5:41 AM
After reading your code a little bit more carefull I take it back... The other article handles LinwQueries and you handles tables.. :) Both are nice! :) Keep it up!
Comment posted by Malcolm Sheridan on Friday, March 13, 2009 6:34 AM
@ICSharp
I'm glad you liked the article.  Thanks for the positive feedback!
Comment posted by Koistya `Navin (www.riaguy.com) on Saturday, March 14, 2009 8:03 AM
There is no need in using SqlCacheDependencyAdmin with SQL Server 2005 and above. Your are combining 2 different solutions into one.
Comment posted by saroj on Friday, March 20, 2009 8:44 AM
please send book .net devlopers
Comment posted by saroj on Friday, March 20, 2009 8:50 AM
please send book .net devlopers
Comment posted by Joe Reynolds on Thursday, April 2, 2009 7:01 PM
I downloaded the code, pubs database, set the database per instructions, and then opened the default.aspx page. The dropdown is blank. The change for notification table is created.

Any ideas on why the dropdown has no items?

I see the codebehind for default call for the data from LinqCache. I'm wondering if this requires a non-cache call fist to load the cache, or is this taken care of in MyExtensions?

Anyway, I'm missing something here, but don't know what it is.
Comment posted by Malcolm Sheridan on Friday, April 3, 2009 7:46 PM
@Joe Reynolds
Without seeing your code running it is hard to say why it is not running.  The best option would be to debug the code and put a breakpoint on the LinqCache extension method and check that it is being executed properly.  
Comment posted by Sam U on Friday, May 8, 2009 10:44 PM
Am I correct to conclude that this technology seems to watch specified table and force a "refresh" when anything in that table changes i.e. an update/delete/insert? Is there a way to monitor data at row level rather than table level? For example, if I have a table that will get updated quite often, this technology would be not be beneficial. Am I interpreting this wrong?
Comment posted by Zak on Wednesday, May 13, 2009 8:42 AM
Isn't SqlCacheDependencyAdmin.EnableTableForNotifications used to set up pooling infrastructure only?
If you set up a trace you will see that dbo.AspNet_SqlCachePollingStoredProcedure is still being called. This is NOT a notification as I uderstand it.
Comment posted by Malcolm Sheridan on Tuesday, May 19, 2009 12:05 AM
@Sam U
Yes it monitors the table for any changes.  Unfortunately you can't watch the data at the row level.  It only watches the table for changes.
Comment posted by Marcel Wijnands on Wednesday, May 27, 2009 7:52 PM
@Malcolm
You are wrong about that. With Sql Server 2005 Notification-based Cache Invalidation you can detect changes to results of queries. Read up about it here: http://quickstarts.asp.net/QuickStartv20/aspnet/doc/caching/SQLInvalidation.aspx

Also like Koistya said, you are combining polling and notification based cache invalidaition into this extension which is unneccesary. You will realize that once you read the above article.
Comment posted by Malcolm Sheridan on Wednesday, May 27, 2009 8:04 PM
@Marcel Wijnand
I wasn't aware that you could poll results of queries.  Thanks for the article!

There is no polling of the application in this example.  As for SqlCacheDependencyAdmin that is required for this example.  Without it, the AspNet_SqlCacheTablesForChangeNotification table does not know which table to monitor for changes.  
Comment posted by bill on Thursday, July 2, 2009 7:11 AM
Sorry - newbie here. How do you create a query with 'order bys' and 'where' clauses. In the example files its getting the whole contents of a table in to a list. what happens if you only want to cache a proportion of that table?
Comment posted by bill on Thursday, July 2, 2009 7:52 AM
Sorry - newbie here. How do you create a query with 'order bys' and 'where' clauses. In the example files its getting the whole contents of a table in to a list. what happens if you only want to cache a proportion of that table?
Comment posted by Will on Thursday, July 2, 2009 9:31 AM
Hi Malcolm - In the myextensions class "public static List<T> LinqCache<T>(this Table<T> query) where T : class" you first look to see if the list exists in cache. If it doesnt then it returns null and gets data from db. All good so far. However at the end of the script you say "return result"; but we know that result is null as there is no cached object, so my list is empty. Also, forwhatever reason each time i refresh my page it still thinks that there is no cached object and goes back to the db to get the data after it has already run through it once. When I run through the script I cant see anything obvious. Sorry I am new to .NET!
Comment posted by Will on Thursday, July 2, 2009 9:35 AM
BTW I am using Windows 2008 and SQL 2008 - there is no ASPNET user, could this be an issue?
Comment posted by Will on Thursday, July 2, 2009 9:49 AM
Found out that it was an error in the downloaded files - the downloaded files do not match what is copied and pasted above. In the copy above there is: result = query.ToList(); HttpContext.Current.Cache.Insert(tableName, result, dependency); where as in the downloaded files it is set as HttpContext.Current.Cache.Insert(tableName, query.ToList(), dependency) with out resetting the result. However, when running my web app it goes back to the database everytime and doesnt seem to store my result in cache. Not sure why its not storing it, when debugging it goes through the code fine. Do I need to run it from IIS for caching to work rather than localhost?
Comment posted by dom on Tuesday, July 7, 2009 6:12 PM
Great article. However if you need to deploy the web application in a DMZ ie. seperate domain, how would you run GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "TESTSERVER\ASPNET"? OR
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO domainAcc. As I will be connecting with user name password will it be;

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sqluserName?
Comment posted by Suman on Thursday, July 9, 2009 3:31 AM
Have you tried using this feature when our DAL is running on a different process other than our web app. I tried using this in my DAL which is used from my BAL from my WCF Service layer which runs in a different process on a different server(I had to make the aspNetCompatibilityEnabled true for this to get access to the HttpContext object). In that case starting the sqldependency from the application start event didnt help. In such a scenario at which event/at what point of time in which layer do u start the sqldependency? Any suggestions?
Comment posted by Malcolm Sheridan on Tuesday, July 14, 2009 3:16 AM
@will
I never tried it locally as I was working from my development SQL server.  You should try it and let me know.
Comment posted by Grimur on Wednesday, July 29, 2009 11:36 AM
Hi, I've run into a bit of a problem here. My problem is that my data never refreshes and stays cached.

The table dbo.AspNet_SqlCacheTablesForChangeNotification (lets call it notiTable) is indeed created and a row added for the table I'm testing (lets call it tableA) and when I add a row to tableA the changeId column of notiTable is changed.

This is how it is supposed to work I would imagine, but my webapp never realizes that the data has been updated and doesn't clear the cache.

Any ideas?
Comment posted by Malcolm Sheridan on Sunday, August 2, 2009 6:25 PM
@Grimur
I ran into that issue and it is because port 80 was blocked on my SQL server.  As soon as I opened that, the notifications were being sent.  Try that.
Comment posted by Fuck on Friday, August 14, 2009 7:22 AM
Article is good
Comment posted by VISHNU on Thursday, October 8, 2009 3:07 AM
Excellent article
Comment posted by DickB on Monday, October 12, 2009 4:47 AM
Having taken a closer look at this, I have to agree with some of the comments posted earlier.
Indeed two different caching techniques are mixed together:

1. ASP.NET Polling-based cache invalidation: tables have a trigger that fires upon change and modify an entry in the table "AspNet_SqlCacheTablesForChangeNotification".
The ASP.NET polling infrastructure polls this table and invalidates SqlCacheDependency instances, that are instantiated with parameters "databaseEntryName", "tableName", accordingly (I would have expected your code to use this overload). Data is watched at "table"-level

2. ASP.NET notification-based cache invalidation: sql broker notifies if a certain query is no longer valid. This mechanism goes together with the other SqlCacheDependency overload that takes a SqlCommand-object. In this approach, data is watched at "table"-level

I'm currently using a slightly modified copy of the example posted under: http://code.msdn.microsoft.com/linqtosqlcache (also mentioned a little higher up in this thread).
But also in this example, there's a slight mix-up of the 2 technologies. (I've disabled steps 5.1 - 5.3)

The challenge now is to make Linq-to-SQL generate queries that are cacheable(no outer joins, no aggregates, no subqueries, etc.) See this article for a detailed overview of what your queries are restricted to in order to use sql notifications: http://aspalliance.com/1541_Understanding_SQL_Server_2005_Notification_Services_with_ASPNET_20.2

Anyway, at the core, you're approach remains an eye opener to me. Thanks again.
Comment posted by DickB on Monday, October 12, 2009 5:09 AM
on 2. I meant: data is watched at "row"-level (as opposed to "table"-level)
Comment posted by Malcolm Sheridan on Friday, November 6, 2009 3:22 PM
@DickB
The SqlCacheDependencyAdmin.GetTablesEnabledForNotifications code checks the AspNet_SqlCacheTablesForChangeNotification table for changes.  That's how I understand it.  I'm glad this article has raised so much interest because this is how I would implement it, but it's interesting to see how other people implement it too.
Comment posted by David on Monday, December 7, 2009 10:30 AM
Great write up and I used this almost line for line in our application.  We have just started to see an issue where it appears that listeners are not being closed.  

We have in the area of 5 developers working on the app and a nightly restore that overwrites our existing database (dev).  Part of that job is to close out any "open" connections with the database.  Occasionally we see connections that are locked with the BROKER_RECEIVE_WAITFOR wait type - which cannot be killed.

Any idea why that would happen?
Comment posted by Malcolm Sheridan on Monday, December 28, 2009 11:40 PM
@David
I haven't run into this issue yet.  A quick google search found this link: http://tinyurl.com/yd5x3jy
Comment posted by Llewellyn Kruger on Wednesday, February 10, 2010 8:59 AM
Thanks for sharing!
Comment posted by darshan thacker on Saturday, February 20, 2010 1:33 AM
i have tried this but its not working for me...please help...
i have enabled broker on sql but - GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "TESTSERVER\ASPNET" is not working with local ip address of server where database is hosted...
Comment posted by darshan thacker on Saturday, February 20, 2010 1:57 AM
The table dbo.AspNet_SqlCacheTablesForChangeNotification (lets call it notiTable) is indeed created and a row added for the table I'm testing (lets call it tableA) and when I add a row to tableA the changeId column of notiTable is changed.

This is how it is supposed to work I would imagine, but my webapp never realizes that the data has been updated and doesn't clear the cache.

how to check for port is blocked or not ? or should i grant subscribe query notification to "sqlservername" / "machine name" / "ip address" ????

please help
Comment posted by emzero on Wednesday, April 7, 2010 3:19 PM
Great article! But I've just registered to download the source code and I didn't get any email with the zip password...
Comment posted by emzero on Wednesday, April 7, 2010 3:46 PM
Nevermind, I've just got the email, sorry!
Comment posted by Malcolm Sheridan on Thursday, April 15, 2010 7:29 AM
@darshan
Unfortunately there's no easy way I know of to check this.  When this fails, it fails silently.
Comment posted by Praveen Menon on Thursday, May 13, 2010 10:47 AM
Excellent article. Thank you for sharing it.

Can you let me know if multiple caches can be updated using this technique. For e.g.  
I have 2 applications App1 and App2 hosted on separate servers each having their own cache C1 and C2. I have a table T1 which is accessed by both the applications.

C1 and C2 both have dependency defined on the table T1. Now if A1 fires an update on the table T1, I am sure that C1 will be updated. But will C2 also be updated?

It would be great if you could clarify my doubt.

Thanks in advance
Comment posted by Santanu Datta on Sunday, June 13, 2010 3:41 AM
how to add this without using LINQ
Example :
i have a class called Categories and using Northwind database and also used it in SqlCacheDependency
Comment posted by Owain on Wednesday, July 28, 2010 2:59 PM
I have this all working but I would like the cache to persist for different requests and because it is added to the HttpContext.Current.Cache that is not happening.  For example if I cached a products table this does not appear to use the same cache for two separate users with requests to the same data.

Thanks
Comment posted by Greg Tyndall on Monday, August 9, 2010 3:10 PM
Modified to work with EF 4.0.  If anyone knows of a different way to get the table name in EF 4.0 I would appreciate you letting me know.  I also made a change to the cache key to use the table name and the command text.  The changes I made were for use in a WPF thick client so note that I'm using the httpruntime for caching instead of the current http context.

public static IEnumerable<T> Cached<T>(this ObjectQuery<T> query)
        {
            string queryString = query.ToString();
            string tableName = queryString.Substring(queryString.LastIndexOf('.') + 1).TrimEnd(']');
            string key = "ObjectQuery_" + tableName + query.ToTraceString();

            IEnumerable<T> result = HttpRuntime.Cache[key] as IEnumerable<T>;

            if (result == null)
            {
                string connectionString = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
                using (SqlConnection cn = new SqlConnection(connectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query.ToTraceString(), cn);
                    cmd.Notification = null;
                    cmd.NotificationAutoEnlist = true;
                    SqlCacheDependencyAdmin.EnableNotifications(connectionString);

                    if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString).Contains(tableName))
                        SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, tableName);
                  
                    SqlCacheDependency dependency = new SqlCacheDependency(cmd);
                    cmd.ExecuteNonQuery();
                    result = query.ToList();
                    HttpRuntime.Cache.Insert(key, result, dependency);
                }
            }
            return result;
        }
Comment posted by praveen on Tuesday, September 21, 2010 10:15 AM
Hi,
how do i add this sqlcachedependency to output cache? i do not wish to do a cache.insert, instead attach this dependency to a output cache. Is that possible?

Thanks
Comment posted by praveen on Tuesday, September 21, 2010 10:16 AM
Hi,
how do i add this sqlcachedependency to output cache? i do not wish to do a cache.insert, instead attach this dependency to a output cache. Is that possible?

Thanks
Comment posted by Valeriano on Thursday, December 23, 2010 5:56 AM
Hi,
i've downloaded the source, but testing it on my machine (VS2010 - ASP.NET 3.5), the HttpContext.Current.Cache[tableName] as List<T>
is always null.

why?
thanks
Comment posted by Malcolm Sheridan on Sunday, December 26, 2010 10:24 PM
@Valeriano
more than likely the service account doesn't have permission.
Comment posted by jimmy on Thursday, February 3, 2011 12:23 AM
nice post. but i think it ll not be scalable as the asp.net cache is an in memory cache and in case the data is distributed over multiple servers, it may ends up with some scalability and performance issues. so you better check a distributed cache. here some a few free distributed caching solutions.

NCache: http://www.alachisoft.com/ncache/index.html
Appfabric: http://www.microsoft.com/windowsazure/appfabric/overview/default.aspx  
Comment posted by yosef on Monday, July 18, 2011 3:13 AM
Hi Malcolm,

I like this post very much. I have tested it with VS2010 and SQL Server 2008 and it works. However, in order to refresh the combo box, I have to manual refresh the browser (F5) or using ajax to refresh the combo box. Is it true or is it supposed to be refresh by itself?
Comment posted by yosef on Monday, July 18, 2011 3:17 AM
Hi Malcolm,

I like this post very much. I have tested it with VS2010 and SQL Server 2008 and it works. However, in order to refresh the combo box, I have to manual refresh the browser (F5) or using ajax to refresh the combo box. Is it true or is it supposed to be refresh by itself?
Comment posted by Mike W on Thursday, August 4, 2011 2:15 PM
This is completely confusing: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "TESTSERVER\ASPNET"
Comment posted by Sean B on Thursday, August 4, 2011 3:49 PM
If you are having setup issues here is what I had to deal with for this setup.  I am using SQL Server 2008 Express, VS 2010.  First thing that I had to fix was the code in the MyExtensions the query.Context.Connection.ConnectionString does not seem to pass through the password for some reason.  I had to just change the code and created a "string conn = ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString;" and replaced all those with my conn string.  That will get past any  "Unable to connect to the SQL database for cache dependency registration." errors, I am using the Adventure Works database instead of pubs for 2008.  The next issue was the creation of the trigger I was getting errors:  "Cannot create trigger ‘dbo.” error when trying to enable a SQL cache dependency on a table".  In order to fix this the issue is the schema name and you can download the SQL file to alter a procedure from http://chrisbenard.net/2007/01/03/enabling-sql-cache-dependency-with-schema-solution/.   Hopefully this helps anyone having the same issues.
Comment posted by Sean B on Thursday, August 4, 2011 3:51 PM
yosef yes it is on refresh.  In ASP.Net of course it would be on any postback or where you are actually hitting your load of the datasource.  That is the beauty behind this sample here and the latest SQL Caching is you don't have to poll or refresh on an interval.
Comment posted by Sean B on Thursday, August 4, 2011 4:16 PM
can anyone also provide a sample on how we can do this using stored procedures vs a table and add that to the MyExtensions?  Most cases I would use stored procs with joins of course so this is simplified by table but what about joins and stored procedures?
Comment posted by Joey on Monday, October 17, 2011 9:44 AM
Just subscribed to the newsletter but i didn't get a password... Why ???
Comment posted by Joey on Monday, October 17, 2011 9:45 AM
Just subscribed to the newsletter but i didn't get a password... Why ???
Comment posted by Carol Nadarwalla on Tuesday, October 18, 2011 1:09 PM
Looks like we missed out removing the password on this one. Apologies for the goof-up. The password is dotnetcurry123#
Comment posted by Leszek on Tuesday, December 20, 2011 3:41 PM
Thanks Malcolm for the great post!

It works well in my environment except the following line:
SqlCommand cmd = new SqlCommand(query.Context.GetCommand(query).CommandText, cn);

It happens that query.Context.GetCommand(query).CommandText returns a query with table names that are not fully-qualified. As stated here (http://msdn.microsoft.com/en-us/library/h91hx359.aspx) the table names have to be fully-qualified in order for SqlDependency to work properly. I've modified the code and provided a correct query as a parameter to LinqCache. It looks ugly but at least it works. Otherwise HttpContext.Current.Cache always returns null.
Comment posted by Leszek on Tuesday, December 20, 2011 5:40 PM
Thanks Malcolm for the great post!

It works well in my environment except the following line:
SqlCommand cmd = new SqlCommand(query.Context.GetCommand(query).CommandText, cn);

It happens that query.Context.GetCommand(query).CommandText returns a query with table names that are not fully-qualified. As stated here (http://msdn.microsoft.com/en-us/library/h91hx359.aspx) the table names have to be fully-qualified in order for SqlDependency to work properly. I've modified the code and provided a correct query as a parameter to LinqCache. It looks ugly but at least it works. Otherwise HttpContext.Current.Cache always returns null.
Comment posted by Malcolm Sheridan on Tuesday, December 27, 2011 4:23 AM
@Leszek

Glad you enjoyed it.  Thanks for the code update.
Comment posted by Matt Penman on Friday, February 10, 2012 4:44 PM
The AspNet_SqlCacheTablesForChangeNotification show that the table was changed but the cache is not being updated... any ideas?
Comment posted by URVISH SUTHAR on Monday, February 13, 2012 6:00 AM
good example for SQL Cache Dependency with SQL Server, Thanks
Comment posted by Matt Penman on Wednesday, February 29, 2012 2:29 PM
Well, I've done everything according to the instructions... no go. It doesn't update. I guess this post is too old and is being ignored or neglected.
Comment posted by Malcolm Sheridan on Tuesday, March 6, 2012 4:58 AM
@Matt

From memory the issue is the ASPNET account hasn't been set-up correctly to be notified of query notifications.
Comment posted by barua99 on Friday, May 4, 2012 7:54 AM
Respected sir,
when i using GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "SUSANTA-PC\ASPNET" then i get Cannot find the user 'SUSANTA-PC\ASPNET', because it does not exist or you do not have permission. then i going to create user SUSANTA-PC\ASPNET it showing like SUSANTA-PC\ASPNET at create new user in sql server 2008( Not sql server 2008 express). please reply.
Comment posted by Malcolm Sheridan on Sunday, July 22, 2012 7:03 AM
@barua99
Its telling you it can't find the user.  I can't tell you anymore than that!
Comment posted by xc on Monday, May 13, 2013 6:48 AM
f
Comment posted by again on Friday, August 2, 2013 10:06 AM
can anyone also provide a sample on how we can do this using stored procedures vs a table and add that to the MyExtensions?  Most cases I would use stored procs with joins of course so this is simplified by table but what about joins and stored procedures?
Comment posted by Marcelo Jury on Friday, August 2, 2013 10:09 AM
Just to say it is not working with stored procedures, it works fine with tables, but how do I enable triggering for a change in a related table which is being used in an stored procedure???  thanks
Comment posted by Raju Prasad on Friday, August 9, 2013 1:00 PM
An exception is throwing, Unable to cast object of type 'System.Int32' to type 'System.String' at query.ToList(). Please tell me the solution.
Comment posted by Raju Prasad on Friday, August 9, 2013 1:00 PM
An exception is throwing, Unable to cast object of type 'System.Int32' to type 'System.String' at query.ToList(). Please tell me the solution.
Comment posted by Erick on Monday, October 6, 2014 3:01 AM
It looked good until you introduced you extension method.  It makes it difficult to lean a new topic if you need to learn another new topic (extensions methods) to do that.  You didn't really go into what this methods do and I can see where in your code they would be called.