SqlDependency事件未触发

本文关键字：事件 SqlDependency | 更新日期: 2023-09-27 18:11:52

很多人在做这个工作时似乎都有问题，所以我对这里并不陌生，希望有人能指出我还可以检查什么，这就是我正在做的…

运行环境:.net 4.0， SQL Server express 2008 R2

设置数据库的步骤 

CREATE QUEUE WebSiteCacheMessages ;
CREATE SERVICE WebCacheNotifications ON QUEUE WebSiteCacheMessages
  ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]) ;
CREATE ROUTE WebCacheMessagesRoute WITH SERVICE_NAME = 'WebCacheNotifications',ADDRESS = 'LOCAL' ;
EXEC sp_configure 'show advanced options', '1'
GO
RECONFIGURE
GO
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
ALTER DATABASE EFTest SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO     
ALTER AUTHORIZATION ON DATABASE::[EFTEST] TO [domain'user];
GO
下面的代码测试通知 
     static void Main(string[] args)
        {
            var id = 0;
            string sqlStatement = "SELECT [Id] ,[FirstName],[LastName],[Email] FROM [dbo].[People]";
            string queueName = "WebSiteCacheMessages";
            string connectionString = ConfigurationManager.ConnectionStrings["MyDataContext"].ConnectionString;
            try
            {
                SqlDependency.Start(connectionString, queueName);
                using (var db = new DataContext(connectionString))
                {
                    var person = new Person { Email = "test@test.com", FirstName = "Testy", LastName = "Tester", HomeAddress = address };
                    db.Persons.Add(person);
                    db.SaveChanges();
                    id = person.Id;
                }
                var dependency = new SqlDependency();
                dependency.AddCommandDependency(new SqlCommand(sqlStatement));
                dependency.OnChange += (o, e) =>
                {
                    Console.WriteLine("Notification called !");
                };
                Console.ReadLine();
            }
            finally
            {
                SqlDependency.Stop(connectionString, queueName);
            }
        }

当我在SQL server的查询窗口中对email列运行update语句时，我没有看到一个change事件被触发。

事件日志或数据库日志中没有事件，当我运行以下查询时，我没有看到任何

select * from WebSiteCacheMessages -- queue name
select * from sys.transmission_queue

SqlDependency事件未触发

您实际上不会执行任何带有SqlDependency的命令。您只需将SqlDependency与SqlCommand关联起来，但实际上并不执行 SqlCommand。只有在执行SQL语句时，服务器上的查询通知才会创建。比如:

var dependency = new SqlDependency();
var SqlCommand cmd = new SqlCommand(sqlStatement);
dependency.AddCommandDependency(cmd);
dependency.OnChange += (o, e) =>
{
    Console.WriteLine("Notification called !");
};
// Executing the command will submit the query notification request
using (SqlDataReader rdr = cmd.ExecuteReader ()) {
   while (rdr.Reader ()) {
      ...
   }
}
Console.Reade ();

另一种选择是使用LinqToCache并查询您的DataContext.Persons:

var people = from p in db.Persons select new {p.Id, p.LastName, p.FirstName, p.Email};
var peopleCached = people.AsCached("Persons", new QueryCachedOptions () {
  OnInvalidate = (sender, args) => {
    Console.WriteLine("Notification called !");
  }
 });
// Again, the underlying SqlCommand must actually be executed. Iterate the query
foreach (p in peopleCached ) {
 ....
}
相关文章：