SqlCacheDependency using ASP.NET 2.0 and SQL Server 2005
by David Hayden ( .NET Developer )
SqlCacheDependency using ASP.NET 2.0 and SQL Server 2005 is a beautiful thing :) Although getting SqlCacheDependency to work with SQL Server 2000 is not rocket science, there are a few extra moving parts that need to be set-up in your web.config and on SQL Server 2000. When using SQL Server 2005, all of that goes away :)
Enable Service Broker
Before SqlCacheDependency will work with SQL Server 2005, you first have to enable Service Broker, which is reponsible for the notification services that let the web cache know a change has been made to the underlying database and that the item in the cache must be removed.
ALTER DATABASE Store SET ENABLE_BROKER;
GO
SqlCacheDependency.Start() in Global.asax
In ASP.NET, you need to run SqlCacheDependency.Start(connectionString) in the Global.asax:
void Application_Start(object sender, EventArgs e)
{
string connectionString = WebConfigurationManager.
ConnectionStrings["Catalog"].ConnectionString;
SqlDependency.Start(connectionString);
}
SqlCacheDependency in ASP.NET 2.0 Example
Now you can just create your SqlCacheDependency as normal in your ASP.NET 2.0 page. Here is a simple example:
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable categories = (DataTable)Cache.Get("Categories");
if (categories == null)
{
categories = GetCategories();
Label1.Text = System.DateTime.Now.ToString();
}
GridView1.DataSource = categories.DefaultView;
GridView1.DataBind();
}
private DataTable GetCategories()
{
string connectionString = WebConfigurationManager.
ConnectionStrings["Catalog"].ConnectionString;
DataTable categories = new DataTable();
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(
"SELECT CategoryID,Code,Title
FROM dbo.Categories", connection);
SqlCacheDependency dependency =
new SqlCacheDependency(command);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataSet dataset = new DataSet();
adapter.Fill(dataset);
categories = dataset.Tables[0];
Cache.Insert("Categories", categories, dependency);
}
return categories;
}
}
Since I am using a normal SELECT statement above, there are a number of rules one needs to follow, such as
- You cannot use SELECT * - use individual fields
- Must use fully qualified name of table, e.g. dbo.Categories
and a whole bunch of other rules outlined here on MSDN.
There are other ways to use SqlCacheDependency as well, such as with Ouput Caching. I can show those at another time.
Source: David Hayden ( .NET Developer )
Filed: ASP.NET 2.0, SQL Server 2005