C#.NET 和 SQL Server 2012 的 SQL 依赖关系

引言

在本技巧中，我们将实现一个 C#.NET 解决方案，该方案在特定表发生更改时，会从 SQL Server 数据库表中接收通知。

在本技巧中，我将介绍在 SQL Server 2012 中需要完成的各种配置步骤以及相关的 C#.NET 代码。

背景

使用 SQLDependency 的目的是避免轮询数据库以检查是否发生任何更改。每次表发生更改（insert、delete 等）时，都会向应用程序发送通知。

在 SQL Server 中创建 Service Broker

使用 SQL 依赖关系的第一步是创建一个队列并为特定表启用 Service Broker。我已经创建了一个名为 user 的表，其中包含 ID 和 name 列。必须执行以下 SQL 脚本才能为 User 表启用 Service Broker。

CREATE QUEUE SQLDependencyQueue;
CREATE SERVICE SQLDependencyService ON QUEUE SQLDependencyQueue; 
ALTER DATABASE TestBase SET ENABLE_BROKER with immediate rollback;

我们可能需要通过执行以下操作授予用户查询通知权限

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUserName;

现在配置完成，我们可以开始使用 C# 代码的有趣部分了。

我创建了一个通用类以获得更大的灵活性。每次发生更改时，通用类都会拦截事件，该事件将转发给适当的侦听器。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Security.Permissions;
using System.Data;
using System.Collections;

namespace ConsoleApplication3
{
    //type T must have default constructor
    class Notify<T> where T : new()
    {
        //assign connection string and sql command for listening 
        public Notify(string ConnectionString, string Command)
        {
            this.ConnectionString = ConnectionString;
            CollectionReturn = new List<T>();
            this.Command = Command;
            this.NotifyNewItem();
        }
        //event handler to notify the calling class
        public event EventHandler ItemReceived;
        private bool isFirst = true;
        public string ConnectionString { get; set; }
        public string Command { get; set; }
        //rows to return as a collection 
        public List<T> CollectionReturn { get; set; }
        //check if user has permission 
        private bool DoesUserHavePermission()
        {
            try
            {
                SqlClientPermission clientPermission = 
                       new SqlClientPermission(PermissionState.Unrestricted);
                clientPermission.Demand();
                return true;
            }
            catch
            {
                return false;
            }
        }
        //initiate notification 
private void NotifyNewItem()
        {
            if (DoesUserHavePermission())
            {
                if (isFirst)
                {
                    SqlDependency.Stop(ConnectionString);
                    SqlDependency.Start(ConnectionString);
                }
                try
                {
                    using (SqlConnection conn = new SqlConnection(ConnectionString))
                    {
                        using (SqlCommand com = new SqlCommand(Command, conn))
                        {
                            com.Notification = null;
                            SqlDependency dep = new SqlDependency(com);
                            //subscribe to sql dependency event handler
                            dep.OnChange += new OnChangeEventHandler(dep_OnChange);
                            conn.Open();
                            using (var reader = com.ExecuteReader())
                            {
                                //convert reader to list<T> using reflection 
                                while (reader.Read())
                                {
                                    var obj = Activator.CreateInstance<T>();
                                    var properties = obj.GetType().GetProperties();
                                    foreach (var property in properties)
                                    {
                                        if (reader[property.Name] != DBNull.Value)
                                        {
                                            property.SetValue(obj, reader[property.Name], null);
                                        }
                                    }
                                    CollectionReturn.Add(obj);
                                }
                            }      }
                    }
                }
                catch (Exception ex)
                {
                    //Console.WriteLine(ex.Message);
                } 
           }
        }
        //event handler
        private void dep_OnChange(object sender, SqlNotificationEventArgs e)
        {
            isFirst = false;
            var sometype = e.Info;
            //call notify item again 
            NotifyNewItem();
            //if it s an insert notify the calling class 
            if (sometype == SqlNotificationInfo.Insert)
                onItemReceived(e);
            SqlDependency dep = sender as SqlDependency;
            //unsubscribe 
            dep.OnChange -= new OnChangeEventHandler(dep_OnChange);
        }
        private void onItemReceived(SqlNotificationEventArgs eventArgs)
        {
            EventHandler handler = ItemReceived;
            if (handler != null)
                handler(this, eventArgs);
        }
    }
}

要使用该类，我们只需要创建一个实例，指定连接字符串和查询命令，并订阅该事件。

尽管如此，使用 SQLDependency 还是有一些规则（有关完整列表，请参阅 此处）

• SELECT 语句中投影的列必须显式声明，并且表名必须使用两部分名称进行限定。请注意，这意味着语句中引用的所有表必须位于同一数据库中。
• 该语句不能使用星号 (*) 或 table_name.* 语法来指定列。
• 该语句不能使用未命名的列或重复的列名。
• 该语句必须引用基本表。
• 该语句不能引用包含计算列的表。
• SELECT 语句中投影的列不能包含聚合表达式，除非该语句使用 GROUP BY 表达式。当提供 GROUP BY 表达式时，选择列表可以包含聚合函数 COUNT_BIG() 或 SUM()。但是，不能为可为空列指定 SUM() 。该语句不能指定 HAVING、CUBE 或 ROLLUP。

我希望您会喜欢我的第一个技巧，并希望为这个社区做出更多贡献。