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