How to refresh ASP.NET Cache when data is updated in database?

Question

I am currently leveraging the code below to refresh a cache at midnight every day. It works great, but the requirements are going to change such that I need to update the cache when an item in the dataset changes. I understand that there is a CacheDependency class for checking to see if a file has changed in order to refresh the cache - but I don't see how that applies to what I am trying to do. Can someone help steer me in the right direction for updating 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've heared and read about SqlCacheDependency class, haven't you? Well if you haven't I suggest you do. It will bring a smile on your face. ;)

MSDN article.

DotNet 1.x approach

In the old days (pre .net 2.0 and pre SQL 2005) there was also a trick with usual CacheDependency class in a way so that you created a trigger for inserts/updates on the table that needed it that barely (re)saved an empty file (so it was as fast as possible) in some folder and you depended your cache on that particular file hence getting your cache invalidated when data changed. As I heard it worked fine.

MSDN Magazine article from 2003 about it.