与Service Broker的Sql依赖关系

问题描述

我已经为我们的一台本地服务器编写了Windows服务。这项服务在本地计算机上的工作方式就像是gem，可以完成应有的操作（ACCP来交换数据库数据），但是我对依赖不是很熟悉。这是构造程序的合适方法吗？

I have written a windows service for one of our local servers. This service works like a gem on my local machine, does what it's supposed to (ACCP to exchange db data), but I'm not overly familiar with dependencies. Is this an appropriate way to structure the program?

string DBP3_US = DBP3_US;
string PING_DEPENDENCY = "SELECT [SomeColumn] FROM [SomeTable];";

protected override void OnStart(string[] args)
{
    SqlDependency.Start(DBP3_US);
    Thread Ping_US = new Thread(PingThread);
    Ping_US.Name = "ping_US";
    Ping_US.Start();
}

private void PingThread()
{
    CreateCommandWithDependency(PING_DEPENDENCY, Ping_OnChange);
}

private void CreateCommandWithDependency(string queryText, OnChangeEventHandler e, string db = DBP3_US)
{
    using (SqlConnection con = new SqlConnection(db))
    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = queryText;
        cmd.CommandType = CommandType.Text;
        cmd.Notification = null;
        SqlDependency sqlDep = new SqlDependency(cmd);
        sqlDep.OnChange += new OnChangeEventHandler(e);
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

private void Ping_OnChange(object sender, SqlNotificationEventArgs e)
{
    PingDependency();
    Thread Ping_US = new Thread(PingThread);
    Ping_US.Start();
}

private void PingDependency(string db = DBP3_US)
{
    // Do whatever operations are required.
}

推荐答案

请小心使用类以监视数据库表中的更改-它具有，并且存在内存泄漏。但是，您可以将自己的实现与DDL触发器和或使用一个开源项目，例如：

Be careful using the SqlDependency class to monitor changes in the database tables - it has the problems with the memory leaks. However, you can use your own realization with DDL triggers and SQL Service Broker API or use one of an open source projects, e.g. 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);

希望这会有所帮助。

这篇关于与Service Broker的Sql依赖关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！