I can't get SqlDependency to fire, am I doing something obviously wrong?

Viewed 391

I am trying to setup a SqlDependency so my Winforms app doesn't have to bog down the server with 1 second repeating select queries to find out if a table value has changed. I have gotten rid of all the exceptions I encountered but I still can't get it to trigger.

I created a queue and a service in the database and I am using a SQL Server account that has all the required permissions as far as I can tell (Connect, Control, Create Procedure, Create Queue, Create Service, Execute, and Subscribe Query Notifications).

I was stuck for a while on an InvalidOperationException when I was using this line to create the dependency:

// create the SQL dependency
sqlDependency = new SqlDependency(command);

SqlDataReader reader = command.ExecuteReader(); throws the following exception:

System.InvalidOperationException: 'When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.'

I added the options and timeout parameters and that exception stopped occurring but I am not sure it actually fixed the underlying issue.

This is the new line:

// create the SQL dependency
sqlDependency = new SqlDependency(command, "service=RequestUpdateNotifications;", 0);

https://github.com/dotnet/dotnet-api-docs/issues/1670

Using the SQL Server Profiler I can see the call to the stored procedure (GetCurrentRequests) is getting called by '.NET SqlClient Data Provider' (exec GetCurrentRequests) and after the timeout another procedure is called by 'RequestUpdateMessages' (exec sp_executesql N'WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from [RequestUpdateMessages]), TIMEOUT @p2;',N'@p2 int',@p2=60000)

My issue is that I can't get OnDependencyChange() to fire. I have tried using my app to change the values in the table with a stored procedure and tried using SSMS to insert/update values to no avail. Hopefully I missed something simple but I have been googling and tweaking for a few hours and I can't get it working.

C#

internal DatabaseComms(string databaseIp, string databaseName)
{
    connectionString = "Password=****;Persist Security Info=True;User ID=****;InitiaCatalog="
        + databaseName + ";Data Source=" + databaseIp + ";";

    SqlDependency.Start(connectionString, "RequestUpdateMessages");

    using (SqlConnection sql = new SqlConnection(connectionString))
    {
        // Setup the SQL Command
        SqlCommand command = new SqlCommand("GetCurrentRequests", sql);
        command.Connection.Open();
        command.CommandType = CommandType.StoredProcedure;

        // create the SQL dependency
        sqlDependency = new SqlDependency(command, "service=RequestUpdateNotifications;", 0);

        // Subscribe to the SqlDependency event.
        sqlDependency.OnChange += new
           OnChangeEventHandler(OnDependencyChange);

        // execute the command
        SqlDataReader reader = command.ExecuteReader();
        reader.Close();

        // Dispose of the command
        command.Dispose();
    }
}

void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
    throw new NotImplementedException();
}

SQL

ALTER DATABASE ServiceHub SET ENABLE_BROKER;

CREATE QUEUE RequestUpdateMessages;  

CREATE SERVICE RequestUpdateNotifications  
  ON QUEUE RequestUpdateMessages;
0 Answers
Related