How to make OutputCache with SqlDependency dependent on a row in database for each request?

To avoid hitting the database for each webpage request (generally an expensive operation), Object Caching (introduced in .NET 4.0) can be used. This would result in fast serving of webpages because everything would be handled directly from memory. Database operations would occur only if the data actually changed, or if the cache was evicted from memory due to resource constraints or your CacheItemPolicy settings.

The practical strategy used in this case would be as follows.

Step-1. In your Model method where data for the "id" row is being modified/added/deleted, complete that database operation and then:

• If your object already exists in the cache, evict it;
• Create a new cache entry for the object.

Step-2. Retrieve the object from cache whenever possible, refreshing from the db only if necessary:

• From your Controller action method, call a Model method that returns the object identified by the "id" parameter;
• Within your Model method, check the cache for this id. If it is null, retrieve the data from your database and build the object as you would normally do, and then store the complete object in the cache;
• From the Model method, return the content of the cache (i.e. your specific object for this id) back to the calling Controller action method, and then let the action method populate and serve the View as usual.

(The MemoryCache class is the concrete implementation of the ObjectCache class).

With this approach, the OutputCache on the Controller method would not need to be used at all, and the data caching decisions would all be fully encapsulated within the Model. We would get a cleaner separation of concerns; much higher efficiency, better response times, and improved scalability; and a reduced dependency on costly database operations.

[OutputCache (Duration=int.MaxValue VaryByParam="None" VaryByCustom="SqlRow")]

In your global.asax you have to do following.

Public override string GetVaryByCustomString(HttpContext context, string arg) {   if(arg.ToLower() == "sqlrow")   {      using(SqlConnection conn = new SqlConnection(... ) )     {         conn.Open();         var cmd = conn.CreateCommand();         var id = context.QueryString["id"];         cmd.CommandText = "SELECT LastModifiedTime FROM Table WHERE ID = @id";         cmd.Parameters.Add( "id", id );         return cmd.ExecuteScalar();     }  }   return base.GetVaryByCustomString(context, arg); }