SQLDependency times out

Question

I'm trying to get notifications when my SQL Server table was changed in my WPF app, and after about 20 seconds I get a timeout expired exception followed by

There is already an object named 'SqlQueryNotificationStoredProcedure-{random id}' in the database.

after which I get this:

Could not find stored procedure 'SqlQueryNotificationStoredProcedure-{...}'.
Cannot find the queue 'SqlQueryNotificationService-{...}', because it does not exist or you do not have permission.
Invalid object name 'SqlQueryNotificationService-{...}'

and then:

The conversation handle "{id...}" is not found.
The conversation handle "{id...}" is not found.

Most of the time only the timeout and the 'already an object named..' exceptions show and then, if I ignore these exceptions and continue, the SqlDependency still works fine afterwards, I've tried SqlTableDependency too but that one I couldn't get working, I'm super lost... I'd really appreciate any help!

This is the code for the SqlDependency:

public static void StartDependency()
{
        using (var dbconn = new SqlConnection(ConnStr))
        {
            dbconn.Open();

            using (SqlCommand command = new SqlCommand(*my select command*, dbconn))
            {
                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

                using (SqlDataReader reader = command.ExecuteReader())
                {

                }
            }
        }
}

private static void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
        SqlDependency dependency = sender as SqlDependency;
        dependency.OnChange -= OnDependencyChange;
        Console.WriteLine(e.Source);
        Console.WriteLine(e.Info);
        Console.WriteLine(e.Type);
        StartDependency();
}

and at startup I do this :

SqlDependency.Start(ConnStr);

AppDomain.CurrentDomain.UnhandledException += (s, e) => { SqlDependency.Stop(ConnStr); };
Application.Current.MainWindow.Closing += (s, e) => { SqlDependency.Stop(ConnStr); };

StartDependency();

Edit : Even if I don't call 'SQLDependency.Stop(ConnStr)' on UnhandledException it still throws the same exceptions, and I am using the latest System.Data.SqlClient on .NET 5.

Answer 1

I ended up using SqlDependencyEx, which worked out of the box! I noticed that it explicitly sets the timeout to 60000 seconds, so maybe that would have also helped with SqlDependency, but from what I understand, I shouldn't need to set something like that.