使用命名队列与SqlDependency

Question

我有以下使用SqlDependency监视我的数据库更改的代码。它工作得很好，但每次运行时都会在数据库中生成自己的带有GUID的队列/服务/路由：

类：

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

执行：

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

我希望通过创建一些队列/服务/路由对象并在程序中调用它们，而不是每次都生成新的对象。

我已经在数据库中创建了这些对象：

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

然后我修改了我的 C# 代码以使用这个队列和服务：

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

进行这些代码更改后，当我运行我的代码时不会创建任何队列，但是我的代码不再起作用，我的应用程序也无法识别对表格/查询所做的更改。

我已经花了几天的时间尝试解决问题，但没有成功，有人可以提供任何建议吗？谢谢。

- Bert Wagner

---

感谢您在问题中提供的代码，我终于成功地设置了SqlDependency... 直到现在，我一直遇到非常神秘的错误（或者根本没有错误，只是整个东西不起作用）。我想知道为什么很难找到任何关于这方面的好例子... 无论如何，谢谢！ - LambdaCruiser

Answer 1

我已经明白了——错误出现在我的SQL CREATE语句中。我使用的是：

CREATE SERVICE NamesService ON QUEUE NamesQueue;

但根据 http://technet.microsoft.com/en-us/library/ms190332.aspx 的说法，CREATE SERVICE 命令需要带上 contract_name 参数才能允许其他对话框将目标指向正在创建的服务。

因此，使用以下 CREATE 语句解决了我的问题：

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

- Bert Wagner

Answer 2

使用SqlDependency类需要小心，因为它存在内存泄漏的问题。然而，您可以使用SqlDependency类的开放源代码实现-SqlDependencyEx。它使用数据库触发器和本地服务代理通知来接收有关表更改的事件。以下是使用示例：

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

希望这有所帮助。

- dyatchenko