Using SqlDependency with named Queues

Question

I have the following code that uses SqlDependency to monitor changes in one of my databases It works great, except every run it generates its own Queue/Service/Route with a guid in the database:

Class:

class SqlWatcher
{
    private string connectionString;
    private string sqlQueue;
    private string listenerQuery;
    private SqlDependency dependency;

    public SqlWatcher(string connectionString, string sqlQueue, string listenerQuery)
    {
        this.connectionString = connectionString;
        this.sqlQueue = sqlQueue;
        this.listenerQuery = listenerQuery;
        this.dependency = null;
    }

    public void Start()
    {
        SqlDependency.Start(connectionString);
        ListenForChanges();
    }

    public void Stop()
    {
        SqlDependency.Stop(this.connectionString);
    }

    private void ListenForChanges()
    {
        //Remove existing dependency, if necessary
        if (dependency != null)
        {
            dependency.OnChange -= onDependencyChange;
            dependency = null;
        }

        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();

        SqlCommand command = new SqlCommand(listenerQuery, connection);

        dependency = new SqlDependency(command);

        // Subscribe to the SqlDependency event.
        dependency.OnChange += new OnChangeEventHandler(onDependencyChange);

        SqlDependency.Start(connectionString);

        command.ExecuteReader();

        //Perform this action when SQL notifies of a change
        performAction();

        connection.Close();
    }

    private void onDependencyChange(Object o, SqlNotificationEventArgs args)
    {
        if ((args.Source.ToString() == "Data") || (args.Source.ToString() == "Timeout"))
        {
            Console.WriteLine(System.Environment.NewLine + "Refreshing data due to {0}", args.Source);
            ListenForChanges();
        }
        else
        {
            Console.WriteLine(System.Environment.NewLine + "Data not refreshed due to unexpected SqlNotificationEventArgs: Source={0}, Info={1}, Type={2}", args.Source, args.Info, args.Type.ToString());
        }
    }

    private void performAction()
    {
        Console.WriteLine("Performing action");
    }
}

Execution:

static void Main(string[] args)
{
   string connectionString = @"<MY CONNECTION STRING>"; 
   string sqlQueue = @"NamesQueue";

   //Listener query restrictions: http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx
   string listenerQuery = "SELECT Value FROM dbo.Table WHERE Name = 'Test'";

   SqlWatcher w = new SqlWatcher(connectionString, sqlQueue, listenerQuery);
   w.Start();
   Thread.Sleep(10000);
   w.Stop();
}

Instead of generating its own queue/service/route everytime, I would like to create them up front and then tell my program to use them.

I went ahead and created these objects on the database :

CREATE QUEUE NamesQueue;
CREATE SERVICE NamesService ON QUEUE NamesQueue;
CREATE ROUTE NamesRoute WITH SERVICE_NAME = 'NamesService', ADDRESS = 'LOCAL';

and modified my C# code to use this queue and service:

...
SqlDependency.Start(connectionString, sqlQueue);
...
SqlDependency.Stop(this.connectionString, sqlQueue);
...
dependency = new SqlDependency(command, "service=NamesService;local database=<MY DB>", 0);
...
SqlDependency.Start(connectionString,sqlQueue);
...

Making these code changes causes no queues to be created on-the-fly when I run my code, however my code no longer works and my app doesn't recognize changes made to my table/query.

I have spent days trying to figure this out with no success, can anyone offer any advice? Thanks.

Answer 1

Figured it out - the error was in my SQL CREATE statements. I was using:

CREATE SERVICE NamesService ON QUEUE NamesQueue;

but according to http://technet.microsoft.com/en-us/library/ms190332.aspx, the CREATE SERVICE command needs to take a contract_name parameter in order to be able to allows other dialogs to target the service being created.

So, using the below CREATE statement fixed my issue:

CREATE SERVICE NamesService
ON QUEUE NamesQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]) ;

Answer 2

Be careful using SqlDependency class - it has the problems with memory leaks. Hovewer, you can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

Hope this helps.