监视与 sql依赖项和信号器相关或无关的多个表

Question

我正在尝试使用 signalR 监视多个表。这些表是相关的，但我需要所有表中的所有列来显示。我在一张桌子上看到了SQldependcy。如何为多个表实现它？这是为多个表实现 sqldependecy 和信号器的正确方法吗？在数据库中有不同的表，每个表都有ForiegnKey master--->submaster--->detail。请指教！

        var masterpc = new List<master_Table>();
        var submaster = new List<submaster_Table>();
        var detail = new List<detail_Table>();
        using (SqlConnection connection = new SqlConnection(regularConnectionString))
        {
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                connection.Open();
                //var dependency = new SqlDependency(command);
                //dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                // NOTE: You have to execute the command, or the notification will never fire.
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    masterpc.Add(item: new master_Table
                    {
                        MasterKeyId = (int)reader["MasterKeyId"],
                        Master_Name = (string)reader["Master_Name"],
                        Master_IP = reader["Master_IP"] != DBNull.Value ? (string)reader["Master_IP"] : "",
                        Master_Valid = (bool)reader["Master_Valid"],
                    });
                    count++;
                }
                masterViewModel.masterpc_info = masterpc;
            }
        }
        count = 0;
        using (SqlConnection connection = new SqlConnection(regularConnectionString))
        {
            commandText = "select * from submaster where master_Valid=1 and masterKeyId in(select masterKeyId from masterpc_table where id=24) ";
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                connection.Open();
                //var dependency = new SqlDependency(command);
                //dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                // NOTE: You have to execute the command, or the notification will never fire.
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    submaster.Add(item: new submaster_table
                    {
                        SubmasterKeyId = (int)reader["SubmasterKeyId"],
                        submaster_Type = (string)reader["submaster_Type"],
                        submaster_SN = reader["submaster_SN"] != DBNull.Value ? (string)reader["submaster_SN"] : "",
                        masterPCKeyId = (int)reader["masterPCKeyId"],
                    });
                    count++;
                }
                masterconfigViewModel.submasterinfo = submaster;
            }
        }
        using (SqlConnection connection = new SqlConnection(regularConnectionString))
        {
            commandText = "select * from detail where submasterKeyId in(select submasterkeyid from masterpc_table where id=24) ";
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                connection.Open();
                var dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                // NOTE: You have to execute the command, or the notification will never fire.
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    detail.Add(item: new detail_table
                    {
                        detailkeyid = (int)reader["detailkeyid"],
                        detail_Type = (string)reader["detail_Type"],
                        detail_status = reader["detail_status"] != DBNull.Value ? (string)reader["detail_status"] : "",
                        submasterkeyid = (int)reader["submasterkeyid"],
                    });
                    count++;
                }
                masterconfigViewModel.detailinfo = detail;
            }
        }

Answer 1

这可能有点晚了，但您可能认为以下选项适合您。

称为 SqlDependencyEx 的项目

https://github.com/dyatchenko/ServiceBrokerListener

如何用于多个表

您需要做的就是创建具有不同标识如下所示：

var listener1 = new SqlDependencyEx(connectionString, "YourDatabase", "YourTable1", identity: 1);
var listener2 = new SqlDependencyEx(connectionString, "YourDatabase", "YourTable2", identity: 2);