SqlDependency OnChange Event为数据库上的每个事件触发多次
本文关键字：事件 Event OnChange 数据库 SqlDependency | 更新日期: 2023-09-27 18:04:45
我正在使用SqlDependency和signalR开发一个通知系统，我无法处理的问题是，当我根据与会者状态将DB中的属性值"IsOnline"更改为True或False时，OnChange事件多次触发，第一次新用户登录我得到两个通知，然后第二次我得到更像4然后越来越多。每次有新的用户登录或注销时，通知的数量都会增加。我确信SqlDependency中的问题不在SignalR中，我将与您分享我的部分代码。
提前感谢。
[System.Web.Services.WebMethod]
public static IEnumerable<AttendeeList> GetAllUsers()
{
var AttendeeList = new List<AttendeeList>();
try
{
using (var connection = new SqlConnection(_connString))
{
connection.Open();
string str = "";
str += "SELECT [AttendeeID], ";
str += " [IsAllowToUploadDocuments],";
str += " [IsOnline], ";
str += " [AttendeeTypeName],";
str += " [UserName] ";
str += " FROM [dbo].[Meeting_Attendees] ";
str += " INNER JOIN [dbo].[aspnet_Users] ON [aspnet_Users].[UserId] = [Meeting_Attendees].[AttendeeID] ";
str += " INNER JOIN [dbo].[AttendeeType] ON [dbo].[AttendeeType].[AttendeeTypeID] = [dbo].[Meeting_Attendees].[AttendeeTypeID] ";
str += " WHERE [MeetingID]=@MeetingID ORDER BY [IsOnline] DESC";
using (var command = new SqlCommand(@str, connection))
{
SqlParameter prm = new SqlParameter("@MeetingID", SqlDbType.Int);
prm.Direction = ParameterDirection.Input;
prm.DbType = DbType.Int32;
prm.Value = Convert.ToInt32(Properties.Settings.Default.MeetingID);
command.Parameters.Add(prm);
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependencyUsers_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
AttendeeList.Add(item: new AttendeeList { UserName = (string)reader["UserName"], UserType = (string)reader["AttendeeTypeName"], IsOnline = (bool)reader["IsOnline"], IsAllowToUploadDocuments = (bool)reader["IsAllowToUploadDocuments"], IsCurrentUser = true ? (Guid)reader["AttendeeID"] == new Guid(Properties.Settings.Default.UserID.ToString()) : false });
}
}
}
}
catch { }
return AttendeeList;
}
private static void dependencyUsers_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Update)
{
//Call SignalR
MessagesHub.UpdateUsers();
}
}
要确保事件处理程序只注册一次，请执行"-="+="前:
oDependency.OnChange -= new OnChangeEventHandler(DBUpdateNotificationReeived);
oDependency.OnChange += new OnChangeEventHandler(DBUpdateNotificationReeived);
检查你的SQL表是否有触发器(除了sqltabledependency) &;update"正在更新的记录
我也有同样的问题，在我的项目中多次调用OnChange事件，但我用help counter变量修复了它。在这个示例之后，在我的例子中，示例中的dependencyUsers_OnChange函数两次触发。
我将计数器变量初始化为全局变量。在"扫描"临时数据的状态之后，我还将计数器的值设置为0。
按照您的示例，在此步骤之后，在if语句中的dependencyUsers_OnChange处进行修改:
private static void dependencyUsers_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Update && counter == 0)
{
//Call SignalR
MessagesHub.UpdateUsers();
counter++; //The update is done once
}
else
{
counter = 0; //if the update is needed in the same iteration, please don't update and set the counter to 0
}
}
在您的情况下，解决方案将是这样的:
int counter = 0; //initialization of help counter
[System.Web.Services.WebMethod]
public static IEnumerable<AttendeeList> GetAllUsers()
{
var AttendeeList = new List<AttendeeList>();
try
{
using (var connection = new SqlConnection(_connString))
{
connection.Open();
string str = "";
str += "SELECT [AttendeeID], ";
str += " [IsAllowToUploadDocuments],";
str += " [IsOnline], ";
str += " [AttendeeTypeName],";
str += " [UserName] ";
str += " FROM [dbo].[Meeting_Attendees] ";
str += " INNER JOIN [dbo].[aspnet_Users] ON [aspnet_Users].[UserId] = [Meeting_Attendees].[AttendeeID] ";
str += " INNER JOIN [dbo].[AttendeeType] ON [dbo].[AttendeeType].[AttendeeTypeID] = [dbo].[Meeting_Attendees].[AttendeeTypeID] ";
str += " WHERE [MeetingID]=@MeetingID ORDER BY [IsOnline] DESC";
using (var command = new SqlCommand(@str, connection))
{
SqlParameter prm = new SqlParameter("@MeetingID", SqlDbType.Int);
prm.Direction = ParameterDirection.Input;
prm.DbType = DbType.Int32;
prm.Value = Convert.ToInt32(Properties.Settings.Default.MeetingID);
command.Parameters.Add(prm);
command.Notification = null;
var dependency = new SqlDependency(command);
counter = 0; //Whenewer the web method is called, set te counter to 0
dependency.OnChange += new OnChangeEventHandler(dependencyUsers_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
AttendeeList.Add(item: new AttendeeList { UserName = (string)reader["UserName"], UserType = (string)reader["AttendeeTypeName"], IsOnline = (bool)reader["IsOnline"], IsAllowToUploadDocuments = (bool)reader["IsAllowToUploadDocuments"], IsCurrentUser = true ? (Guid)reader["AttendeeID"] == new Guid(Properties.Settings.Default.UserID.ToString()) : false });
}
}
}
}
catch { }
return AttendeeList;
}
private static void dependencyUsers_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Update && counter == 0)
{
//Call SignalR
MessagesHub.UpdateUsers();
counter++; //The update is done once
}
else
{
counter = 0; //if the update is needed in the same iteration, please don't update and set the counter to 0
}
}
我希望这个想法能对别人有所帮助，我用这个解决方案解决了我项目中的问题
查询通知在被监视的结果集可能发生更改时触发，请参见了解何时发生查询通知。作为一般规则，您可能会收到比实际数据更改更多的通知:
请注意，SQL Server可能会在不改变数据的事件或不影响查询结果的变化的响应中产生查询通知。例如，当UPDATE语句更改查询返回的一行时，即使对该行的更新没有更改查询结果中的列，也可能会触发通知。查询通知旨在支持提高缓存数据的应用程序的性能这一总体目标。当服务器负载过重时，SQL server可能会为订阅生成查询通知消息，而不是执行确定查询结果是否已更改的工作。
从你的帖子中无法判断这是否会导致问题，特别是不清楚你是如何处理更新的，所以它会导致4个以上的通知。得到4个通知意味着你发布了4个查询要通知，所以很可能你的代码也有问题，你超额订阅了。
阅读使用SQL跟踪故障排除查询通知，并尝试故障排除发生了什么，在哪里创建和无效的通知。
我在使用SignalR和SQL依赖时遇到了同样的问题
该行被执行了不止一次。事件应该只订阅一次。oDependency。OnChange += new OnChangeEventHandler(dbupdatenotificationreceived);