我有以下代码使用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 = @""; 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=", 0); ... SqlDependency.Start(connectionString,sqlQueue); ...
进行这些代码更改会导致在运行代码时无法动态创建队列,但是我的代码不再有效,我的应用程序无法识别对我的表/查询所做的更改.
我花了好几天试图解决这个问题没有成功,任何人都可以提供任何建议吗?谢谢.