Can't get SqlCacheDependency working correctly

J

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?
 
J

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.
 
S

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.)
 
C

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
 
S

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.)
 
Joined
Jun 5, 2009
Messages
2
Reaction score
0
Can't get SqlCacheDependency working correctly Reply to Thread

Hi Can anyone please help me .i m getting data in dataset from store procedure and stored it in cache its work fine but dataset not refreshed when storeprocedure result changes.
 
Joined
Dec 21, 2009
Messages
1
Reaction score
0
SQL Cache dependency not working properly

HI Experts i am using sql cache dependency in my application and it is working fine on my local machine but when i port it to the production server the application performs caching for few initial combinations but after that the application slows down tremendiously.
On running cleartemp on the server and on changing the dependent tables the application starts running again but the issue crops up again after sometime. Please note that the database and application are on 2 seperate servers and cleartemp is hit on application server. Can anyone please help!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top