How to cache database tables to prevent many database queries in Asp.net C# mvc

Question

I build my own cms using Asp.net mvc 4 (c#), and I want to cache some database data, likes: localization, search categories (it's long-tail, each category have it's own sub and sub-sub categories), etc..

It's will be overkill to query the database all the time, because it can be more than 30-100 queries for each page request, however the users update those database rarely

So what is the best way (performance and convenience) to do it?

I know how use the OutputCache of the action, but it's not what I need in this situation , it's cache the html, but what I need is for example, that my own helper @html.Localization("Newsletter.Tite") will take the value of the language, or any another helper that interact with data etc.

I think (not really sure) that I need to cache the data I want, only when the application is invoke for the first time, and then work with the cache location, but I don't have any experience even about to it.

Answer 1

You could use the built-in MemoryCache to store entire resultsets you have retrieved from the database.

A typical pattern:

MyModel model = MemoryCache.Default["my_model_key"] as MyModel; if (model == null) {     model = GetModelFromDatabase();     MemoryCache.Default["my_model_key"] = model; }  // you could use the model here 

Answer 2

I had to cache common database data like data that were displayed in drop downs. I used MemoryCache. And I used Entity Framework code first and Autofac for dependency injection.

Here is part of what I did in my solution, might not work for you but it worked for me, not perfect though but needs a lot of cleaning up to do.

My ICacheManager interface:

public interface ICacheManager {      T Get<T>(string key);       void Set(string key, object data, int cacheTime);       bool IsSet(string key);       void Remove(string key);       void Clear(); } 

My CacheManager class:

public class CacheManager : ICacheManager {      private ObjectCache Cache      {           get           {                return MemoryCache.Default;           }      }       public T Get<T>(string key)      {           return (T)Cache[key];      }       public void Set(string key, object data, int cacheTime)      {           if (data == null)           {                return;           }            CacheItemPolicy policy = new CacheItemPolicy();           policy.AbsoluteExpiration = DateTime.Now + TimeSpan.FromMinutes(cacheTime);            Cache.Add(new CacheItem(key, data), policy);      }       public bool IsSet(string key)      {           return (Cache.Contains(key));      }       public void Remove(string key)      {           Cache.Remove(key);      }       public void Clear()      {           foreach (var item in Cache)           {                Remove(item.Key);           }      } } 

An extension class for my caching:

public static class CacheExtensions {      public static T Get<T>(this ICacheManager cacheManager, string key, Func<T> acquire)      {           return Get(cacheManager, key, 60, acquire);      }       public static T Get<T>(this ICacheManager cacheManager, string key, int cacheTime, Func<T> acquire)      {           if (cacheManager.IsSet(key))           {                return cacheManager.Get<T>(key);           }           else           {                var result = acquire();                 cacheManager.Set(key, result, cacheTime);                 return result;           }      } } 

And this is how I would use it in my repository class. This method returns a list of all my banks which is displayed in a drop down.

public class BankRepository : RepositoryBase<Bank>, IBankRepository {      private readonly ICacheManager cacheManager;      private const string BanksAllCacheKey = "banks-all";       public BankRepository(IDatabaseFactory databaseFactory, ICacheManager cacheManager)           : base(databaseFactory)      {           Check.Argument.IsNotNull(cacheManager, "cacheManager");            this.cacheManager = cacheManager;      }       public IEnumerable<Bank> FindAll()      {           string key = string.Format(BanksAllCacheKey);            return cacheManager.Get(key, () =>           {                var query = from bank in DatabaseContext.Banks                            orderby bank.Name                            select bank;                 return query.ToList();           });      } } 

I hope this helps. It's a very simple implementation but it works for me. There are many articles online on how to use a caching strategy in ASP.NET MVC. Just Google it.