我们先做个小例子
1.新建一个类
/// <summary>
/// 数据变化检测,利用dependency实现
/// 需要开启数据库的Service Broker
/// use 数据库名
///go
///ALTER DATABASE 数据库名 SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
///ALTER DATABASE 数据库名 SET ENABLE_BROKER;
///注意:查询语句中不能使用*,表名要加[dbo].[xx]。sql语句要执行。
/// </summary>
public class DataChange
{
public static string _connStr = ConfigurationManager.AppSettings["conStr2"].ToString();
public static SqlDependency dependency;
public SqlCommand command;
/// <summary>
/// 开始监听
/// </summary>
public void Start()
{
SqlDependency.Start(_connStr);//传入连接字符串,启动基于数据库的监听
UpdateGrid();
}
/// <summary>
/// 停止监听
/// </summary>
public void Stop()
{
SqlDependency.Stop(_connStr);
}
private void UpdateGrid()
{
using (SqlConnection connection = new SqlConnection(_connStr))
{
connection.Open();
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
string StrSql = "SELECT [PersonId]FROM [dbo].[Exam_Person_Check] WHERE [IsDel]=0 AND [Status] = 1 AND [VerifyTime] = '2020/5/22 9:59:56'";
using (SqlCommand command = new SqlCommand(StrSql, connection))
{
command.CommandType = CommandType.Text;
//connection.Open();
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataReader sdr = command.ExecuteReader())
{
Console.WriteLine();
while (sdr.Read())
{
Console.WriteLine("AssyAcc:{0}\r\n\t", sdr["PersonId"].ToString());
}
sdr.Close();
}
}
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change) //只有数据发生变化时,才重新获取并数据
{
UpdateGrid();
}
}
}
2.在一个控制台项目下使用上面的东西
static void Main(string[] args)
{
DataChange dataChange = new DataChange();
dataChange.Start();
Console.Read();
dataChange.Stop();
}
运行程序会精选的发现,修该数据库的东西,不会触发depency_OnChange.而把sql中where后面最后个条件删了改成这样“SELECT [PersonId]FROM [dbo].[Exam_Person_Check] WHERE [IsDel]=0 AND [Status] = 1”就可以了。
问题大概明了,我们来尝试别的方法,看看能解决不。
我们尝试下command.Parameters来添加查询参数试一试
private void UpdateGrid()
{
using (SqlConnection connection = new SqlConnection(_connStr))
{
connection.Open();
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
string StrSql = string.Format(@"SELECT [PersonId]FROM [dbo].[Exam_Person_Check] WHERE [IsDel]=0 AND [Status] = 1 AND [VerifyTime] = @CreatedOn");
using (SqlCommand command = new SqlCommand(StrSql, connection))
{
command.Parameters.Add("@CreatedOn", SqlDbType.DateTime);
command.Parameters["@CreatedOn"].Value = Convert.ToDateTime("2020/5/22 9:59:56");
command.CommandType = CommandType.Text;
//connection.Open();
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataReader sdr = command.ExecuteReader())
{
Console.WriteLine();
while (sdr.Read())
{
Console.WriteLine("AssyAcc:{0}\r\n\t", sdr["PersonId"].ToString());
}
sdr.Close();
}
}
}
再次运行,嗯,完美解决
---如果这篇文章帮助到您,麻烦点个赞,谢谢