使用SqlDependency和SqlCacheDependency进行查询通知

• 下载 NotificationMessages - 27.55 KB
• 下载 CacheDependencyNotifier - 2.69 KB

目录

• 引言
  • 什么是 Service Broker？
  • 什么是查询通知？
• 创建 SQL 通知请求的先决条件
  • 通知消息包含什么？
  • 即时检查通知订阅
• Windows 客户端实现
  • 创建通知的步骤 
• ASP.NET Web 客户端实现（缓存依赖）
  • 创建利用通知服务的 ASP.NET Web 客户端应用程序的步骤
• 查询通知有性能开销吗？
• 故障排除
• 历史
• 结论

嗯，自从我上次在 CodeProject 上发帖已经有一段时间了。在此期间，我连续两次获得了 CodeProject MVP（2011 年），今年也获得了 Microsoft MVP 荣誉。获得这个奖项总是令人高兴的，没有你们的支持，我就不会有今天的成就。请阅读这篇文章。

今天我将讨论 SQL Server 2005 中引入的一个有些老但非常有用的功能。我们将讨论 SQL Server 的查询通知功能，然后介绍一种实用的方法来帮助您理解这个概念。

引言

查询通知是一种功能，它允许您订阅一个查询，以便在查询结果更改时，将消息传递给订阅者。SqlDependency 是一个特殊的 .NET 类，它允许您订阅一个事件，当 SQL Server 发送通知时，该类的 OnChange 事件将被调用。另一方面，对于 ASP.NET 应用程序，有一个 SqlCacheDependency 类，它使用相同的技术来使您的应用程序缓存条目在底层数据更改结果时失效。因此，无论哪种方式，您的应用程序都将受益于这个新功能，您无需一遍又一遍地轮询数据库来获取更新。收到通知后，您可以进行一次数据库轮询以获取更新，并重新注册下一次通知。查询通知功能使用 Service Broker 架构将消息发送到外部进程。让我们快速进入主题，为您构建一个应用程序。

什么是 Service Broker？

Service Broker 架构允许您构建松耦合的 SQL Server 实例，以便实例通过常规的消息形式进行通信。Service Broker 使用 TCP/IP 在网络上传输消息，因此允许加密消息。它适用于使用 SQL Server 实例的应用程序，也适用于将工作分布到多个 SQL Server 实例的应用程序。Service Broker 允许使用队列来暂存消息，因此消息会逐个处理，而无需调用方等待接收消息。

什么是查询通知？

查询通知是一项新功能，它允许应用程序等待通知，当底层查询更改其结果时。数据库引擎使用 Service Broker 来传递通知消息。因此，一旦您运行了一个具有通知关联的命令，数据库引擎就会将其注册通知请求并与现有命令进行映射。因此，一旦结果发生更改，就会执行通知。

创建 SQL 通知请求的先决条件

在运行应用程序之前，您需要遵循一些数据库先决条件，以便您的应用程序能够接收所需的通知。让我们逐一讨论：

1. 启用 Service Broker： 通知服务所需的第一件事是为数据库启用 Service Broker。可以使用以下命令在 SQL Server 数据库中启用 Service Broker 功能：
   

   ALTER DATABASE MyDatabase SET ENABLE_BROKER

   启用 Service Broker 后，应用程序就可以从 SQL Server 获取通知消息。

2. 数据库的权限集： 查询通知需要数据库的一些权限。它需要为数据库启用“订阅查询通知”权限。
   

   GRANT SUBSCRIBE QUERY NOTIFICATIONS TO database_principal

   除此之外，它还需要在您希望订阅运行的数据库上授予“创建服务”、“创建队列”和“创建过程”权限。另一方面，为了接收通知，订阅者还必须具有 SEND/RECEIVE 通知权限。

   GRANT RECEIVE ON Table TO login
   GRANT SEND ON SERVICE:://the service to login

   用户还必须授予对 Table 的 SELECT 权限才能使通知正常工作。

3. 识别查询： 最后一步是识别您需要注册通知的查询。查询通知通常是在查询上订阅的，但您也可以将其应用于存储过程，其中允许使用基本的语句，如 IF、Try、Loop 等。尽管如此，在使用此订阅之前，您需要了解一些限制。
   1. 所有列名都必须在查询中显式声明。因此，请使用 Select Col1, Col2 from Table，而不是 select * from Table。因此，选择不能包括 * 和 TableName.*。
   2. 表必须使用两部分名称，使用 dbo.TableName 而不是 TableName。
   3. 不允许使用未命名或重复的列。
   4. 不允许引用具有 ComputedColumns 的表。
   5. 当您需要聚合列订阅时，您必须使用 GROUPBY。不允许使用 Cube、Rollup 或 Having。
   6. 语句不得包含 Pivot 或 Unpivot 运算符。
   7. 不允许使用 Union、Intersect 和 except。
   8. 语句不应包含对视图的引用。
   9. 它不应包含 Distinct、Compute 或 Into。
   10. 查询中不允许使用 NText、Text、Image 类型进行通知。
   11. 不允许使用 Rowset 函数，如 OpenRowset 或 OpenQuery。
   12. 语句不得引用 Service Broker 队列。
   13. 查询中也禁止使用 Top 表达式。
   14. 设置 NoCount ON 将使存储过程中的查询通知失效。
   15. 查询中也必须排除对服务器全局变量（@@variableName）的引用。

   有关完整的限制集，请参阅此处。

通知消息包含什么？

通知消息本质上是一种基于 Soap 的 XML 消息，客户端通过从 Service Broker 队列接收来消费它。查询通知消息的类型为 http://schemas.microsoft.com/SQL/Notifications/QueryNotification。此消息是 http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification 合同的一部分。因此，注册通知服务的服务应绑定到此合同。因此，在客户端收到消息后，客户端应使用 End Conversation 关闭会话。

一个示例查询通知消息可以是：

<qn:QueryNotification
  xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification"
  Type="change" Source="data" Info="insert">
    <qn:Message>http://mysite.microsoft.com/catalog.aspx?Category=Cars</qn:Message>
</qn:QueryNotification>

您可以在此处阅读更多关于查询通知消息的信息。

即时检查通知订阅

有一个特殊的系统 Table 列出了当前存储在数据库中的所有订阅。要检查通知，请尝试以下查询：

select * from sys.dm_qn_subscriptions

您也可以使用以下命令取消订阅：

KILL QUERY NOTIFICATION SUBSCRIPTION 3

其中 3 是订阅 ID。您可以使用订阅 ID 替换为 All 关键字来取消所有订阅。

Windows 客户端实现

正如您已经了解了查询通知的基础知识，让我们来构建一个利用此功能的应用程序。ADO.NET 公开了一个名为 SqlDependency 的类，它将 SqlCommand 放入其构造函数中，并自动为您构建 SqlNotificationRequest。在收到 SQL Server 的通知后，该类会自动调用 OnChange 事件，从而应用程序获得实际通知。

创建通知的步骤

1. 使用 SqlDependency.Start(connectionString) 开始监听特定数据库实例的通知。方法中提供的 ConnectionString 参数标识了实际的数据库实例。
2. 创建一个 SqlCommand 对象，其中包含 CommandText（也支持 StoredProcedure）和一个指向启动执行的相同数据库的 SqlConnection。
3. 使用 SqlNotificationRequest 对象配置 SqlCommand.Notification，或者最好使用 SqlDependency 来创建它。要使用 SqlDependency，请创建一个 SqlDependency 实例，并将命令对象包含在其中。
4. 订阅 SqlDependency 对象的 OnChange 事件以获取查询通知。
5. 收到通知后，将引发 OnChange 事件。该事件接收 SqlDependency 对象作为发送者，SqlNotificationEventArgs 作为事件参数。在 EventHandler 内部，您需要取消注册事件，因为通知是一次性的。另一方面，SqlNotificationEventArgs 接收有关数据、其源等的信息。
6. SqlDependency.Stop(connectionString) 允许您取消当前实例的通知订阅。

    public class SQLNotifier : IDisposable
    {
        public SqlCommand CurrentCommand { get; set; }
        private SqlConnection connection;
        public SqlConnection CurrentConnection
        {
            get
            {
                this.connection = this.connection ?? 
			new SqlConnection(this.ConnectionString);
                return this.connection;
            }
        }
        public string ConnectionString
        {
            get
            {
                return @"Data Source=VALUE-699460DF8\SQLEXPRESS;
                		Initial Catalog=Northwind;Integrated Security=True";
            }
        }

        public SQLNotifier()
        {
            SqlDependency.Start(this.ConnectionString);

        }
        private event EventHandler<SqlNotificationEventArgs> _newMessage;

        public event EventHandler<SqlNotificationEventArgs> NewMessage
        {
            add
            {
                this._newMessage += value;
            }
            remove
            {
                this._newMessage -= value;
            }
        }

        public virtual void OnNewMessage(SqlNotificationEventArgs notification)
        {
            if (this._newMessage != null)
                this._newMessage(this, notification);
        }
        public DataTable RegisterDependency()
        {

            this.CurrentCommand = new SqlCommand("Select [MID],[MsgString],
             			[MsgDesc] from dbo.Message", this.CurrentConnection);
            this.CurrentCommand.Notification = null;

            SqlDependency dependency = new SqlDependency(this.CurrentCommand);
            dependency.OnChange += this.dependency_OnChange;

            if (this.CurrentConnection.State == ConnectionState.Closed)
                this.CurrentConnection.Open();
            try
            {

                DataTable dt = new DataTable();
                dt.Load(this.CurrentCommand.ExecuteReader
			(CommandBehavior.CloseConnection));
                return dt;
            }
            catch { return null; }
        }

        void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            SqlDependency dependency = sender as SqlDependency;

            dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);

            this.OnNewMessage(e);
        }
        public void Insert(string msgTitle, string description)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("usp_CreateMessage", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@title", msgTitle);
                    cmd.Parameters.AddWithValue("@description", description);

                    con.Open();

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
        }

        #region IDisposable Members

        public void Dispose()
        {
            SqlDependency.Stop(this.ConnectionString);
        }

        #endregion
    }

SQLNotifier 是示例应用程序中创建的自定义类，用于演示该功能。在此类中，我们有两种数据库活动。一种是使用 RegisterDependency，它允许您注册查询通知服务，并调用 select 语句获取 DataTable；另一种是 Insert，它调用 usp_CreateMessage 在表中创建一个条目。您可能注意到我在构造函数中使用了 SqlDependency.Start，并在 Dispose 方法中使用了 Stop。该类将在收到通知时向外部环境生成一个 NewMessage 事件。

应用程序非常简单。它是一个 WPF 客户端，显示一个 ListView 来列出表 Message 中的所有数据。SQLNotify 用于通知更改。因此，当调用 Insert 时，即使代码中没有调用 DataLoad，数据也会从通知服务中自动更新。上述 UI 的 XAML 如下所示：

<Grid>
       <Grid.RowDefinitions>
           <RowDefinition Height="Auto"/>
           <RowDefinition Height="*" />
       </Grid.RowDefinitions>
       <StackPanel Grid.Row="0" Orientation="Horizontal" 
       VerticalAlignment="Center">
           <TextBlock Text="Title:" VerticalAlignment="Center" 
           	Margin="10,0,0,0"/>
           <TextBox Text="{Binding Title}"  Width="50"/>
           <TextBlock Text="Description:" VerticalAlignment="Center" 
           	Margin="10,0,0,0"/>
           <TextBox Text="{Binding Description}" Width="100"/>
           <Button Command="{Binding InsertMessage}"  Content="Execute Insert"/>
       </StackPanel>
       <ListView ItemsSource="{Binding Messages}" Grid.Row="1">
           <ListView.View>
               <GridView>
                   <GridViewColumn Header="Id" DisplayMemberBinding="{Binding Id}" />
                   <GridViewColumn Header="Title" 
			DisplayMemberBinding="{Binding Title}" />
                   <GridViewColumn Header="Description" 
			DisplayMemberBinding="{Binding Description}"  />
               </GridView>
           </ListView.View>
       </ListView>
   </Grid>

在 ViewModel 中，我创建了一个 SQLNotifier 对象来处理我的通知服务。该类在收到 QueryNotification 时生成 OnNewMessage 事件。因此，ViewModel 会收到来自 SQLNotifier 类的已注册 eventhandler 的通知，并从数据库更新数据。

public Dispatcher UIDispatcher { get; set; }

public SQLNotifier Notifier { get; set; }
public MessageModel(Dispatcher uidispatcher)
{
    this.UIDispatcher = uidispatcher;
    this.Notifier = new SQLNotifier();

    this.Notifier.NewMessage += new EventHandler<SqlNotificationEventArgs>
				(notifier_NewMessage);
    DataTable dt = this.Notifier.RegisterDependency();

    this.LoadMessage(dt);
}

ViewModel 的构造函数接收 Dispatcher，以确保数据是从 UI 线程更新的。调用 RegisterDependency 会加载数据并将其作为 DataTable 传递。LoadMessage 将数据加载到 UI 控件。

void notifier_NewMessage(object sender, SqlNotificationEventArgs e)
{
   this.LoadMessage(this.Notifier.RegisterDependency());
}

每当收到新消息时，都会再次调用 RegisterDependency，并将数据加载到 UI 上。正如我之前所说，通知是一次性的，因此当收到一个通知时，您需要再次重新注册通知以获取下一个通知。

private void LoadMessage(DataTable dt)
{
    this.UIDispatcher.BeginInvoke((Action)delegate()
    {
        if (dt != null)
        {
            this.Messages.Clear();

            foreach (DataRow drow in dt.Rows)
            {
                Message msg = new Message
                {
                    Id = Convert.ToString(drow["MID"]),
                    Title = drow["MsgString"] as string,
                    Description = drow["MsgDesc"] as string
                };
                this.Messages.Add(msg);
            }
        }
    });
}

LoadMessage 将消息从 datatable 加载到 ObservableCollection，然后绑定到实际的 UI 控件。

要测试应用程序，请运行应用程序，从屏幕上插入数据，您可以看到数据已更新。您也可以运行同一应用程序的另一个实例，或直接通过 Insert 查询在数据库中插入数据，并查看数据已更新到 UI。

ASP.NET Web 客户端实现（缓存依赖）

对于 ASP.NET 应用程序，您也可以使用此功能。System.Web.Caching 中有一个名为 SqlCacheDependency 的类，它允许您在底层数据被修改或收到 SQL Server 查询通知时使缓存失效。因此，您可以在 ASP.NET 中轻松使用此功能来创建更复杂的网站。

缓存对每个 ASP.NET 站点都非常重要。缓存允许您将经常访问的数据存储在所有用户可用的内存中，从而减轻了每个请求加载数据的压力。因此，当网站受到大量访问时，缓存可以使 IIS 快速响应。主要缺点之一是，当数据更新时，如何从数据库加载新数据。查询通知允许您拥有缓存依赖关系，以便在 SQL Server 通知数据已修改时，缓存条目将被删除。因此，在下次基于此的请求中，您可以从数据库重新加载数据。

创建利用通知服务的 ASP.NET Web 客户端应用程序的步骤

1. 创建一个空白的 Web 站点，并添加一个 Default 页面和 Global.asax。

2. 在 Global.asax 的 Application_Start 事件中，使用 SqlDependency.Start 注册 SQL Server 实例以获取通知。Application_Stop 将注销 SqlDependency。

3. 为网页创建 UI，为了简单起见，我们创建了与 WPF 应用程序中看到的相同的网页。保存按钮将数据 Insert 到数据库，而 GridView 将显示数据。

4. 数据是从 Cache.Get 或从数据库加载的，具体取决于缓存中的数据。

string connectionString = 
	ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;

void Application_Start(object sender, EventArgs e)
{
    System.Data.SqlClient.SqlDependency.Start(connectionString);
}

void Application_End(object sender, EventArgs e)
{
    System.Data.SqlClient.SqlDependency.Stop(connectionString);
}

因此，首先我们从 Web.config 中检索 connectionString 并为查询通知注册 SQL 实例。

<div>
        Message Title:
        <asp:TextBox runat="server" ID="txtTitle" />
        <asp:RequiredFieldValidator ControlToValidate="txtTitle" 
        Display="Dynamic"
            runat="server" SetFocusOnError="true" 
            ErrorMessage="Title is left blank" />
        Message Description :
        <asp:TextBox runat="server" ID="txtDescription" />
        <asp:RequiredFieldValidator ControlToValidate="txtDescription" 
        runat="server" SetFocusOnError="true" 
        Display="Dynamic"
            ErrorMessage="Description is left blank" />
        <asp:Button ID="btnSave" runat="server" 
        OnClick="btnSave_Click" Text="Execute Insert" />
        <br />
        <asp:Label ID="lblDate" runat="server" /><br />
        <asp:GridView ID="grdMessages" runat="server">
        </asp:GridView>
    </div>

上述设计非常简单。我使用了两个文本框，一个 button 和一个 Label。Label 显示数据是从哪里检索的。有一个 Grid 显示从数据库获取的 Data。

现在，如果您查看 UI 的代码，它看起来像这样：

    private string ConnectionString = 
    	ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
            return;

        this.BindGrid();

    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (!string.IsNullOrWhiteSpace(this.txtTitle.Text) &&
        			!string.IsNullOrWhiteSpace(this.txtDescription.Text))
            this.Insert(this.txtTitle.Text, this.txtDescription.Text);
        this.BindGrid();

    }

    private void BindGrid()
    {
        DataTable dtMessages = (DataTable)Cache.Get("Messages");

        if (dtMessages == null)
        {
            dtMessages = this.LoadMessages();
            lblDate.Text = string.Format("Last retrieved DateTime : {0}", 
				System.DateTime.Now);
        }
        else
        {
            lblDate.Text = "Data Retrieved from Cache";
        }
        grdMessages.DataSource = dtMessages;
        grdMessages.DataBind();
    }
    private DataTable LoadMessages()
    {
        DataTable dtMessages = new DataTable();

        using (SqlConnection connection = new SqlConnection(this.ConnectionString))
        {
            SqlCommand command = new SqlCommand("Select [MID],
            			[MsgString],[MsgDesc] from dbo.Message", connection);

            SqlCacheDependency dependency = new SqlCacheDependency(command);

            if (connection.State == ConnectionState.Closed)
                connection.Open();

            dtMessages.Load(command.ExecuteReader(CommandBehavior.CloseConnection));

            Cache.Insert("Messages", dtMessages, dependency);
        }

        return dtMessages;
    }
    public void Insert(string msgTitle, string description)
    {
        using (SqlConnection con = new SqlConnection(this.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("usp_CreateMessage", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@title", msgTitle);
                cmd.Parameters.AddWithValue("@description", description);

                con.Open();

                try
                {
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }

代码看起来非常相似。Insert 将数据插入数据库，LoadMessage 将消息加载到 Grid。现在最主要的区别在于对 Cache.Get("Messages") 的调用，它检查缓存中是否存在数据（仅当底层数据未修改时）或需要重新加载数据。基于此，设置了 Label 的文本。在加载消息时，我们将整个 datatable 放入 Cache 中，并以 SqlCacheDependency 作为其依赖参数。正如我之前所说，SqlCacheDependency 将根据 SQL Server 查询通知使缓存条目失效。

查询通知有性能开销吗？

考虑到这一点，我试图查看在订阅通知时查询的性能如何受到影响。让我们看看 SQL Server Management Studio 的查询执行计划，以对此有所了解。

执行计划显示，一次 insert 操作 43% 的成本估计用于通知断言。因此，查询通知在 SQL Server 方面确实存在相关的成本。

故障排除

故障排除是此类应用程序的重要组成部分。我将在此更新我发现的所有故障排除信息。如果您在运行示例时遇到任何问题，请告诉我。

1. 通知的无限循环： 在某些情况下，您的应用程序在注册后会立即收到通知。主要原因是您注册了一个不符合查询通知标准要求的查询，或者您的应用程序从内部重新生成了通知。请确保您使用列名而不是 *，并使用 dbo.TableName 而不是 TableName。
2. 无法获取有关 Windows NT 用户/组的信息： 如果看到此异常，则表示访问通知服务的用户无权访问。尝试运行 ALTER AUTHORIZATION ON DATABASE::Northwind TO sa 来解决此问题。

历史

• 初稿：2011 年 1 月 10 日

结论

尽管我在网上找到了很多关于同一主题的帖子，但没有一个帖子能够完全阐明这一切。所以我想自己发一篇。希望这篇文章能对您有所帮助。请提供您的反馈，以丰富未来的文章。感谢您的阅读。