SqlCacheDependency的使用方法

步骤一：

sql数据库必须开启ServiceBroker服务，首先检测是否已经启用ServiceBroker，检测方法：

Select  DATABASEpRoPERTYEX('数据库名称','IsBrokerEnabled')

--1表示已经启用0表示没有启用

步骤二：

如果ServiceBroker没有启用，使用下面语句启用：

ALTER  DATABASE  <数据库名称>  SET  ENABLE_BROKER;

步骤三：Global.asax的设置

protected void Application_Start(object sender, EventArgs e)
        {
            SqlDependency.Start(ConfigurationManager.AppSettings["ConnectionString"].ToString());
        }

protected void Application_End(object sender, EventArgs e)
        {
            SqlDependency.Stop(ConfigurationManager.AppSettings["ConnectionString"].ToString());
        }

步骤四：缓存实现

使用sqldependency实现缓存的代码：

   public string GetCust()
        {
            string jsonCustInofor=string.Empty;
            //判断是否存在缓存，如果存在就使用
            if (HttpContext.Current.Cache[Cache_CustKey] == null)
            {
                List<Model.ComboxSourceModel> entities = new List<Model.ComboxSourceModel>();
                using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
                {
                    conn.Open();
                    SqlCommand sqlCommand = new SqlCommand();
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText = "select ccusCode,ccusName from dbo.Customers";
                    sqlCommand.Connection = conn;
                    SqlCacheDependency scd = new SqlCacheDependency(sqlCommand);
                    SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        Model.ComboxSourceModel entity = new Model.ComboxSourceModel();
                        entity.Key = reader["ccusCode"].ToString();
                        entity.Value = reader["ccusName"].ToString();
                        entities.Add(entity);
                    }
                    reader.Close();
                    reader.Dispose();
                    conn.Close();
                    conn.Dispose();
                    if (entities != null && entities.Count > 0)
                    {
                        jsonCustInofor=JsonHelper.Serialize(entities);
                        HttpContext.Current.Cache.Insert(Cache_CustKey, jsonCustInofor, scd);
                    }
                }

            }
            else
            {
                jsonCustInofor = HttpContext.Current.Cache[Cache_CustKey].ToString();
            }
            return jsonCustInofor;
        }
 

注意：

 这里的查询语句中不能出现“*”，表名之前要注明所有者（dbo.Customers）