Cache Dependency Problem

Hello.

I am having problems with SQL Cache dependency. I am using SQL 2005, ASP .net 2.0.

Every time i try to load data from cache, this is null. It acts like someone is constantly changing everything in the db.

Because of this my website makes hundreds of connections to the db instead of 5. This is a major issue that i cannot figure it out. PLEASE ADVISE.

On my local machine everything seems to work just fine. On the testing server the cache is always null.

Is cache dependency related

- to the platform used?

- to the number of IIS servers connected to the DB?

- to the sql user used?

Also a strange thing happens. When i change something in web.config the cache is working for about 1 min and after that it stops.

Here is the code i wrote:

public ManufacturerList GetAllManufacturers()
{
if (HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] != null)
return HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] as ManufacturerList;

SqlCacheDependency dep = new SqlCacheDependency(ConstantsManager.Instance.GetDefaultAsString("DB_NAME"), ConstantsManager.Instance.GetDefaultAsString("TABLE_MANUFACTURERS"));

_manufacturerList = new ManufacturerList();

DataReader reader = SqlHelper.ExecuteDataReader(WebContext.ConnectionString, CommandType.StoredProcedure, "dvx_web_MANUFACTURER_LoadAll", null);

while (reader.Read())
{
int manufacturerID = reader.GetInt("ID");

Manufacturer findManufacturer = _manufacturerList.FindByID(manufacturerID);

if (findManufacturer == null)
{
findManufacturer = new Manufacturer();
findManufacturer.LoadFromDataReader(reader);
_manufacturerList.Add(findManufacturer);
}
}

reader.Close();

HttpContext.Current.Cache.Insert(ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS"), _manufacturerList, dep);

return _manufacturerList;
}

Thanks a lot.

Hi,

Look at this article describing some ways of using SQL Dependency Cache.

http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html