Best way to cache stored procedure result in C#

Question

I am running a web site in ASP.NET/C#/SQL Server 2012 that needs to cache the result of some stored procedure queries. The result should have an absolute expiration. What options are there to do this?

Preferably setting command.ExpirationDateTime = DateTime.Now.AddMinutes(10) would be great, but as far as I know nothing like that is possible.

Edit: The data will be returned from an API, so caching using pages or user controls is not possible.

Answer 1

Have a look at the Enterprise Library Caching Application Block. This has the exact functionality you are looking for

The Caching Application Block

    cache.Add(listid.ToString(), list, CacheItemPriority.Normal, null, 
new SlidingTime(TimeSpan.FromMinutes(60)));

Answer 2

I don't understand you restriction on where you can actually perform caching, but I assume you'll have access to HttpRuntime.Cache? If that's the case, I have written a series of utilities for caching service responses in a blog post (Caching Services - The Lazy Way).

The basics of this utility is you can do:

   string cacheKey = GenerateCacheKey(myParam); //most likely a derivative of myParam

   if (Cache.IsInCache<MyResultType>(cacheKey))
   {
      return Cache.GetFromCache<MyResultType>(cacheKey);
   }

   var result = GetMyRequestedResult(myParam);
   if (result != null) //or whatever makes sense
   {
      Cache.InsertIntoCacheAbsoluteExpiration(cacheKey, result, DateTime.Now.AddMinutes(0));
   }

   return result;

If you have any services in between, the post shows a cute class for interacting/caching with those services.