轉 SqlServer的查詢通知功能 從sqlserver層面上來解決介面資料的即時更新

SQL Server 2005的推出，使企業級的應用上了一個嶄新的台階，
也使.NET平台的根基進一步穩固，因為其兩者的結合使用使資料庫開發的簡
便性不言而喻，Reporting Services、Notification Services等諸多功能
都讓人心潮澎湃，今天就介紹一下其中的一個功能：查詢通知的使用。
 查詢通知簡單的說就是在應用程式中向資料庫發送一個查詢請求並
請求查詢通知後，資料庫會監視任何執行修改操作的DML，當操作修改了所
監視的資料時，其會嚮應用程式發送訊息告訴程式資料已修改。發送的訊息
將通過sp_DispatcherProc預存程序發給應用程式，應用程式中將以
SqlDependency的OnChang事件接收訊息，然後在OnChang中執行自己想要
的操作。

 要使用查詢通知請參考一下幾個方面：
1、必須在msdb資料庫中的QueryNotificationService服務上相Guest使用者授
予發送許可權，因為在修改資料時，SqlDenpendency對象會使用
ServiceBroker將訊息發送給QueryNotificationService服務。
use msdb
grant send on service::
[http://schemas.microsoft.com/SQL/Notifications/QueryNotificationS
ervice]
to guest
方括弧中的語句像一個url地址，但其實是QueryNotificationService服務
的完整名稱，注意大小寫不能錯。
2、在資料庫伺服器上開啟CLR，因為所用到的一個預存程序
sp_DispatcherProc中的代碼是用.net的代碼寫的。
use master
exec sp_configure 'clr enabled', 1
reconfigure
3、啟用Service Broker
alter database 資料庫名 set ENABLE_BROKER
執行完後，可以用SELECT DATABASEPROPERTYEX('資料庫名',
'IsBrokerEnabled')來驗證是否開啟了Service Broker，返回1表示true，
返回0表示false
4、如果資料庫使用者執行的命令包含通知訂閱請求，則必須為此使用者授予對
執行命令所在資料庫的 SUBSCRIBE QUERY NOTIFICATIONS 許可權。
use dbname
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO database_principal
5、建立通知的主體必須對執行查詢的資料庫具有下列許可權才能使用
SqlDependency 成功建立通知：
CREATE SERVICE
CREATE QUEUE
CREATE PROCEDURE
6、若要接收通知，訂閱使用者必須對訂閱資料庫的
QueryNotificationErrosQueue 具有 RECEIVE 許可權。
GRANT RECEIVE ON QueryNotificationErrosQueue TO login
7、還需要目標裝置的 SEND 許可權：
GRANT SEND ON SERVICE:://theservice to login
8、執行查詢的使用者必須對要訪問通知的表具有 SELECT 許可權。
在程式的代碼上可以察看MSDN，有很詳細的代碼，最重要的一點是select語
句的寫法，必須寫出所有列名，不能使用*，且表名必須使用兩個名稱來定
義，如：dbo.tablename。
如果配置正確，開啟SQL Profiler，運行程式後，可以看見程式向資料庫發
送的包請求。另外需要注意的是SQL Server 2005 Express版不提供查詢通
知，而且將Express版的資料庫移植到正式版後查詢通知似乎有問題，因為
本人就是移植了Express版的資料庫後用了兩天時間也沒把查詢通知做出來
，後來重建資料庫後一切正常。