asp.net Sql缓存依赖

1. sqlserver(本人用的sqlserver2008)中使用语句ALTER DATABASE TestDB SET ENABLE_BROKER;启用监听服务

如果启用失败或者很长时间一直在执行请执行以下语句，然后秒了

ALTER DATABASE TestDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE TestDB SET ENABLE_BROKER;

2. 检查数据库是否启用监听服务

Select DATABASEpRoPERTYEX('db_artistic_studios3','IsBrokerEnabled')

ALTER DATABASE db_artistic_studios3 SET ENABLE_BROKER;

ALTER DATABASE db_artistic_studios3 SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db_artistic_studios3 SET ENABLE_BROKER;

SELECT is_broker_enabled FROM sys.databases WHERE name = 'TestDB'

(1为已启用,0为未启用)

3. 配置WebConfig

配置<connectionStrings>

   
   

    
    <
    
    connectionStrings
    
    >


    
    <
    
    add 
    
    name
    
    ="conStr"
    
     connectionString
    
    ="Data Source=.;Initial Catalog=TestDB;Persist Security Info=True;User ID=sa;Password=sa"
    
     providerName
    
    ="System.Data.SqlClient"
    
     
    
    />  


    
    </
    
    connectionStrings
    
    >
   
   

<system.web> 节点下配置

  
  

   
   <
   
   caching
   
   >       


   
   <
   
   sqlCacheDependency 
   
   enabled
   
   ="true"
   
    pollTime
   
   ="1000"
   
   >         


   
   <
   
   databases
   
   >           


   
   <
   
   add 
   
   name
   
   ="TestDB"
   
    connectionStringName
   
   ="conStr"
   
   />         


   
   </
   
   databases
   
   >       


   
   </
   
   sqlCacheDependency
   
   >     


   
   </
   
   caching
   
   >
  
  

下面上示例代码：

  
  

   
   //
   
   定义连接字符串
   
   


   
   string conStr = System.Configuration.ConfigurationManager.ConnectionStrings[
   
   "
   
   conStr
   
   "].ConnectionString;




   
   protected 
   
   void Page_Load(
   
   object sender, EventArgs e)

{



    
   
   if (!IsPostBack)

    {



        SqlDependency.Start(conStr);
   
   //
   
   启动监听服务，ps：只需启动一次
   
   

        System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(conStr);
   
   //
   
   设置通知的数据库连接，ps：只需设置一次
   
   

        System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(constr, 
   
   "
   
   dbo.TableA
   
   ");
   
   //
   
   设置通知的数据库连接和表，ps：只需设置一次
   
   

        
   
   string sql = 
   
   "
   
   select Name from dbo.TableA
   
   ";

        SqlConnection con = 
   
   new SqlConnection(conStr);

        SqlCommand cmd = 
   
   new SqlCommand(sql, con);

        SqlDataAdapter adapter = 
   
   new SqlDataAdapter(cmd);



        DataSet ds = 
   
   new DataSet();

        adapter.Fill(ds, 
   
   "
   
   cache
   
   ");



        System.Web.Caching.SqlCacheDependency cd = 
   
   new System.Web.Caching.SqlCacheDependency(
   
   "
   
   TestDB
   
   ", 
   
   "
   
   dbo.TableA
   
   ");
   
   //
   
   建立关联
   
   

        Cache.Insert(
   
   "
   
   cache
   
   ", ds, cd);

    }



}