Getting SqlChangeMonitor to work

Question

I am trying to get the new SqlChangeMonitor feature included in the System.Runtime.Caching namespace to work and am getting some unexpected results.

I have a simple console application setup as:

static void Main(string[] args)
{
    var cacher = new Cacher();

    cacher.Start();

    Console.WriteLine("Watching for changes...");

    while (true)
    {
        if (cacher.HasData)
        {
            Console.Write(".");
            Thread.Sleep(1000);
        }
        else
        {
            Console.WriteLine();
            Console.WriteLine("Cache EMPTY!");
            Console.ReadLine();
            cacher.Start();
        }
    }
}

The Cacher class is defined as:

public class Cacher
{
    private MemoryCache cache = new MemoryCache("test");

    public Boolean HasData
    {
        get { return cache.Contains("data"); }
    }

    public void Start()
    {
        var connectionString = "Data Source=.;Initial Catalog=CachingTest;Integrated Security=True";
        var list = new NameValueCollection();
        var policy = new CacheItemPolicy();

        SqlDependency.Start(connectionString);

        using (var connection = new SqlConnection(connectionString))
        {
            using (var command = new SqlCommand("SELECT * FROM dbo.Table_1", connection))
            {
                var dependency = new SqlDependency(command);

                connection.Open();

                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(reader["Name"].ToString(), reader["Value"].ToString());
                }

                var monitor = new SqlChangeMonitor(dependency);

                policy.ChangeMonitors.Add(monitor);
            }

            cache.Add("data", list, policy);
        }
    }
}

When I run the code, I get the Waiting message and a single dot '.', the one second delay, then the "Cache Empty" message. This is repeated after I press a key to restart the loop.

What am I missing?

score 15 · Accepted Answer · answered Oct 24 '11 at 22:23

15

It turns out that my original problem was caused by using the asterisk (*) wildcard in the SELECT statement. As soon as I listed the column names, the cache started working properly.

I also wanted to receive notifications in code when the cache has been updated/cleared. The NotifyOnChanged method of the ChangeMonitor does not work as this is intended for internal use.

As it turns out, the CacheItemPolicy object has two callbacks: RemovedCallback and UpdateCallback that may be used for these purposes. Simply set these properties to methods in code and take the desired action.

Given the lack of documentation available on the SqlChangeMonitor, I hope this may be of assistance to anyone else running into these issues.

answered Oct 24 '11 at 22:23

SonOfPirate

5,642
3
41
97

I see that each time you add an item to the cache, you repeat the steps of creating a connection, a sql command, the dependency, executing the reader etc. and finally adding the `SqlChangeMonitor` to the `CacheItemPolicy`. Would it be safe to perform all these steps only once (perhaps at application startup) and then reuse the `policy` instance each time an entry is added to the cache? – Gabriel S. Feb 28 '14 at 14:16
This is a proof-of-concept and far from production-ready code. That said, no, I would not perform those steps on start-up and leave the connection open, etc. so I could reuse the policy each time an entry is added to the cache. I won't delve into all of the reasons as it is well established that doing so is bad practice. You want to create a new connection each time to make sure you are leveraging connection pooling and not holding onto resources when you don't need them. (Again, the code above is for demonstration purposes only.) – SonOfPirate Feb 28 '14 at 19:15
I'm not talking about any connection left open; from what i understand, the connection is closed anyway as soon as the monitor is included in the policy, it's right there in the code. What i actually need to know is if the same policy instance becomes "stale" and unusable after using it more than once, for new cache entries. – Gabriel S. Mar 03 '14 at 16:16
1

Well, it seems it's not possible to reuse the policy, most probably due to the `SqlChangeMonitor`, it complains with an `InvalidOperationException` when trying to add a new entry in the cache using the same policy instance. – Gabriel S. Mar 05 '14 at 11:16
1

This MSDN article gives the requirements for the Query used in a change monitor, might be helpful for someone in the future: https://msdn.microsoft.com/library/ms181122.aspx – mcdrummerman Dec 08 '15 at 08:29
This Codeproject artice gives the requirements for the Query used in a change monitor, might be helpful for someone in the future: https://www.codeproject.com/articles/167282/net-memorycache-with-sqlchangemonitor – Colin Chen Jan 07 '22 at 05:28

Getting SqlChangeMonitor to work

1 Answers1