How do I cache a dataset to stop round trips to db?

Question

I am creating a search results page in C# in an ASP.NET 1.1 page. In my data layer I have a DataSet that stores the result of a plain old ADO.NET stored procedure call. The DataSet has two DataTables and I'm using a DataVIew to filter and sort the columns. I only want to fill the dataset once, and then work on the DataTables and derived DataView until the page is unloaded. How best should I cache the DataSet in my DAL so that it is filled only PageLoad? Do i put it in a Cache object, a static member variable, a property...I don't have any fancy entity models or ORM, this is .NET 1.1. Thanks in advance.

Answer 1

Will your DAL be used by any other applications? If not then you can imbed the caching of the DataSet in the Cache or Session depending on what features are need of the storage.

Session will be specific to the user and get cleaned up when the user's session expires which means the data might be around a lot longer than required. More info on Session

Cache is nice because it will auto expire (use sliding expiry if the search data will not change often) and you can store the search criteria with it so that other users can leverage the search as well which will save you calls to the DB by multiple users possibly. Multiple user's being able to access this data is a big advantage over using Session. More info on Cache

If you plan to use your DAL in other apap, you might want the application to do the caching itself.

You just need a wrapper like:

// Consider this psuedo code for using Cache
public DataSet GetMySearchData(string search)
{
    // if it is in my cache already (notice search criteria is the cache key)
    string cacheKey = "Search " + search;
    if (Cache[cacheKey] != null)
    {
        return (DataSet)(Cache[cacheKey]);
    }
    else
    {
        DataSet result = yourDAL.DoSearch(search);
        Cache[cacheKey].Insert(result);  // There are more params needed here...
        return result;
    }
}

Answer 2

there are ORMs that work with .net 1.1, but if you don't want to use them, I would recommend either populating something in the Cache so that you can expire it if needed and re-get the data. You could also put it into the session (if the data is user specific) or application (if it is not) objects.

Answer 3

If this is per user data don't cache a large amount of data putting it into session or similar, you are seriously hindering the ability of your site to scale. Consider how the amount of info in memory grows as you add more users, and then what happens when you need to use more than a single site server.

Instead modify the procedure so you can only retrieve the page of data that you need to show.

If this is data that is used for all users, definitely cache it. You can use the asp.net Cache for that.