信号器在db表中的更改时不触发C＃事件 [英] Signalr not triggering C# event on changes in db table

问题描述

我正在使用signalR进行通知，即当记录插入表格时，我必须通知我的应用程序中的用户作为新通知。我的代码是这样的：



我在Application_start（）中的global.asax文件：

I am using signalR for notification i.e. when record is inserted in table, I have to inform user in my app as new notification. My code goes like this:

I global.asax file in Application_start():

SqlDependency.Start(@"data source=DESKTOP-591MN5Q\SQLEXPRESS01;initial catalog=Test_test;integrated security=True;");

我的通知服务类::

My notification service class::

public static class Notification
 {
     static readonly string connString = @"data source=DESKTOP-591MN5Q\SQLEXPRESS01;initial catalog=Test_test;integrated security=True;";
     internal static SqlCommand command = null;
     internal static SqlDependency dependency = null;

     public static string GetNotification()
     {
         try
         {

             var messages = new List<tblNotification>();
             using (var connection = new SqlConnection(connString))
             {
                 connection.Open();
                 using (command = new SqlCommand(@"SELECT [NotificationId],[UserId],[IsSeen],[Message],[CreatedDate],[ActionID] FROM [dbo].[tblNotification]", connection))
                 {
                     command.Notification = null;

                     if (dependency == null)
                     {
                         dependency = new SqlDependency(command);
                         dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                     }

                     if (connection.State == ConnectionState.Closed)
                         connection.Open();

                     var reader = command.ExecuteReader();

                     while (reader.Read())
                     {
                         messages.Add(item: new tblNotification
                         {
                             NotificationId = (int)reader["NotificationId"],
                             UserId = (int)reader["UserId"],
                             IsSeen = (bool)reader["IsSeen"],
                             Message = reader["Message"] != DBNull.Value ? (string)reader["Message"] : "",
                             CreatedDate = (DateTime)reader["CreatedDate"],
                             ActionID = (int)reader["ActionID"]
                         });
                     }
                 }
             }
             var jsonSerialiser = new JavaScriptSerializer();
             var json = jsonSerialiser.Serialize(messages);
             return json;

         }
         catch (Exception ex)
         {

             return null;
         }
     }

     private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
     {
         if (dependency != null)
         {
             dependency.OnChange -= dependency_OnChange;
             dependency = null;
         }
         if (e.Type == SqlNotificationType.Change)
         {
             MyHub.Send();
         }
     }
 }

我理解的是页面加载，将调用GetNotification（）方法，它将注册dependency_OnChange（）方法，这样如果在tblNotification中进行任何更改，dependency_OnChange（）将被触发。



我已执行以下脚本以启用更改跟踪：

What I understood is on page load, GetNotification() method will be called that will register dependency_OnChange() method such that if any changes is done in tblNotification, dependency_OnChange() will get triggered.

I have executed below script to enable change tracking:

ALTER DATABASE [Test_test]
	SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

但我的代码仍然没有能够在表中插入记录时触发dependency_OnChange（）方法。



我尝试过：



第一次执行GetNotification（）方法时，command.Notification = null。依赖关系将被初始化。在执行line var reader = command.ExecuteReader（）;之后，command.Notification将获得值。

But still my code is not able to trigger dependency_OnChange() method when record is inserted in table.

What I have tried:

In executing GetNotification() method for first time, command.Notification = null. dependency will be initialized. After executing line var reader = command.ExecuteReader();, command.Notification will get value .

推荐答案

我找到了解决方案。以防万一此解决方案将来会帮助其他人。



没有数据库所有者存在所以导致此问题。我更改了db owner，现在每个东西都按要求工作。

I found the solution. Just in case if this solution will help someone else in future.

No db owner existed so this issue was caused. I changes db owner and now every thing is working as required.

这篇关于信号器在db表中的更改时不触发C＃事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！