It has been a while since my last post. I have been mostly preoccupied at work, but have been fortunate enough to be assigned to some really interesting project here at the bank using the latest Microsoft technologies. One of my project even made it into the .NET 3.0 case study, unfortunately my name wasn’t mentioned. But that’s ok cause I had fun working on it ;)
Anyway the purpose of this post is to outline the steps I’ve taken in getting caching going using SQLCacheDependency with SQL Server 2005 for ASP.NET 2.0.
In this scenario, I have a lookup table that Iwould like to cache on the first call, but have that cache item invalidated if the any value in the underlying tables changes.
Update:
I'm assuming that SQL Broker is enabled against the underlying database. To enable SQL Broker, run the following
ALTER DATABASE Store SET ENABLE_BROKER;
GO
1) In your Global.asax, add a reference to System.Data.SqlClient.
2) Call SqlDependency.Start() method in the Application_Start() method (Global.asax), passing in the connection string.
protected void Application_Start(Object sender, EventArgs e)
{
...
string connectionString = WebConfigurationManager.ConnectionStrings["YOUR_DB"].ConnectionString;
SqlDependency.Start(connectionString);
}
3) In my data access code, I instantiate a new SqlCacheDependency object passing a reference to the SqlCommand object. This SqlCacheDependency object is then used in the Cache.Insert() call. (I’ve hightlighted the relevant lines of code below)
Dictionary<string, bool> switches = new Dictionary<string, bool>();
string connectionString = WebConfigurationManager.ConnectionStrings["YOUR_DB"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd =
new SqlCommand("SELECT SwitchName, SwitchValue from dbo.ExternalSystemSwitch", conn);
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
switches.Add(reader.GetString(0), reader.GetBoolean(1));
}
}
Cache myCache = HttpContext.Current.Cache;
myCache.Insert("ExternalSystemSwitch", switches, dependency);
if (conn.State == ConnectionState.Open)
conn.Close();
}
Note: There is a whole bunch of rules to how you should write your select query, but the two important ones are
- Do NOT use Select *, instead use individual fields
- Must use fully qualified table names, eg. dbo.Customer
The complete list of rules can be found on MSDN
here.
Update:I'm assuming that SQL Broker is enabled in the underlying database. If not, you can run the following to enable it.
ALTER DATABASE Store SET ENABLE_BROKER;
GO
