Can't get SqlCacheDependency working correctly

[Joel Barsotti]

I'm trying to get sql cache dependency working correctly.

I've got sql server 2005 for my back end.

I've don the "ALTER DATABASE dbName SET broker_enable" command.

I made sure the user in my connection string has subscripe notifications
permission set to grant.

But now it seems that the cache is kicking my product out of the cache
immediately.

I'm using Cache.Insert(prodName, productObject, prodDepenancy) to put
stuff into the cache, and when I go to refresh the page Cache[prodName] has
reverted to null.

Any ideas?

 

[Jeffrey Tan[MSFT]]

Hi Joel,

Currently we are looking for some people to help you on this issue, we will
update you ASAP. Thanks for your understanding

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Steven Cheng[MSFT]]

Hi Joel,

Welcome to the MSDN newsgroup.

As for the SQL Cache dependency in ASP.NET 2.0, we need to make sure the
configuration in the following places are correct:

1. In sqlserver, we need to make sure we've enabled the sql cache through
aspnet_regsql.exe tool

2. In ASP.NET web application, ensure that web.config file contains the
correct setting for that certain sql cache database/table

If the above are all correctly configured, there may have something else
cause the problem. And for general throubleshooting, you can try the
following means also:

a.Use Sql Profiler to monitor the SQL Server database server to see whether
there're sql query operations peformed by asp.net sql cache thread...

b.Add a RemoveCallBack handler for the cached item to see whether it is
called immediately after you add the cache item.


In addition, here is the walkthrough article in MSDN on using sql cache
dependency in asp.net 2.0, you can also try testing through this to see
whether it can work:

#Walkthrough: Using ASP.NET Output Caching with SQL Server
http://msdn2.microsoft.com/en-us/library(d=robot)/e3w8402y.aspx

Hope this helps.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[chris]

Steven,

It was my understanding that you step 1 and 2 were only needed if you
were hooking to a Sql 2000 server and Sql 2005. In order to get mine
working all I needed to do was have a user with the right permissions
() and then make sure he was hooked to a custom schema and not the dbo
schema. Also, I am sure you probably have this, otherwise it would be
giving you an error, but make sure to call SqlDependency.Start.

void Application_Start(object sender, EventArgs e)
{


SqlDependency.Start(WebConfigurationManager.ConnectionStrings["MainDB"].ConnectionString);
}

Here is my code that works for me. Note that I have my application
abstracted to multiple layers.
Data Layer:
public DataReader GetLists(Guid ownerid, ref SqlCacheDependency
scd)
{
//Note that _Command and _Connection are already declared as
new SqlCommand and SqlConnection objects respectively by this point.
_Command.Parameters.Add(new SqlParameter("@OwnerID",
SqlDbType.UniqueIdentifier, 0));
_Command.Parameters["@OwnerID"].Value = ownerid;

spname = "[dbo].[usp_ListGetList]";
_Command.Connection = _Connection;
_Command.CommandType = CommandType.StoredProcedure;
_Command.CommandText = spname;

scd = new SqlCacheDependency(_Command);


return new DataReader(_Command.ExecuteReader());

}


Here is my Business Layer
public Dictionary<string, string> LoadDictionary()
{
Dictionary<string, string> dict = new Dictionary<string,
string>();

Cache cache = HttpRuntime.Cache;
string cacheKey = "List::" + _OwnerID.ToString();
if (cache[cacheKey] == null)
{
dict.Add("", "Choose One...");

//Note that SqlBuilder is a custom wrapper class that
contains all db calls like the one shown above
using (SqlBuilder builder = new SqlBuilder())
{
SqlCacheDependency scd = null;
using (DataReader reader =
builder.GetLists(_OwnerID, ref scd))
{
while (reader.Read())
{

dict.Add(reader.GetGuid("ItemListID").ToString(),
reader.GetString("Description"));
}
}
//insert this into cache with a sql cache
dependency.
cache.Insert(cacheKey, dict, scd);
}

}
else
dict = cache[cacheKey] as Dictionary<string, string>;

return dict;
}

This took me a little while to get working, but I was having
permissions issues and not the issue you were having. But anyway I
hope this help because once you get this working it is very slick.

Chris

 

[Joel Barsotti]

Any ideas yet?

 

[Steven Cheng[MSFT]]

Hi Joel,

Have you also viewed Chris and mine response in this thread?

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)