1. MSSql (本人用的sqlserver2008)中使用语句
ALTER DATABASE TestDB SET ENABLE_BROKER;
启用监听服务
如果启用失败或者很长时间一直在执行请执行以下语句:
ALTER DATABASE TestDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE TestDB SET ENABLE_BROKER;
2. 检查数据库是否启用监听服务
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);
}
}
这样初始化后,更新数据库TestDB中表dbo.TableA后,缓存Cache["cache"]会失效。