SQL Dependency with Broker

Question

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 receive/send a message?

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

Answer 1

I refactored your service base code into another class, which raises an event DataChanged. This way you can subscribe to multiple queries and databases. I also created a SqlDatabaseDepedency class which starts and stops the SqlDepedency for a database and implemented in a singleton pattern using unity (singleton on dbName). I also used Microsoft Enterprise Library's data access block for better database code. Let me know what you think.

public class SqlDepedencyBroker
{
    public event EventHandler DataChanged;
    public string Query { get; private set; }
    private string DbName { get; private set; }
    private CancellationToken Token { get; private set; }
    public SqlDepedencyBroker(string query, string dbName = null, CancellationToken token = default(CancellationToken))
    {
        SqlDatabaseDepedency.RegisterDependency(dbName);
        this.Query = query;
        this.DbName = dbName;
        this.Token = token;
    }
    public async Task WatchQuery()
    {
        await Task.Yield();
        SqlDatabase db = this.DbName == null ? DatabaseFactory.CreateDatabase() as SqlDatabase : DatabaseFactory.CreateDatabase(this.DbName) as SqlDatabase;
        if (db == null)
            throw new InvalidOperationException();
        var cmd = db.GetSqlStringCommand(this.Query) as SqlCommand;
        if (cmd == null)
            throw new InvalidOperationException();
        cmd.Notification = null;
        SqlDependency dep = new SqlDependency(cmd);
        dep.OnChange += dep_OnChange;
        Token.ThrowIfCancellationRequested();
        db.ExecuteNonQuery(cmd);
        Token.ThrowIfCancellationRequested();
    }

    void dep_OnChange(object sender, SqlNotificationEventArgs e)
    {
        Token.ThrowIfCancellationRequested();
        var dataChanged = this.DataChanged;
        if (dataChanged != null)
            dataChanged(this, EventArgs.Empty);
        Token.ThrowIfCancellationRequested();
        Task t = WatchQuery();
    }
}
public class SqlDatabaseDepedency
{
    public string DbName { get; private set; }
    private string ConnectionString { get; set; }
    private SqlDatabaseDepedency(string dbName = null)
    {
        var db = dbName != null ? DatabaseFactory.CreateDatabase(dbName) : DatabaseFactory.CreateDatabase();
        if (db == null)
            throw new NotImplementedException();
        ConnectionString = db.ConnectionString;
        SqlDependency.Start(ConnectionString);
    }
    private static Lazy<IUnityContainer> _container = new Lazy<IUnityContainer>(() => new UnityContainer());
    private static IUnityContainer Container
    {
        get { return _container.Value; }
    }
    public static void RegisterDependency(string dbName = null)
    {
        lock (Container)
        {
            if (!Container.IsRegistered<SqlDatabaseDepedency>(dbName))
                Container.RegisterInstance<SqlDatabaseDepedency>(dbName, new SqlDatabaseDepedency(dbName));
        }
    }
    ~SqlDatabaseDepedency()
    {
        SqlDependency.Stop(ConnectionString);
    }
}