What is the best way to load and reuse List objects in MVC controller

Question

I am developing a MVC project though I am new in MVC, where I have a function which is loading database values. I am using this function in different places within the same controller. When I have checked the SQL profiler, I found that everytime its running the procedure where I used that function.

My question is what is best way to use the function which will call the procedure in the first time and from next time, it will use the same value.

Thanks in advance

Updated:

public List<DataRow> SalesCount()
{          
    List<DataRow> lstSales = new List<DataRow>();
    string str_salesClass = adding session value here;    
    return lstSales = SalesRepository.SalesCount(SessionValue);        
}

@VadimMartynov, Thanks Vadim, I might get idea about the cache data, though in my scenario, how can I implement the cache data — ronibd, Apr 06 '16 at 11:40

Mikhail Tulubaev · Accepted Answer · 2016-04-06T11:57:31.433

There are several ways to do this inside Controller. At first you should know, that with every request to controller you are using different instances of the same controller.

First way: to use a singleton. Store value in public static instance, load it once and get the same List every time you ask. However, as it is database values - you have a need to periodically update stored values from database.

public class LoaderSingleton
{
    private static readonly LoaderSingleton _instance = new LoaderSingleton();
    private List<DataRow> _items;
    static LoaderSingleton()
    {

    }

    private LoaderSingleton()
    {

    }

    public static LoaderSingleton Instance
    {
        get
        {
            return _instance;
        }
    }

    public List<DataRow> Items
    {
        get
        {
            if (this._items == null)
            {
                this.Load();
            }

            return this._items;
        }
    }

    public void Load()
    {
        this._items = //perform load from database
    }
}

Second way: to use a Session variable. Store values in the user session instead of static class. You should not to forget to update values and clean session.

//inside controller
this.Session[str_salesClass] = SalesRepository.SalesCount();

Third way: to use ViewData variable. Value will be stored only between requests and you should to update it with new values, after last request will be complete.

//inside controller
this.ViewData[str_salesClass] = SalesRepository.SalesCount();

Fourth way: Perform async requests from your view. For example, you are loading required values to base view, which can asynchronously load another views and when you are sending request for load child view - you should pass loaded data as parameter.

public ActionResult BaseView()
{
    ViewBag.Data = SalesRepository.SalesCount();
    return View();
}

[ChildActionOnly]
public ActionResult ChildView1(List<DataRow> sales)
{

}

I do not recommend to use Cache, cause it has unpredictable behaviour. And you will get null when you are not expected it.

Thank you Mikhail, I am going to use the ViewData/ViewBag option, Many thanks — ronibd, Apr 06 '16 at 12:19

Vadim Martynov · Answer 2 · 2016-04-06T11:54:51.620

You can use MemoryCache to save SQL query results and have a new request only if some parameters changes or cache value needs to be invalidate.

For configure cache parameters you can use CacheItemPolicy class. You can configure value that indicates whether a cache entry should be evicted after a specified duration, extra monitors to invalidate cache state by the trigger and other parameters.

// your controller field
private readonly MemoryCache _dbDataCache = MemoryCache.Default;

// string key for your cached data
private const string DbDataCacheSalesCountKey = "DbDataCacheSalesCountKey";

public List<DataRow> SalesCount()
{
    if(_dbDataCache.Contans(DbDataCacheSalesCountKey))
        return _dbDataCache[DbDataCacheSalesCountKey] as List<DataRow>;

    string str_salesClass = adding session value here;  
    var lstSales = SalesRepository.SalesCount(SessionValue);

    // save cache only for 10 minutes
    var cip = new CacheItemPolicy { AbsoluteExpiration = new DateTimeOffset(DateTime.Now.AddMinutes(10)) };
    _dbDataCache.Set(DbDataCacheSalesCountKey, lstSales, cip);
    return lstSales;
}

The alternative code organization looks like next one and works the same with code below:

public List<DataRow> SalesCount()
{
    var salesCount = _dbDataCache[DbDataCacheSalesCountKey] as List<DataRow>;
    if(salesCount == null)
    {
        string str_salesClass = adding session value here;  
        salesCount = SalesRepository.SalesCount(SessionValue);

        // save cache only for 10 minutes
        var cip = new CacheItemPolicy { AbsoluteExpiration = new DateTimeOffset(DateTime.Now.AddMinutes(10)) };
        _dbDataCache.Set(DbDataCacheSalesCountKey, salesCount, cip);
    }

    return salesCount;
}

Thank you Vadim for your assistance, after testing, I think it will be better to use ViewData/ViewBag, Many thanks — ronibd, Apr 06 '16 at 12:18

What is the best way to load and reuse List objects in MVC controller

2 Answers2