Sql Dependency with Service Broker

问题:

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.
}

回答1:

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

Hope this helps.