为什么我的 SqlDependency 没有触发
我有一个在 MS SQL Server 2005 上运行的数据库和一个 ASP.NET 3.5 Web 应用程序。
该数据库包含一个产品目录，我用它来将“页面”输入到在 Web 应用程序中运行的 CMS 中。创建页面后，应用程序会缓存它们，因此我需要将此更改通知应用程序，以便它可以重新创建页面对象。
CMS 使用它自己的缓存，因此不能使用 SqlCacheDependency 来执行此任务。
当我启动应用程序时，我确实在结果中出现了一个订阅者
select * from sys.dm_qn_subscriptions
，但是一旦我向表中添加一些数据，订阅者就会消失，并且 OnChanged 事件永远不会触发。
在我的启动代码中我有以下内容
// Ensure the database is setup for notifications
SqlCacheDependencyAdmin.EnableNotifications(DataHelper.ConnectionString);
SqlCacheDependencyAdmin.EnableTableForNotifications(DataHelper.ConnectionString, "Category");
SqlCacheDependencyAdmin.EnableTableForNotifications(DataHelper.ConnectionString, "Product");
if (!SqlDependency.Start(DataHelper.ConnectionString, SqlDependencyQueueName))
throw new Exception("Something went wrong");
string queueOptions = string.Format("service = {0}", SqlDependencyQueueName);
using (var connection = new SqlConnection(DataHelper.ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(@"SELECT [CategoryID],[Name]
FROM [dbo].[Category]", connection))
{
// Create a dependency and associate it with the SqlCommand.
dependency = new SqlDependency(command, queueOptions, 0);
// Subscribe to the SqlDependency event.
dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
command.ExecuteReader().Close();
}
}
// ...
void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
Debugger.Break(); // This never hits
this.ReloadData();
}
I have a database running on MS SQL Server 2005 and an ASP.NET 3.5 web application.
The database contains a product catalog which I'm using to feed "pages" into a CMS running in the web app. Once the pages have been created the application caches them so I need to notify the application of this change so it can recreate the page objects.
The CMS uses it's own caching so an SqlCacheDependency cannot be used to perform this task.
When I fire the app up I do get a subscriber appearing in the result of
select * from sys.dm_qn_subscriptions
But as soon as I add some data to the table, the subscriber disappears and the OnChanged event never fires.
In my startup code I have the following
// Ensure the database is setup for notifications
SqlCacheDependencyAdmin.EnableNotifications(DataHelper.ConnectionString);
SqlCacheDependencyAdmin.EnableTableForNotifications(DataHelper.ConnectionString, "Category");
SqlCacheDependencyAdmin.EnableTableForNotifications(DataHelper.ConnectionString, "Product");
if (!SqlDependency.Start(DataHelper.ConnectionString, SqlDependencyQueueName))
throw new Exception("Something went wrong");
string queueOptions = string.Format("service = {0}", SqlDependencyQueueName);
using (var connection = new SqlConnection(DataHelper.ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(@"SELECT [CategoryID],[Name]
FROM [dbo].[Category]", connection))
{
// Create a dependency and associate it with the SqlCommand.
dependency = new SqlDependency(command, queueOptions, 0);
// Subscribe to the SqlDependency event.
dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
command.ExecuteReader().Close();
}
}
// ...
void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
Debugger.Break(); // This never hits
this.ReloadData();
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
检查您的数据库
sys.transmission_queue。您的通知很可能会在那里，但由于无法发送而被保留。transmission_status将解释为什么会发生这种情况。有关更详细的故障排除指南，请参阅故障排除对话框。最常见的问题是由于孤立数据库 dbo 所有权无法满足 EXECUTE AS 基础设施要求，可以通过以下方式解决：
但是，解决方案取决于具体情况，具体取决于实际问题，如上所述。
Check your database
sys.transmission_queue. Most likely your notification(s) will be there, retained because they cannot be delivered. Thetransmission_statuswill have an explanation why is this happening. For a more detailed troubleshooting guide, see Troubleshooting Dialogs.The most often issue is due to EXECUTE AS infrastructure requirements not being satisfied by an orphaned database dbo ownership and can be resolved via:
however the solution depends from case to case, depending on the actual problem, as investigated above.
您的选择是错误的。您必须明确说明查询。
来自微软 SQL 文档：
Your Select is wrong. You must explicity state the Query.
From Microsoft SQL Docs: