基于ASP.NET Core MVC的SignalR+SqlDependency用户通知方案咨询

针对你提出的三个核心疑问，直接解答如下：

核心疑问解答 #

1. 多张表是否需要每张创建SqlDependency？ #

不需要强制每张表单独创建。如果多张表的变更触发同类型通知，可以通过一个符合SqlDependency查询规范的联合查询（仅包含需监控字段、明确表名）用单个SqlDependency监听；如果每张表对应不同业务场景的通知（比如订单新增、商品上架），建议单独创建SqlDependency，避免逻辑耦合。注意：SqlDependency的查询不能包含*、TOP、无分组的聚合函数等，需符合SQL Server Service Broker的要求。

2. SqlDependency是否存在性能问题？ #

配置合理的话，性能完全可控。它基于SQL Server Service Broker的事件通知机制，而非轮询，资源消耗很低。需要注意：

• 避免监听过于宽泛的查询，尽量缩小监控范围；
• 通知触发后及时重新注册监听（SqlDependency触发一次后会自动停止）；
• 确保数据库已启用Service Broker，且应用有足够的数据库权限。
  中小项目无需担心性能，高并发场景可考虑批量通知或限流。

3. 如何统计每个客户端的未读通知数量？ #

通过数据库持久化通知状态是最可靠的方案：

1. 建UserNotifications表记录用户的通知内容、是否已读、创建时间；
2. 客户端连接Hub时，查询该用户的未读记录数并返回；
3. 数据库变更触发新通知时，插入未读记录并通过SignalR推送给用户，同时更新客户端计数；
4. 用户标记通知已读后，调用Hub方法更新数据库状态，同步客户端计数。

实现步骤与代码示例 #

1. 数据库准备 #

首先启用数据库的Service Broker（执行一次即可）：

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

创建通知记录表：

CREATE TABLE UserNotifications (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    UserId NVARCHAR(450) NOT NULL, -- 关联AspNetUsers的Id
    NotificationContent NVARCHAR(500) NOT NULL,
    IsRead BIT DEFAULT 0,
    CreatedTime DATETIME DEFAULT GETDATE()
);

-- 索引优化查询
CREATE INDEX IX_UserNotifications_UserId_IsRead ON UserNotifications(UserId, IsRead);

2. 项目配置（Program.cs） #

添加SignalR和SqlDependency的配置：

var builder = WebApplication.CreateBuilder(args);

// 注册SignalR服务
builder.Services.AddSignalR();

// 配置数据库上下文
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString));

// 初始化SqlDependency
SqlDependency.Start(connectionString);

// 注册数据库监听服务
builder.Services.AddSingleton<DatabaseChangeMonitor>();

var app = builder.Build();

// 配置SignalR路由
app.MapHub<NotificationHub>("/notificationHub");

app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

3. 实现SignalR Hub #

using Microsoft.AspNetCore.SignalR;
using Microsoft.EntityFrameworkCore;

public class NotificationHub : Hub
{
    private readonly ApplicationDbContext _dbContext;

    public NotificationHub(ApplicationDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    // 客户端连接时获取未读通知数
    public async Task<int> GetUnreadCount()
    {
        var userId = Context.UserIdentifier;
        if (string.IsNullOrEmpty(userId)) return 0;

        return await _dbContext.UserNotifications
            .CountAsync(n => n.UserId == userId && !n.IsRead);
    }

    // 标记通知为已读
    public async Task MarkAsRead(int notificationId)
    {
        var userId = Context.UserIdentifier;
        var notification = await _dbContext.UserNotifications
            .FirstOrDefaultAsync(n => n.Id == notificationId && n.UserId == userId);
        
        if (notification != null)
        {
            notification.IsRead = true;
            await _dbContext.SaveChangesAsync();
            await Clients.User(userId).SendAsync("UpdateUnreadCount", await GetUnreadCount());
        }
    }

    // 推送新通知到指定用户（内部调用）
    public async Task SendNotificationToUser(string userId, string content)
    {
        var notification = new UserNotification
        {
            UserId = userId,
            NotificationContent = content
        };
        _dbContext.UserNotifications.Add(notification);
        await _dbContext.SaveChangesAsync();

        await Clients.User(userId).SendAsync("ReceiveNotification", content);
        await Clients.User(userId).SendAsync("UpdateUnreadCount", await GetUnreadCount());
    }
}

// 数据库实体类
public class UserNotification
{
    public int Id { get; set; }
    public string UserId { get; set; }
    public string NotificationContent { get; set; }
    public bool IsRead { get; set; }
    public DateTime CreatedTime { get; set; }
}

4. 实现数据库变更监听服务 #

using Microsoft.AspNetCore.SignalR;
using Microsoft.EntityFrameworkCore;
using System.Data.SqlClient;

public class DatabaseChangeMonitor
{
    private readonly string _connectionString;
    private readonly IHubContext<NotificationHub> _hubContext;
    private readonly ApplicationDbContext _dbContext;

    public DatabaseChangeMonitor(IConfiguration configuration, IHubContext<NotificationHub> hubContext, ApplicationDbContext dbContext)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
        _hubContext = hubContext;
        _dbContext = dbContext;
        StartMonitoring();
    }

    private void StartMonitoring()
    {
        // 监听Orders表插入事件
        MonitorTable("Orders", "SELECT Id, CustomerId FROM dbo.Orders", async (sender, e) =>
        {
            if (e.NotificationType == SqlNotificationType.Change)
            {
                var newOrder = await _dbContext.Orders.OrderByDescending(o => o.Id).FirstOrDefaultAsync();
                if (newOrder != null)
                {
                    await _hubContext.Clients.User(newOrder.CustomerId.ToString())
                        .SendAsync("ReceiveNotification", $"您有新订单：编号{newOrder.Id}");
                    await _hubContext.GetHub<NotificationHub>().SendNotificationToUser(newOrder.CustomerId.ToString(), $"您有新订单：编号{newOrder.Id}");
                }
                StartMonitoring(); // 重新注册监听
            }
        });

        // 监听Products表插入事件
        MonitorTable("Products", "SELECT Id, ProductName FROM dbo.Products", async (sender, e) =>
        {
            if (e.NotificationType == SqlNotificationType.Change)
            {
                await _hubContext.Clients.All.SendAsync("ReceiveNotification", "有新商品上架");
                StartMonitoring(); // 重新注册监听
            }
        });
    }

    private void MonitorTable(string tableName, string query, OnChangeEventHandler onChange)
    {
        using var connection = new SqlConnection(_connectionString);
        connection.Open();

        using var command = new SqlCommand(query, connection);
        var dependency = new SqlDependency(command);
        dependency.OnChange += onChange;

        command.ExecuteReader(); // 执行查询触发监听
    }
}

5. 前端视图代码（Razor） #

@{
    ViewData["Title"] = "通知中心";
}

<div class="container mt-4">
    <h2>通知中心</h2>
    <div class="alert alert-secondary">未读通知数：<span id="unreadCount" class="badge bg-danger">0</span></div>
    <div id="notificationList" class="mt-3"></div>
</div>

<script src="~/lib/signalr/dist/browser/signalr.js"></script>
<script>
    const connection = new signalR.HubConnectionBuilder()
        .withUrl("/notificationHub")
        .build();

    // 接收新通知
    connection.on("ReceiveNotification", function (content) {
        const list = document.getElementById("notificationList");
        const item = document.createElement("div");
        item.className = "alert alert-info mb-2";
        item.textContent = content;
        list.prepend(item);
    });

    // 更新未读计数
    connection.on("UpdateUnreadCount", function (count) {
        document.getElementById("unreadCount").textContent = count;
    });

    // 启动连接并获取初始未读数
    async function startConnection() {
        try {
            await connection.start();
            const count = await connection.invoke("GetUnreadCount");
            document.getElementById("unreadCount").textContent = count;
        } catch (err) {
            console.error(err);
            setTimeout(startConnection, 5000);
        }
    }

    startConnection();
</script>

内容的提问来源于stack exchange，提问作者Katakyie Kofi Poku