how to add multiple different data into MemoryCache

Question

Even though I looked through a lot of documents about this concept, I couldn't make any progress on this problem. I have a wcf service which makes some queries to Sql Server database. When I cache only one query it works as I expect, but when I try to cache two queries, the second one throws the exception The method has already been invoked. I am new to this cache things, so let me know if there is a minor or major approach problems to my cachce concept. Below is the code snippet for what I'm trying to do:

var cachePolicy = new CacheItemPolicy();
var cache = MemoryCache.Default;

firstObject = cache["firstObject"] as string;
if ( firstObject == null )
    using ( var command = new SqlCommand("SELECT * FROM someTable", connection) )
    {
        cachePolicy.ChangeMonitors.Add(new SqlChangeMonitor(new SqlDependency(command, null, 600)));
        ///... I get the data from database and set the firstObject
        cache.Add("firstObject", firstObject , cachePolicy);
    }

secondObject = cache["secondObject"] as string;
if ( secondObject == null )
    using ( var command = new SqlCommand("SELECT * FROM someTable2", connection) )
    {
        cachePolicy.ChangeMonitors.Add(new SqlChangeMonitor(new SqlDependency(command, null, 600)));
        ///... I get the data from database and set the secondObject 
        cache.Add("secondObject", secondObject , cachePolicy);///---> problem occurs here
    }

I tried to use cache.Set method too, still same exception.

Answer 1

You need a CacheItemPolicy per cache item, so your code should look more like:

var cache = MemoryCache.Default;

var firstObject = cache["firstObject"] as string;
if (firstObject == null)
{
    var cmd = new SqlCommand("SELECT * FROM Table1");
    firstObject = GetFirstObject(cmd);
    var policy = new CacheItemPolicy();
    policy.ChangeMonitors.Add(new SqlChangeMonitor(new SqlDependency(cmd, null, 600)));
    cache.Add("firstObject", firstObject, policy);
}

var secondObject = cache["secondObject"] as string;
if (secondObject == null)
{
    var cmd = new SqlCommand("SELECT * FORM Table2");
    secondObject = GetSecondObject(cmd);
    var policy = new CacheItemPolicy();
    policy.ChangeMonitors.Add(new SqlChangeMonitor(new SqlDependency(cmd, null, 600)));
    cache.Add("secondObject", secondObject, policy);
}

In addition, I think you will find that to use a SqlDependency you cannot use * in the select and you must specify owner on the table. So

SELECT * FROM Table1 

becomes

SELECT Column1, Column1 FROM dbo.Table1

See here for more details

Hope this helps