SQL dependency event not being triggered

Question

First time working with SQL Dependency... but after having gone over several examples I feel as I am doing everything correct. I've checked that the Broker is Enabled. I've further checked that my query is correct. I am not receiving any exceptions at all! All and all everything seems as it should work... but it is not, and I have no idea how to begin to troubleshoot it without any exceptions being thrown.

Any help would be VERY much appreciated!

Here is my class:

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");
    }
}

I am initializing this from another classes IniatializeComponent() as seen:

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

Answer 1

I have just tested following in my Code and Its working good. I have simplified your code. Please see if this is working and you are getting a call in OnNotificationChange on Db Change.

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

Are you setting SQLClientPermission? see: 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;
    }
}