How to update ASP.NET cache when updating data in a database?

Question

I am currently using the code below to update the cache at midnight every day. It works fine, but the requirements will change so that I need to update the cache when the item in the dataset changes. I understand that there is a CacheDependency class to check if a file has changed to update the cache, but I don’t see how this relates to what I'm trying to do. Can someone help me direct me in the right direction to update the cache when the dataset changes? Thanks.

try { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["localDb"].ConnectionString); DataSet ds = new DataSet(); if (Cache["ds"] == null) { conn.Open(); SqlCommand sqlComm = new SqlCommand("SELECT EmployeeID, NationalIDNumber, ContactID, LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender FROM HumanResources.Employee", conn); SqlDataAdapter adapter = new SqlDataAdapter(sqlComm); adapter.Fill(ds); Cache.Insert("ds", ds, null, DateTime.Today.AddDays(1), TimeSpan.Zero); conn.Close(); } GridViewEmployees.DataSource = (DataSet)Cache["ds"]; GridViewEmployees.DataBind(); } catch (Exception) { throw; } 

Answer 1

DotNet 2.0+ approach

I suppose you heard and read about the SqlCacheDependency class, right? Well, if you did not offer you to do this. It will bring you a smile.;)

MSDN article .

Dotnet 1.x approach

In the old days (pre.net 2.0 and pre SQL 2005) there was also a trick with the usual CacheDependency class so that you create a trigger for inserts / updates in a table in which it was small (re) to save an empty file (so it was like faster) in some folder, and you depended on your cache on this particular file, therefore, your cache became invalid when the data changed. As I heard, it worked fine.

2003 MSDN journal article about this .