c# - 如何在 .NET Core 3.1 中正确使用 SignalR 和 SQLTableDependency 和 Razor 页面？

Question

我会说我对SignalRand还是很陌生SqlTableDependency。在过去的两周里，我一直在努力掌握这些主题，阅读我能找到的每一篇博客文章、微软文档或 YouTube 视频。起初，我SignalR独自工作，只是制作了一个示例 Razor Pages 应用程序并阅读了 Microsoft 文档：ASP.NET Core SignalR 入门。这对我来说很好。

所以然后我尝试使用，无论我查找什么教程，我什至SqlDependency都无法触发事件。OnChange所以我放弃了，SqlTableDependency转而使用，查找有关如何使用它的任何资源。然后我的OnChange事件按预期进行，但随后我又遇到了一些问题。在我展示我的代码后，我将进入。

基本上，我有一个带有聊天室的聊天应用程序。我正在努力做到这一点，每当用户加入房间时，查看房间信息的每个其他用户都将自动从数据库中看到更新的用户计数，而无需刷新页面。

到目前为止，这是我的代码：

所以首先，我ENABLE_BROKER在我的数据库中设置并创建了一个新用户

ALTER DATABASE ChattingApp SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE ChattingApp SET ENABLE_BROKER; 
ALTER DATABASE ChattingApp SET MULTI_USER WITH ROLLBACK IMMEDIATE

CREATE LOGIN [ChattingUser] WITH PASSWORD=N'WhateverPasswordIPutHere', 
            DEFAULT_DATABASE=[ChattingApp], CHECK_EXPIRATION=OFF, 
            CHECK_POLICY=OFF

CREATE USER [ChattingUser] FOR LOGIN [ChattingUser]
WITH DEFAULT_SCHEMA=[dbo]

GRANT ALTER TO [ChattingUser]
GRANT CONNECT to [ChattingUser]
GRANT CONTROL to [ChattingUser]
GRANT CREATE CONTRACT to [ChattingUser]
GRANT CREATE MESSAGE TYPE to [ChattingUser]
GRANT CREATE TABLE to [ChattingUser]
GRANT CREATE PROCEDURE to [ChattingUser]
GRANT CREATE QUEUE to [ChattingUser]
GRANT CREATE SERVICE to [ChattingUser]
GRANT EXECUTE to [ChattingUser]
GRANT SELECT to [ChattingUser]
GRANT SUBSCRIBE QUERY NOTIFICATIONS to [ChattingUser]
GRANT VIEW DATABASE STATE to [ChattingUser]
GRANT VIEW DEFINITION to [ChattingUser]

我的集线器类：ChatProxy.cs

public class ChatProxy : Hub {

}

Startup.cs

public class Startup {
        public Startup(IConfiguration configuration) {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services) {
            services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();

            services.Configure<KestrelServerOptions>(options => {
                options.AllowSynchronousIO = true;
            });

            services.AddControllersWithViews().AddRazorRuntimeCompilation();

            services.AddSession(options => {
                options.IdleTimeout = TimeSpan.FromMinutes(30);
                options.Cookie.IsEssential = true;
            });

            services.AddSignalR();

            services.AddTransient<IChattingController, ChattingController>();
            services.AddTransient<IChatRoomManager, ChatRoomManager>();
            services.AddTransient<IChatServerManager, ChatServerManager>();

            services.AddAntiforgery(o => o.HeaderName = "RequestVerificationToken");

            services.AddMemoryCache();
            services.AddRazorPages();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env) {
            if (env.IsDevelopment()) {
                app.UseDeveloperExceptionPage();
            } else {
                app.UseExceptionHandler("/Error");
                // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
                app.UseHsts();
            }

            app.UseHttpsRedirection();
            app.UseStaticFiles();
            app.UseSession();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints => {
                endpoints.MapRazorPages();
                endpoints.MapHub<ChatProxy>("/chatHub");
            });
        }
    }

我的javascript类：enterroom.js

"use strict";

var script = document.createElement('script');
script.src = 'https://code.jquery.com/jquery-3.4.1.min.js';
script.type = 'text/javascript';
document.getElementsByTagName('head')[0].appendChild(script);

var connection = new signalR.HubConnectionBuilder().withUrl("/chatHub").build();

connection.start().then(function () {
    console.log("Custom message: Connection started successfully");
}).catch(function (err) {
    console.log("Custom message: connection.start() threw an error");
    return console.error(err.toString());
});

connection.on("RecieveChatUpdates", function () {
    var url = new URL(window.location.href);
    var params = url.searchParams;
    params.set("handler", "RefreshServerInfo");
    url.search = params.toString();
    var newUrl = url.toString();

    $.ajax({
        type: "POST",
        url: newUrl,
        beforeSend: function (xhr) {
            xhr.setRequestHeader("RequestVerificationToken",
                $('input:hidden[name="__RequestVerificationToken"]').val());
        },
        success: function (response) {
            console.log(response);
        },
        failure: function (error) {
            console.log(error);
        }
    })

    console.log("Custom message: RecieveChatUpdates");
});

我的数据库类与我正在使用的连接字符串：DatabaseAccess.cs

public class DatabaseAccess {
        public string RetrieveConnectionString() {
            return @"Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=ChattingApp;Persist Security Info=False;Integrated Security=False;User Id=ChattingUser;Password=ThePasswordIUsedToSetUpMyUser";
        }

        public string RetrieveSharedConnectionString() {
            return @"Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=ChattingApp;Persist Security Info=False;Integrated Security=False;User Id=ChattingUser;Password=ThePasswordIUsedToSetUpMyUser;MultipleActiveResultSets = true;";
        }
    }

NotificationEventArgs.cs

public class NotificationEventArgs : EventArgs {
        public RecordChangedEventArgs<ChatServer> ChatServerArgs { get; private set; }

        public NotificationEventArgs (RecordChangedEventArgs<ChatServer> chatServerArgs) {
            this.ChatServerArgs = chatServerArgs;
        }
    }

MessageNotifications.cs

public delegate void NotificationEventHandler(ChatServer chatServer, NotificationEventArgs e);
    public class MessageNotifications : IDisposable {
        private static SqlTableDependency<ChatServer> sqlTableDependency;
        private const string TableName = "ChatServer";

        public MessageNotifications(string connectionString) {
            var mapper = new ModelToTableMapper<ChatServer>();
            mapper.AddMapping(cs => cs.ServerNo, "ServerNo")
                  .AddMapping(cs => cs.ServerStarted, "ServerStarted")
                  .AddMapping(cs => cs.CurrentServerOccupancy, "CurrentServerOccupancy");

            sqlTableDependency = new SqlTableDependency<ChatServer>(connectionString, TableName, mapper: mapper);
            sqlTableDependency.OnChanged += HandleOnChanged;
            sqlTableDependency.Start();
        }

        ~MessageNotifications() {
            Dispose();
        }

        public event NotificationEventHandler OnNewMessage;

        // you need to remove your listeners from an event before you destroy a class instance
        private void HandleOnChanged(object sender, RecordChangedEventArgs<ChatServer> e) {
            if (e.ChangeType == ChangeType.Insert ||
                e.ChangeType == ChangeType.Update ||
                e.ChangeType == ChangeType.Delete) {
                OnNewMessage?.Invoke(e.Entity, new NotificationEventArgs(e));
                //sqlTableDependency.OnChanged -= HandleOnChanged;
            }
        }

        private bool disposedValue = false;

        protected virtual void Dispose(bool disposing) {
            if (!disposedValue) {
                if (disposing && sqlTableDependency != null) {
                    sqlTableDependency.Stop();
                    sqlTableDependency.Dispose();
                }

                disposedValue = true;
            }
        }
        public void Dispose() {
            Dispose(true);
        }

    }

我的 ViewModel 类 Rooms.cshtml.cs

public class RoomsModel : PageModel {
    public string Alert { get; private set; }
        public string Username { get; private set; }
        public List<ChatRoom> Rooms { get; private set; } = new List<ChatRoom>();
        public ChatRoom RoomInfo { get; private set; } = new ChatRoom();
        public ChatServer ServerInfo { get; private set; } = new ChatServer();
        public bool ViewingServers { get; private set; }
        public readonly bool ViewingOwnedChatRooms = false;

        private readonly IChattingController _controller;
        private readonly IHubContext<ChatProxy> _context;
        private readonly MessageNotifications notifications;

        public RoomsModel(IChattingController controller, IHubContext<ChatProxy> context) {
            this._controller = controller;
            this._context = context;
            notifications = new MessageNotifications(new DatabaseAccess().RetrieveConnectionString());
            notifications.OnNewMessage += Rooms_OnEnteredRoom;
        }

        private void Rooms_OnEnteredRoom(ChatServer server, NotificationEventArgs e) {
            _context.Clients.All.SendAsync("RecieveChatUpdates");
        }

        public IActionResult OnGet() {
            Username = HttpContext.Session.GetString("Username");
            if (string.IsNullOrEmpty(Username)) {
                return RedirectToPage("/Account/Login");
            } else {
                ViewingServers = false;

                int index = 0;
                bool viewingOwnedChatRooms = false;
                List<ChatRoom> currentRoomList = new List<ChatRoom>();
                Rooms = _controller.DisplayChatRooms(ref currentRoomList, ref index, viewingOwnedChatRooms, ViewingServers, Username, null, false);

                HttpContext.Session.SetObject("Rooms", Rooms);

                HttpContext.Session.SetObject("ConnectionStarted", false);

                return Page();
            }
        }

        public IActionResult OnPostRefreshServerInfo() {
            ViewInfo();
            return Page(); // I've also tried return new JsonResult(Rooms)
        }

        public IActionResult OnPostViewInfo() {
            ViewInfo();
            return Page();
        }

        private void ViewInfo() {
            Username = HttpContext.Session.GetString("Username");
            ViewingServers = true;

            List<ChatRoom> currentRoomList = new List<ChatRoom>();
            string chatroomName = Request.Query["chatroom"];
            int index = 0;
            Rooms = _controller.DisplayChatRooms(ref currentRoomList, ref index, ViewingOwnedChatRooms, ViewingServers, Username, chatroomName, false);
            Rooms[0].ChatroomDescription = Request.Query["description"];
            Rooms[0].MaxServerOccupancy = Convert.ToInt32(Request.Query["maxoccupancy"]);
            Rooms[0].CurrentUsersInRoom = Convert.ToInt32(Request.Query["usersinroom"]);
            RoomInfo = Rooms[0];

            HttpContext.Session.SetObject("RoomInfo", RoomInfo);
        }
}

我还引用enterroom.js了所有其他SignalRjavascript 类Rooms.cshtml

<script src="~/js/signalr/dist/browser/signalr.js"></script>
<script src="~/js/enterroom.js"></script>

我也@Html.AntiForgeryToken()用于我的 ajax 调用

另外，我ChattingController使用的课程Rooms.cshtml.cs不是MVC Controller. 它只是我创建的一个业务逻辑类，我在其中做我的SqlConnection事情ExecuteReader()。我的名字中只有这个词Controller，因为我觉得它好像是一个标准，用Controllerand附加业务逻辑类名称Manager。只是想在我走得更远之前澄清这一点。

因此，到目前为止，我遇到了几个问题。每次我对我的数据库表进行更改（基本上是通过运行附加房间用户计数的存储过程），页面都不会刷新。还有一点，就是会碰到异常就行了：SqlTableDependency.Start()在我的MessageNotifications.cs班上

这是我得到的异常：
Exception Message
Exception Message With More Details

不知何故，我也能够在异常之后继续，并在我的控制台中得到结果。

这是return Page() RecieveChatUpdates_1

这是return new JsonResult(Rooms) RecieveChatUpdates_2

在这一点上，我不知道该怎么做，我觉得我已经尝试了一切。任何帮助将不胜感激。

标签： c#asp.net-corerazor-pagesasp.net-core-signalrsqldependency