SqlNotificationRequest 查询通知

Question

我使用sqlnotificatiorequest实现sql server的查询通知功能，在sqlserver 中创建了broker service和 queue。程序中使用

 sqlnotificaitonrequest通过sqlcommand订阅后，可以看到 queue中有一条订阅的消息，但是我执行更改数据库的命令后，却没有

查询通知的消息在queue里，不知道是怎么回事！

Answer 1

請問你的code中，那個Command 執行時，有給SqlNotificationReqest去Command的Notification嗎？

https://technet.microsoft.com/en-us/library/ms190270%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

還有你堤到"更改数据库的命令"，更改了什麼？

---

大家一齊探討、學習和研究，謝謝！
MCSD, MCAD, MCSE+I, MCDBA, MCDST, MCSA, MCTS, MCITP, MCPD,
MCT, Microsoft Community Star(TW & HK),
Microsoft MVP for VB.NET since 2003
My MSMVP Blog

請記得將對您有幫助的回覆標示為解答以幫助其他尋找解答及參與社群討論的朋友們。
Please remember to clickMark as Answer on the post that helps you. This can be beneficial to other community members reading the thread.

Answer 2

感谢您的回复！command中设置了查询语句："SELECT name from tProduct"，并且通过以下的代码进行的订阅。在执行了command.ExecuteReader()后，数据库中的message queue中多了一条记录，我理解应该是订阅成功了。之后我尝试执行一个update tProduct set name ='wang'  where id =2 语句更改了一条记录。根据查询通知的说明，我应该去轮询message queue中的消息记录去发现数据更改的通知消息获取通知，但是我在数据库中始终没有数据更改的消息出现，所以也就无法获取通知了。

                    // Create a SqlNotificationRequest object.
                    SqlNotificationRequest notficationRequest = new SqlNotificationRequest();

                    notficationRequest.Options = "Service=tProductChangeNotifications";
                    notficationRequest.UserData = new Guid().ToString();
                    notficationRequest.Timeout = 0;

                    // Associate the notification request with the command.
                    command.Notification = notficationRequest;
                    // Execute the command.
                    SqlDataReader readerNotification = command.ExecuteReader();

Answer 3

從MSDN上寫著，只有Select和Execute才會記錄下來

You can set up notifications for SELECT and EXECUTE statements. When using an EXECUTE statement, SQL Server registers a notification for the command executed rather than the EXECUTE statement itself. The command must meet the requirements and limitations for a SELECT statement. When a command that registers a notification contains more than one statement, the Database Engine creates a notification for each statement in the batch.

https://msdn.microsoft.com/en-us/library/aewzkxxh(v=vs.80).aspx

---

大家一齊探討、學習和研究，謝謝！
MCSD, MCAD, MCSE+I, MCDBA, MCDST, MCSA, MCTS, MCITP, MCPD,
MCT, Microsoft Community Star(TW & HK),
Microsoft MVP for VB.NET since 2003
My MSMVP Blog

請記得將對您有幫助的回覆標示為解答以幫助其他尋找解答及參與社群討論的朋友們。
Please remember to clickMark as Answer on the post that helps you. This can be beneficial to other community members reading the thread.

Answer 4

应该不是这样，我使用sqldependency，在同样的条件下就是可以，只是sqldependency封装好了，我无法看到消息，但是可以触发通知，这个是sql broker触发的，和应用程序应该没有关系。