一、启用 Service Broker
1、检查数据库是否启用了Service Broker
SELECT is_broker_enabled FROM sys.databases WHERE name = '数据库名'
查询结果:is_broker_enabled de 结果为 0,代表数据库没有启动 Service Broker
如果查询结果为0,执行下面语句
use 数据库名
go
ALTER DATABASE 数据库名 SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE 数据库名 SET ENABLE_BROKER;
之后在查询一遍,结果为1
二、Web.config配置
Web.config加入代码
<connectionStrings>
<add name="SQLJT" connectionString="数据库连接字符串"/>
</connectionStrings>
三、在Global.asax添加启用和停止监听
1、创建Global.asax
2、写入代码
string connectionString = ConfigurationManager.ConnectionStrings["SQLJT"].ConnectionString;
void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
System.Data.SqlClient.SqlDependency.Start(connectionString);
}
void Application_End(object sender, EventArgs e)
{
// Code that runs on application shutdown
System.Data.SqlClient.SqlDependency.Stop(connectionString);
}
四、所需页面监听代码
//监听数据库
private static string conn = ConfigurationManager.ConnectionStrings["SQLJT"].ConnectionString;
static SqlDependency dependency;
protected void Page_Load(object sender, EventArgs e)
{
SqlDependency.Start(conn); //传入连接字符串,启动基于数据库的监听
if (!IsPostBack)
{
Update(conn);
}
}
//使用SqlDependency监控数据库表变化
private void Update(string conn)
{
using (SqlConnection connection = new SqlConnection(conn))
{
StringBuilder strsql = new StringBuilder();
//对被监控的目标表做简单查询,此处 要注意 不能使用* 表名要加[dbo] 否则会出现一直调用执行 OnChange
strsql.Append(@"select id,title,subTitle,link,classificationID from dbo.tb_article");
using (SqlCommand command = new SqlCommand(strsql.ToString(), connection))
{
connection.Open();
command.CommandType = CommandType.Text;
dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);//添加监控,检测数据表变化
//必须要执行一下command
command.ExecuteNonQuery();
//Console.WriteLine(dependency.HasChanges);
//connection.Close();
}
}
}
//检测到数据表变化后执行动作
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change)
{
//这里要再次调用
Update(conn);
}
}