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