数据库依赖侦听

前言

.NET缓存技术对于系统并发量请求所带来数据库服务器的性能提升是非常显著的，但为了优化WEB服务器资源，通常设置缓存的生命周期，当然缓存的技术除了Cache，也可以适当使用静态类来处理，加上数据库侦听来更新静态类的数据结构。

类及属性定义

        private static object _lockObj = new object();
        public static CacheDepts _instance = null;
        public List<Auth_Departments> _depts = null;protected CacheDepts() { }

        public static CacheDepts Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (_lockObj)
                    {
                        return new CacheDepts();
                    }
                }
                return _instance;
            }
        }    

定义CacheDepts类型，包含静态属性Instance，属性_depts

静态类封装

        public List<Auth_Departments> GetDepts()
        {
            if (_depts == null)
            {
                lock (_lockObj)
                {
                    using (HaiYiWebContext context = new HaiYiWebContext())
                    {
                        _depts = context.Auth_Departments.ToList();
                    }
                }
                NotifyDepts();
            }
            return _depts;
        }    

数据库侦听事件

        public void NotifyDepts()
        {
            using (var conn = new SqlConnection(CacheManager.ConnecionString))
            {
                using (var cmd = new SqlCommand("SELECT IID,DeptName,ParentID,IsDeleted FROM dbo.Auth_Departments", conn))
                {
                    var dependency = new SqlDependency(cmd);

                    dependency.OnChange += DeptsDependencyOnChange;

                    conn.Open();

                    SqlDataReader rdr = cmd.ExecuteReader();
                    Console.WriteLine(rdr.FieldCount);
                    rdr.Close();
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 数据有更新时触发该方法
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="sqlNotificationEventArgs"></param>
        private void DeptsDependencyOnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                SqlDependency dependency = (SqlDependency)sender;
                dependency.OnChange -= this.DeptsDependencyOnChange;

                _depts = null;
            }
        }

增加数据库侦听时，数据库的Broker属性必须开启

ALTER DATABASE DBForDepart SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE DBForDepart SET ENABLE_BROKER;

数据库侦听结果可开启Sql Server Profiler工具查看执行情况