LINQ SQLServer2005 SQLCacheDependency

  • Thread starter Thread starter Adrian
  • Start date Start date
A

Adrian

Hi, I'm trying to add caching to our asp app, but am having problems with the SQLCacheDependancy not invalidating the cache when the table changes.

Using vs2008, sqlserver 2005 developer edition.

So far I've added the following to the application_start event..

System.Data.SqlClient.SqlDependency.Start("connectionstring as the sa user")

and against the db, I did

ALTER DATABASE mydb SET ENABLE_BROKER

I have the following routine to return a cache version of a table called puptext..

Public Shared Function GetPupText() As IQueryable(Of PUPTEXT)
If Cache("puptext") Is Nothing Then
Dim DB As New DBDataContext
Dim pt = From i In DB.PUPTEXT Select i
Dim SCD As New SqlCacheDependency(DB.GetCommand(pt))
Cache.Insert("puptext", pt, SCD)
End If
Return Cache("puptext")
End Function

Firstly, is this how you'd usually cache a linq result?

Are there any other commands you have to issue to get the cache invalidity notification to work?
 
Hello Adrian,

Yes, they are the recommended steps to use SQLCacheDependancy with LINQ. A
community member, Jim, once wrote an article for the topic:
http://www.writebetterbits.com/2008/01/overview-of-sql-server-2005-database_
11.html
He listed some important points (e.g. SQL server 2005 version issues) that
we should pay special attention to when associating LINQ with
SqlCacheDependency.

And the KB article http://support.microsoft.com/kb/555893 describes the
Step-By-Step approach in implementing Query Notifications, though it does
not mention LINQ.

Let me know if you have any other concerns or questions.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Still not getting anywhere.. but I've added the following routine..

Public Shared Function CanRequestNotifications() As Boolean
Dim permission As New
SqlClientPermission(PermissionState.Unrestricted)
Try
permission.Demand()
Return True
Catch ex As Exception
Return False
End Try
End Function

and called it before creating the dependency, and it returns true.

The SQLService isn't running as the local system account..

Can you think of anything else I can try ?

Thanks
Adrian


| Hello Adrian,
|
| Yes, they are the recommended steps to use SQLCacheDependancy with LINQ. A
| community member, Jim, once wrote an article for the topic:
|
http://www.writebetterbits.com/2008/01/overview-of-sql-server-2005-database_
| 11.html
| He listed some important points (e.g. SQL server 2005 version issues) that
| we should pay special attention to when associating LINQ with
| SqlCacheDependency.
|
| And the KB article http://support.microsoft.com/kb/555893 describes the
| Step-By-Step approach in implementing Query Notifications, though it does
| not mention LINQ.
|
| Let me know if you have any other concerns or questions.
|
| Regards,
| Jialiang Ge ([email protected], remove 'online.')
| Microsoft Online Community Support
 
Also tried doing the following (to no avail) in the partial DBdatacontext
class

Private Sub OnCreated()
Dim strSQL As String = "SET ANSI_NULLS ON;" + _
"SET ANSI_PADDING ON;" + _
"SET ANSI_WARNINGS ON;" + _
"SET CONCAT_NULL_YIELDS_NULL ON;" + _
"SET QUOTED_IDENTIFIER ON;" + _
"SET NUMERIC_ROUNDABORT OFF;" + _
"SET ARITHABORT ON"
Me.ExecuteCommand(strSQL)
End Sub



| Still not getting anywhere.. but I've added the following routine..
|
| Public Shared Function CanRequestNotifications() As Boolean
| Dim permission As New
| SqlClientPermission(PermissionState.Unrestricted)
| Try
| permission.Demand()
| Return True
| Catch ex As Exception
| Return False
| End Try
| End Function
|
| and called it before creating the dependency, and it returns true.
|
| The SQLService isn't running as the local system account..
|
| Can you think of anything else I can try ?
|
| Thanks
| Adrian
|
|
| || Hello Adrian,
||
|| Yes, they are the recommended steps to use SQLCacheDependancy with LINQ.
A
|| community member, Jim, once wrote an article for the topic:
||
|
http://www.writebetterbits.com/2008/01/overview-of-sql-server-2005-database_
|| 11.html
|| He listed some important points (e.g. SQL server 2005 version issues)
that
|| we should pay special attention to when associating LINQ with
|| SqlCacheDependency.
||
|| And the KB article http://support.microsoft.com/kb/555893 describes the
|| Step-By-Step approach in implementing Query Notifications, though it does
|| not mention LINQ.
||
|| Let me know if you have any other concerns or questions.
||
|| Regards,
|| Jialiang Ge ([email protected], remove 'online.')
|| Microsoft Online Community Support
|
|
 
Hello Adrian,

Here is a quick note to let you know that I am still researching this
issue. I tested it on my side and failed to get the cache invalidity
notification to work with LINQ, either. I am sorry that I should have
tested Jim's code before I sent my first reply. I am doing further
researches and discussing with ASP.NET product team to see if LINQ is well
supported by SQLCacheDependancy. I will get back to you as soon as
possible. Thank you for your patience.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
 
Hello Adrian,

I am back with some solutions. I will show you the working solutions first,
then I'd explain why our original code does not work.

Solution 1. We need to execute the SqlCommand that we used to create the
dependency. After then, we can use DataContext.Translate to create the
entities. Here is a working sample in C#:

public class PeopleCache
{
static string constr =
ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionStr
ing;
static string cacheKey = "personsCacheKey";

static PeopleCache()
{
SqlDependency.Start(constr);
}

static void OnCacheRemoved(string key, object value,
CacheItemRemovedReason reason)
{
System.Diagnostics.Trace.WriteLine("People Cache Invalidated");
}

static IList<People> GetAndCachePeople()
{
using (SqlConnection con = new SqlConnection(constr))
using (DataClassesDataContext db = new DataClassesDataContext(con))
{
con.Open();
var query = from p in db.Peoples select p;
SqlCommand cmd = (SqlCommand)db.GetCommand(query);
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
IList<People> people;
using (var rdr = cmd.ExecuteReader())
{
people = db.Translate<People>(rdr).ToList();
}
HttpContext.Current.Cache.Add(cacheKey, people, dependency,
DateTime.MaxValue,
TimeSpan.Zero, CacheItemPriority.AboveNormal,
OnCacheRemoved);
return people;
}
}

public IList<People> GetPeople()
{
IList<People> people =
(IList<People>)HttpContext.Current.Cache.Get(cacheKey);
if (people == null)
{
people = GetAndCachePeople();
}
return people;
}
}

Solution 2. Use CallContext.SetData to register a SqlDependency object, and
in its NotificationCallback, we clear the cache.
See: http://dunnry.com/blog/CategoryView,category,LINQ.aspx

One reason for the failure of our original code is that the cmd object
registered to the dependency object and the cmd used for execute are
different objects
SqlCommand cmd1 = db.GetCommand(query) as SqlCommand;
SqlCommand cmd2 = db.GetCommand(query) as SqlCommand;
bool result = cmd1.Equals(cmd2);
This codesnippet returns false, and thus, the it failed to get the cache
invalidity notification to work.

Let me know if you have any other questions.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
 
Hi,

Still not getting anywhere, can you take a look at this code and point out what's wrong.. I still have the dependency.start in the application_start event, so shouldn't need to specify more options on the cache insert.

Public Shared Function GetPupText() As IList(Of PUPTEXT)
If HttpContext.Current.Cache("puptext") Is Nothing Then
Dim ConStr As String = "our connectionstring"

If CanRequestNotifications() Then
Dim Con As New SqlConnection(ConStr)
Dim DB As New DBdc(Con)
Con.Open()
Dim PT = From i In DB.PUPTEXT Select i
Dim Cmd As SqlCommand = DB.GetCommand(PT)
Dim Scd As New SqlCacheDependency(Cmd)
Dim Rdr As SqlDataReader = Cmd.ExecuteReader()
Dim iListPT As IList(Of PUPTEXT) = DB.Translate(Of PUPTEXT)(Rdr).ToList
HttpContext.Current.Cache.Insert("puptext", iListPT, Scd)
Con.Close()
Else
Throw New Exception("Don't have permission to cache")
End If
End If
Return HttpContext.Current.Cache("puptext")
End Function

Public Shared Function CanRequestNotifications() As Boolean
Dim permission As New SqlClientPermission(PermissionState.Unrestricted)
Try
permission.Demand()
Return True
Catch ex As Exception
Return False
End Try
End Function

Thanks
 
Hello Adrian,

The code itself looks correct. Adrian, I'd suggest we start it from a new
project and a new DB. I am going to send you a very detailed step list to
demonstrate how to use SqlCacheDependency with LINQ. You can follow it and
see if it works on your side. Because the newsgroup system does not allow
pictures or rich texts, can I send you the steps through an email? Would
you mind telling me your email by sending one to my mailbox first? My mail
address can be found in my signature.

Thanks,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
 
Back
Top