2

I am building an ASP.NET MVC application using the Repository pattern. A typical method in my Repository is as follows

   public IList<T> Select<T>(string cacheKey, string Sql, object filter) where T: new()
    {
        IList<T> items = MemoryCache.Default.Get(cacheKey) as IList<T>; 
          if (items == null || !items.Any())
          {
              items = Connection.Select<T>(Sql, filter);
              MemoryCache.Default.Add(cacheKey, items, DateTime.Now.AddMinutes(120));
          }

        return items;

    }

and it is being used as follows

IEnumerable<OSADCOL> osadcols = Repository.Select<OSADCOL>("OSADCOLS__TblId=" + Id, "TBLid = @id", new { id = Id });

In the above mentioned example OSADCOL is a model in my app and represents a Table with the same name in my Database. The Connection.Select function is ORMlite function. I don't use Entity Framework for performance issues. My problem is the following. I am currently cashing the result set for future relevance but I am doing it in a hard coded way. I am caching the result set for 2 hours. It is obvious that a proper implementation would be to discard my cashed data when the OSADCOL's table data changes. It seems that I have to use SQLDependency or SQLCacheDependency. The questions are the following:

  1. How am I going to use SQLDependency or SQLCacheDependency with this Repository?
  2. What is the actual difference between these 2?
  3. It is mentioned in some forums that SQLDependency creates memory leaks? Is that true? And If yes is there an alternative approach?

Any Ideas?

Christoph Adamakis
  • 865
  • 2
  • 9
  • 26

0 Answers0