I've been having trouble getting the SqlCacheDependency to work without polling (which is set up through SqlCacheDependencyAdmin or aspnet_regsql). If you check out the documentation it says things like:
“The SqlCacheDependency class also supports integration with the
System.Data.SqlClient.SqlDependency class when using a SQL Server 2005 database. The query notification mechanism of SQL Server 2005
detects changes to data that invalidate the results of an SQL query and removes any cached items associated with the SQL query from the System.Web.Caching.Cache.”
SQL Server 2005 implements a different model for cache dependency than SQL Server 7.0 and SQL Server 2000. You do not need to go through any
special configuration steps to enable SQL cache dependency on SQL Server 2005. Additionally, SQL Server 2005 implements a change notification model where notifications are sent to subscribing application servers, rather than relying on the polling model required
in earlier versions of SQL Server.
SQL Server 2005 cache dependency is more flexible in the types of changes that receive notification. SQL Server 2005 monitors changes to the
result set of a particular SQL command. If a change occurs in the database that would modify the results set of that command, the dependency causes the cached item to be invalidated. This allows SQL Server 2005 to provide row-level notification.
“SqlCacheDependency, in turn, uses SQL Server 2005 query notifications to receive asynchronous callbacks indicating that data returned by the query
has changed. No polling occurs and no special tables, stored procedures, or triggers are required. If you're looking for an excuse to upgrade your current database to SQL Server 2005, this feature alone is worth the price of admission for data-driven ASP.NET
applications.”
So I got on the phone with Microsoft support to see what I was doing wrong, and their unofficial
answer is that SQL Server 2005 lost the ability to utilize SqlCacheDependency through anything but polling (though they didn't sound especially knowledgeable about what we were talking about like most tier one support).
I'm saying there's no way Microsoft could have such a disconnect between their docs and the release
version of the software, so I'm trying to figure out if anyone got SqlCacheDependency working without polling in SQL Server 2005.
Thanks. That helped establish an important detail in the investigation (though it didn't fully answer the question).
Here's the skinny.
Originally I was trying to set up a table-based dependency using the constructor:
//requires a call SqlCacheDependencyAdmin.EnableTableForNotifications()
SqlCacheDependency myDependency =
new SqlCacheDependency("myDb", "SomeTable");
I assumed this would behave differently for SQL Server 2005 than for earlier versions of SQL Server, but it doesn't. All this does is set up polling for the given table after polling-based dependencies are established for the database (through aspnet_regsql
or SqlCacheDependencyAdmin).
To properly set up query notifications and harness the power of SQL Server 2005, you need to pass a SqlCommand to SqlCacheDependency's constructor:
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT FieldName FROM mySchema.SomeTable";
SqlCacheDependency myDependency = new SqlCacheDependency(cmd);
This, however doesn't work for my tastes as I prefer stored procedures. However, when I tried doing this:
As it turns out, all I needed to do was remove the line SET NOCOUNT ON from my stored procedure and I now have query notification-based cache invalidation using stored procedures.
I want to research what all the ramifications are for removing the line, but it's working.
For more info for permissions & setup (for future readers setting up from scratch):
Were you able to use stored procedures with parameters? I was able to get stored procedures working as long as there were no input parameters. As soon as I introduced 1 parameter, it stopped working.
public static Dictionary<string, string> GetResourcesByCulture(string culturename)
{
if (string.IsNullOrEmpty(culturename))
{
throw new ApplicationException("Invalid culture");
}
SqlConnection m_connection = new SqlConnection(CONNECTION_STRING);
SqlCommand m_cmdGetResourcesByCulture = new SqlCommand();
m_cmdGetResourcesByCulture.CommandText = "usp_GetResources";
m_cmdGetResourcesByCulture.CommandType = CommandType.StoredProcedure;
m_cmdGetResourcesByCulture.Parameters.AddWithValue("cultureCode", culturename);
m_cmdGetResourcesByCulture.Connection = m_connection;
//Create stringcomparer object. If the keys are stored in different case
//we are ignoring the case while accesing them.
StringComparer sComparer = StringComparer.OrdinalIgnoreCase;
// create the dictionary
Dictionary<string, string> resourceDictionary = new Dictionary<string, string>(sComparer);
// open a connection to gather resource and create the dictionary
try
{
m_cmdGetResourcesByCulture.Notification = null;
SqlCacheDependency _dependency = new SqlCacheDependency(m_cmdGetResourcesByCulture);
m_connection.Open();
// get resources from the database and insert into cache with dependency
using (SqlDataReader reader = m_cmdGetResourcesByCulture.ExecuteReader())
{
while (reader.Read())
{
string k = reader.GetString(reader.GetOrdinal("resourceKey"));
string v = reader.GetString(reader.GetOrdinal("resourceValue"));
resourceDictionary.Add(k, v);
}
}
HttpRuntime.Cache.Insert(culturename, resourceDictionary, _dependency);
}
finally
{
m_connection.Close();
}
return resourceDictionary;
}
make sure in stored procedure you use dbo.tablename, DO NOT use "SET COUNT ON" statement
public static Dictionary<string, string> GetResourcesByCulture(string culturename)
{
if (string.IsNullOrEmpty(culturename))
{
throw new ApplicationException("Invalid culture");
}
SqlConnection m_connection = new SqlConnection(CONNECTION_STRING);
SqlCommand m_cmdGetResourcesByCulture = new SqlCommand();
m_cmdGetResourcesByCulture.CommandText = "usp_GetResources";
m_cmdGetResourcesByCulture.CommandType = CommandType.StoredProcedure;
m_cmdGetResourcesByCulture.Parameters.AddWithValue("cultureCode", culturename);
m_cmdGetResourcesByCulture.Connection = m_connection;
//Create stringcomparer object. If the keys are stored in different case
//we are ignoring the case while accesing them.
StringComparer sComparer = StringComparer.OrdinalIgnoreCase;
// create the dictionary
Dictionary<string, string> resourceDictionary = new Dictionary<string, string>(sComparer);
// open a connection to gather resource and create the dictionary
try
{
m_cmdGetResourcesByCulture.Notification = null;
SqlCacheDependency _dependency = new SqlCacheDependency(m_cmdGetResourcesByCulture);
m_connection.Open();
// get resources from the database and insert into cache with dependency
using (SqlDataReader reader = m_cmdGetResourcesByCulture.ExecuteReader())
{
while (reader.Read())
{
string k = reader.GetString(reader.GetOrdinal("resourceKey"));
string v = reader.GetString(reader.GetOrdinal("resourceValue"));
resourceDictionary.Add(k, v);
}
}
HttpRuntime.Cache.Insert(culturename, resourceDictionary, _dependency);
}
finally
{
m_connection.Close();
}
return resourceDictionary;
}
make sure in stored procedure you use dbo.tablename, DO NOT use "SET COUNT ON" statement
did anybody try?
Its all about coding!
--------------------------
Don't forget to click "Mark as Answer" on the post(s) that helped you.
BoulderBum
Contributor
3019 Points
610 Posts
SqlCacheDependency Without Polling
Jul 20, 2006 04:24 PM|LINK
I've been having trouble getting the SqlCacheDependency to work without polling (which is set up through SqlCacheDependencyAdmin or aspnet_regsql). If you check out the documentation it says things like:
So I got on the phone with Microsoft support to see what I was doing wrong, and their unofficial answer is that SQL Server 2005 lost the ability to utilize SqlCacheDependency through anything but polling (though they didn't sound especially knowledgeable about what we were talking about like most tier one support).
I'm saying there's no way Microsoft could have such a disconnect between their docs and the release version of the software, so I'm trying to figure out if anyone got SqlCacheDependency working without polling in SQL Server 2005.
Can anyone say yes or no?
dukebaby
Member
382 Points
94 Posts
Re: SqlCacheDependency Without Polling
Jul 25, 2006 07:45 PM|LINK
They have a video on how to do this in their How do I... vidoes.
How Do I: Make use of Caching?
Watch a demonstration of Page Output Caching followed by an overview of the new Database Caching support for SQL Server 2000 and SQL Server 2005.
http://asp.net/learn/videos/default.aspx?tabid=63
You can get the source code and video off of there.
Video direct link:
http://download.microsoft.com/download/8/3/6/836dd5f8-fa92-499f-8219-0d326f13bf18/hilo_cache1_final.wmv
BoulderBum
Contributor
3019 Points
610 Posts
Re: SqlCacheDependency Without Polling
Jul 27, 2006 06:58 PM|LINK
Thanks. That helped establish an important detail in the investigation (though it didn't fully answer the question).
Here's the skinny.
Originally I was trying to set up a table-based dependency using the constructor:
//requires a call SqlCacheDependencyAdmin.EnableTableForNotifications()
SqlCacheDependency myDependency =
new SqlCacheDependency("myDb", "SomeTable");
I assumed this would behave differently for SQL Server 2005 than for earlier versions of SQL Server, but it doesn't. All this does is set up polling for the given table after polling-based dependencies are established for the database (through aspnet_regsql or SqlCacheDependencyAdmin).
To properly set up query notifications and harness the power of SQL Server 2005, you need to pass a SqlCommand to SqlCacheDependency's constructor:
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT FieldName FROM mySchema.SomeTable";
SqlCacheDependency myDependency = new SqlCacheDependency(cmd);
This, however doesn't work for my tastes as I prefer stored procedures. However, when I tried doing this:
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "mySchema.MyStoredProcedure";
cmd.CommandType = CommandType.StoredProcedure;
SqlCacheDependency myDependency = new SqlCacheDependency(cmd);
ASP.NET would invalidate the cache at the instant it inserted my data.
Eventually, I came across this in my research: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=121836&SiteID=1.
As it turns out, all I needed to do was remove the line SET NOCOUNT ON from my stored procedure and I now have query notification-based cache invalidation using stored procedures.
I want to research what all the ramifications are for removing the line, but it's working.
For more info for permissions & setup (for future readers setting up from scratch):
(See sidebar) http://www.code-magazine.com/article.aspx?quickid=0605061&page=4
Also Google SET ENABLE_BROKER
mhsiung
Member
5 Points
1 Post
Re: SqlCacheDependency Without Polling
Aug 29, 2006 01:38 AM|LINK
Were you able to use stored procedures with parameters? I was able to get stored procedures working as long as there were no input parameters. As soon as I introduced 1 parameter, it stopped working.
nisarkhan
Contributor
2402 Points
1472 Posts
Re: SqlCacheDependency Without Polling
Oct 03, 2007 01:17 PM|LINK
please let me know if you guys able to use sp with parameters.
thanks.
--------------------------
Don't forget to click "Mark as Answer" on the post(s) that helped you.
dmose
Member
68 Points
77 Posts
Re: SqlCacheDependency Without Polling
Oct 07, 2007 05:05 PM|LINK
any updates? anyone get this working with sprocs + parameters?
I can get it working when I use a sproc that takes in no input parameters... but that's pretty much useless
lax4u
Participant
1592 Points
902 Posts
Re: SqlCacheDependency Without Polling
May 23, 2008 06:04 PM|LINK
i'm able to get it work with parameter.
dhina.techno
Member
16 Points
11 Posts
Re: SqlCacheDependency Without Polling
Jul 02, 2008 06:55 AM|LINK
I am facing a similar problem. Can you plz let us know how u got the stored procedure working with parameter?
Regards,
Dhina
lax4u
Participant
1592 Points
902 Posts
Re: SqlCacheDependency Without Polling
Jul 02, 2008 02:29 PM|LINK
public static Dictionary<string, string> GetResourcesByCulture(string culturename) { if (string.IsNullOrEmpty(culturename)) { throw new ApplicationException("Invalid culture"); } SqlConnection m_connection = new SqlConnection(CONNECTION_STRING); SqlCommand m_cmdGetResourcesByCulture = new SqlCommand(); m_cmdGetResourcesByCulture.CommandText = "usp_GetResources"; m_cmdGetResourcesByCulture.CommandType = CommandType.StoredProcedure; m_cmdGetResourcesByCulture.Parameters.AddWithValue("cultureCode", culturename); m_cmdGetResourcesByCulture.Connection = m_connection; //Create stringcomparer object. If the keys are stored in different case //we are ignoring the case while accesing them. StringComparer sComparer = StringComparer.OrdinalIgnoreCase; // create the dictionary Dictionary<string, string> resourceDictionary = new Dictionary<string, string>(sComparer); // open a connection to gather resource and create the dictionary try { m_cmdGetResourcesByCulture.Notification = null; SqlCacheDependency _dependency = new SqlCacheDependency(m_cmdGetResourcesByCulture); m_connection.Open(); // get resources from the database and insert into cache with dependency using (SqlDataReader reader = m_cmdGetResourcesByCulture.ExecuteReader()) { while (reader.Read()) { string k = reader.GetString(reader.GetOrdinal("resourceKey")); string v = reader.GetString(reader.GetOrdinal("resourceValue")); resourceDictionary.Add(k, v); } } HttpRuntime.Cache.Insert(culturename, resourceDictionary, _dependency); } finally { m_connection.Close(); } return resourceDictionary; }make sure in stored procedure you use dbo.tablename, DO NOT use "SET COUNT ON" statement
nisarkhan
Contributor
2402 Points
1472 Posts
Re: SqlCacheDependency Without Polling
Jul 08, 2008 02:42 AM|LINK
did anybody try?
--------------------------
Don't forget to click "Mark as Answer" on the post(s) that helped you.