数据缓存.NET 4.0 MemoryCache with SqlChangeMonitor

数据缓存必须在数据库上开启 SQL Server Service Broker服务，执行以下代码

ALTER DATABASE database_name SET TRUSTWORTHY ON WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE database_name SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER AUTHORIZATION ON DATABASE::database_name TO sa

 public class UserDal
    {
        public List<UserInfo> GetModelList()
        {
            const string Users = "Users";
            List<UserInfo> list = null;
            if (MemoryCache.Default.Contains("Users") == false)
            {
                list = new List<UserInfo>();
                CacheItemPolicy policy = new CacheItemPolicy();//缓存过期策略
                string connStr = ConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;

                SqlDependency.Start(connStr);//必须先调用，否则无法执行Sql命令

                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    //注意：sql语句中必须要指名查询的字段名，不能使用*号，并且表名必须的为：dbo.表名，否则缓存无效
                    using (SqlCommand command = new SqlCommand("select Username,DisplayName from dbo.users", conn))
                    {
                        command.Notification = null;
                        SqlDependency dep = new SqlDependency();//Sql依赖命令
                        dep.AddCommandDependency(command);//建立依赖关系
                        conn.Open();
                        using(IDataReader reader = command.ExecuteReader())//必须执行一次命令，否则无法注册通知
                        {
                            while (reader.Read())
                            {
                                list.Add(BindingReader(reader));
                            }
                        }
                        SqlChangeMonitor monitor = new SqlChangeMonitor(dep);
                        policy.ChangeMonitors.Add(monitor);//如果数据库表更改，策略使将缓存数据从缓存中移除
                    }
                }
                MemoryCache.Default.Add("Users", list, policy);
                //MemoryCache.Default.Set(Users, list, policy);
            }
            else
            {
                list = MemoryCache.Default.Get(Users) as List<UserInfo>;
                if (list.FirstOrDefault(m=>m.UserName=="system") ==null)
                {
                    list.Add(new UserInfo { UserName = "system", DisplayName = "缓存" });
                }
            }
            return list;
        }
        


        private UserInfo BindingReader(IDataReader reader)
        {
            UserInfo model = new UserInfo();
            model.UserName = reader["UserName"].ToString();
            model.DisplayName = reader["DisplayName"].ToString();
            return model;
        }

    }