Monitor multiple tables related or non-related with sqldependency and signalr

Question

I am trying to monitor multiple tables using signalR. These tables are related, but I need all columns from all tables for display purposes. I have seen SQldependcy on one table. How to implement it for multiple tables? Is this the right way to implement sqldependecy and signalr for multiple tables? In the database there are different tables with each having the ForiegnKey master--->submaster--->detail. Please suggest!

        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

This might be little late to answer, but you might think the following option suitable for you.

Project known as SqlDependencyEx

https://github.com/dyatchenko/ServiceBrokerListener

How to use for multiple tables

All you need to do is to create multiple listeners with different identities as shown below:

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