Cache items can be depenedent on sql queries as well, It requires following few steps, as mentioned below.
1. Enable cache notification - Make sure your database has the ENABLE_BROKER flag set. Assuming we’re using the Northwind database:
Use Northwind
ALTER DATABASE Northwind SET ENABLE_BROKER
2. Create the cache dependency:
string query ="SELECT EmployeeID, FirstName, LastName, City FROM dbo.Employees";
SqlCommand cmd = new SqlCommand(query, strConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
// Fill the DataSet.
DataSet ds = new DataSet();
adapter.Fill(ds, "Employees");
// Create the dependency.
SqlCacheDependency empDependency = new SqlCacheDependency(cmd);
// Add a cache item that will be invalidated if one of its records changesor a new record is added.
Cache.Insert("Employees", ds, empDependency);
3. Call the static SqlDependency.Start() to initialize the listening service on the web server. This needs to be performed only once for each database connection. One place to call it is the Application_Start() method of the global.asax file.
4. Finally, call SqlDependency.Stop() to detach the listener. Typically, this is called under Application_End() method.
| Caching FAQS | ASP.NET Caching Article |
