未触发 SQL 依赖项事件

Question

第一次使用 SQL 依赖...但是在看了几个例子之后，我觉得我做的一切都是正确的。我已经检查了代理是否已启用。我进一步检查了我的查询是否正确。我根本没有收到任何异常！所有和所有的东西似乎都应该工作...但事实并非如此，我不知道如何在没有任何异常的情况下开始对其进行故障排除。

任何帮助将不胜感激！

这是我的班级：

public class NotificationEvent
{
    private delegate void RateChangeNotification(DataTable table);
    private SqlDependency dependency;
    string ConnectionString = @"ConnectionString";
    string UserName = Environment.UserName;
    public async void StartNotification()
    {
        SqlDependency.Start(this.ConnectionString, "UserNotificationsQueue");
        SqlConnection connection = new SqlConnection(this.ConnectionString);
        await connection.OpenAsync();
        SqlCommand command = new SqlCommand();           
        command.Connection = connection;
        command.CommandType = CommandType.Text;
        command.CommandText = string.Format("SELECT [NotificationID],[UserFrom],[UserTo],[DateTimeSent],[Notification] FROM [dbo].[PersonnellNotifications]", UserName);
        command.Notification = null;

        this.dependency = new SqlDependency(command, "Service=PostUserNotificationsQueue;", Int32.MaxValue);
        dependency.OnChange += new OnChangeEventHandler(this.SqlDependencyOnChange);
        await command.ExecuteReaderAsync();
    }
    private void SqlDependencyOnChange(object sender, SqlNotificationEventArgs eventArgs)
    {
        if (eventArgs.Info == SqlNotificationInfo.Invalid)
        {
            Console.WriteLine("The above notification query is not valid.");
        }
        else
        {
            Console.WriteLine("Notification Info: " + eventArgs.Info);
            Console.WriteLine("Notification source: " + eventArgs.Source);
            Console.WriteLine("Notification type: " + eventArgs.Type);
        }
    }

    public void StopNotification()
    {
        SqlDependency.Stop(this.ConnectionString, "QueueName");
    }
}

我正在从另一个类 IniatializeComponent(( 初始化它，如所见：

private void InitializeComponent()
{
    // Initialize SQL Dependancy        
    ne.StartNotification();
}

Answer 1

我刚刚在我的代码及其工作良好中测试了以下内容。我已经简化了你的代码。请查看这是否有效，并且您在 OnNotificationChange on db 更改中接到电话。

public async void RegisterForNotification()
{
     var connectionString = @"ConnectionString";
     using (var connection = new SqlConnection(connectionString))
     {
         await connection.OpenAsync();
          var queryString = "Your Query String";
          using (var oCommand = new SqlCommand(queryString, connection))
          {
              // Starting the listener infrastructure...
              SqlDependency.Start(connectionString);
               var oDependency = new SqlDependency(oCommand);
               oDependency.OnChange += OnNotificationChange;
               // NOTE: You have to execute the command, or the notification will never fire.
                await oCommand.ExecuteReaderAsync();
            }
        }
    }

private void OnNotificationChange(object sender, SqlNotificationEventArgs e)
{
   Console.WriteLine("Notification Info: " + e.Info);
    //Re-register the SqlDependency. 
   RegisterForNotification();
}

Answer 2

您是否正在设置 SQLClientPermission？ 请参阅：https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-query-notifications

// Code requires directives to
// System.Security.Permissions and
// System.Data.SqlClient
private bool CanRequestNotifications()
{
    SqlClientPermission permission =
        new SqlClientPermission(
        PermissionState.Unrestricted);
    try
    {
        permission.Demand();
        return true;
    }
    catch (System.Exception)
    {
        return false;
    }
}