如何将一个SqlCacheDependency附加到多个缓存项？

Question

SqlConnection sqlConn = new SqlConnection(m_connectionString);
m_cmd = sqlConn.CreateCommand();
m_cmd.CommandText = "Select id,name from dbo.instances";
m_cmd.Notification = null;
SqlCacheDependency cacheDepen = new SqlCacheDependency(m_cmd);

using (SqlDataAdapter sda = new SqlDataAdapter(m_cmd)) 
{
     DataSet ds = new DataSet();
     sda.Fill(ds, "instance");
     Cache.Insert("instance", ds.Tables["instance"],cacheDepen);
     Cache.Insert("timeNow", DateTime.Now.ToLongTimeString(), cacheDepen);
}

我正在由SqlCacheDependency创建一个web应用程序，Sql Server使用Service Broker在数据更新时通知我。它抛出异常“试图从多个缓存条目引用CacheDependency对象”，至少有两行。对于异常我应该怎么做？

Answer 1

我刚刚编写了一个快速的控制台应用程序来测试重用SqlCacheDependency对象，如果您省略了m_cmd.Notification = null;，它可以很好地工作。

namespace CacheDependencyReuse
{
class Program
{
    static void Main(string[] args)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM Products");
        SqlCacheDependency dep = new SqlCacheDependency(cmd);

        // Uncomment this line to get the Exception
        //cmd.Notification = null;

        using (SqlConnection conn = new SqlConnection("Data Source=xxx; Initial Catalog=Northwind;User ID=xxx; Password=xxx;"))
        {
            SqlDependency.Start(conn.ConnectionString);
            cmd.Connection = conn;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet productDataSet = new DataSet();
            conn.Open();
            adapter.Fill(productDataSet);


            HttpRuntime.Cache.Insert("Products",productDataSet,dep);
            HttpRuntime.Cache.Insert("Now",DateTime.Now,dep);

        }

        Console.ReadLine();
    }
}

}

我认为，如果您让Notification处理使用默认值，那么运行库将对您进行排序。