SqlServiceBrokerListener utilizes a class, SqlDependencyEx (Side note: there is a bit in the "How to Use" of the package's readme that claims you must copy the class into your project. This is a relic of the original package that this was forked from and the class can simply be referenced like any other), to handle all listening activities. The setup syntax is fairly simple, as demonstrated by this snippet from the github "How to Use" section:

// See constructor optional parameters to configure it according to your needs
var listener = new SqlDependencyEx(connectionString, "YourDatabase", "YourTable");

// e.Data contains actual changed data in the XML format
listener.TableChanged += (o, e) => Console.WriteLine("Your table was changed!");

// After you call the Start method you will receive table notifications with
// the actual changed data in the XML format
listener.Start();

// ... Your code is here

// Don't forget to stop the listener somewhere!
listener.Stop();

When we call listener.Start(), a couple of SQL objects are set up for the database and table you've specified. First a trigger is added to your table that fires on table modifications. It reacts to incoming changes, wraps them up as an xml object, and passes them to the second SQL object that this method adds, the service broker.

Once the SQL infrastructure has been created, the SqlDependencyEx instance will begin polling the queue for changes. It does this using the WAITFOR/RECEIVE commands:

DECLARE @ConvHandle UNIQUEIDENTIFIER
DECLARE @message VARBINARY(MAX)
USE [{DatabaseName}]
WAITFOR (RECEIVE TOP(1) @ConvHandle=Conversation_Handle
            , @message=message_body FROM {SchemaName}.[{TableName}]), TIMEOUT {TimeoutInMs};
BEGIN TRY END CONVERSATION @ConvHandle; END TRY BEGIN CATCH END CATCH

SELECT CAST(@message AS NVARCHAR(MAX))

With the default configuration, SqlDependencyEx will wait on a queue message with that command for 30 seconds. If it does not receive one, the command is simply restarted.

However, if a message is received, the message is removed from the queue, and the event handler we specified via listener.TableChanged is fired with the XML document that was originally generated by the table trigger at the beginning of the process. We can use that event handler to accomplish any number of actions we want to perform within our application logic.

With the default configuration, SqlDependencyEx will wait on a queue message with that command for 30 seconds. If it does not receive one, the command is simply restarted.

While the first three steps of that process remain unchanged, we are not using user interaction as a catalyst for updates, but rather the TableChanged event above.

Now while you could set up one SqlDependencyEx for each connected user, you'd be introducing additional load onto your SQL instance in the form of redundant triggers and brokers on the same table. It therefore makes more sense to control the flow of updates via a singleton service within your web applications. A single SqlDependencyEx per table can be managed by this single object, moving the work of disseminating updates from the database to the application. A simple implmentation might look like this:

public class SqlUpdatesService : IDisposable
{
    public event Action<SqlDependencyEx.TableChangedEventArgs> TableUpdated;

    private readonly SqlDependencyEx tableListener;

    public SqlUpdatesService()
    {
        tableListener = new SqlDependencyEx("ConnectionString", "YourDatabase", "Table1");
        tableListener.TableChanged += Listener_TableChanged;
        tableListener.Start();
    }

    private void Listener_TableChanged(object sender, SqlDependencyEx.TableChangedEventArgs e)
    {
        TableUpdated.Invoke(e);
    }

    public void Dispose()
    {
        tableListener?.Stop();
    }
}

We can add this service to our dependency injection container during our startup via:

serviceCollection.AddSingleton<SqlUpdatesService>();

@implements IDisposable
@inject Table1DataService table1DataService
@inject SqlUpdatesService sqlUpdatesService

<h3>Table 1 Objects</h3>
@if (tableObjects != null)
{
    <table>
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var table1Obj in table1Objects)
            {
                <tr>
                    <td>@table1Obj.Id</td>
                    <td>@table1Obj.Name</td>
                </tr>
            }
        </tbody>
    </table>
}

@code {
    ICollection<TableObject> tableObjects;

    protected override async Task OnInitializedAsync()
    {
        // Subscribe to table events
        sqlUpdatesService.TableUpdated += SqlUpdatesService_TableUpdated;

        // Fetch starting data set
        tableObjects = table1DataService.GetTable1Objects();
    }

    private async void SqlUpdatesService_TableUpdated(SqlDependencyEx.TableChangedEventArgs e)
    {
        // Any calls to StateHasChanged outside of the standard
        // Blazor lifecycle events must be wrapped in InvokeAsync
        await InvokeAsync(() => {
            // Refresh the component's data set
            tableObjects = table1DataService.GetTable1Objects();

            // Prompt component to render the new data
            StateHasChanged();
        });
    }

    public void Dispose()
    {
        // Unsubscribe from events
        sqlUpdatesService.TableUpdated -= SqlUpdatesService_TableUpdated;
    }
}

That's the gist of it. This component will fetch data on initial load, then again whenever the SqlUpdatesService receives a table change from SQL. From here there are a few logic enhancements that we might choose to implement:

• Reading the SqlDependencyEx.TableChangedEventArgs event's Xml Data property to write the updates directly into memory without needing to call GetTable1Objects again.
• Allowing blazor components to subscribe to updates to just a single entity rather than the entire table, by allowing them to provide SqlUpdatesService with a filter.
• Watching multiple tables with SqlUpdatesService (more on this below).

There is an optional argument in the constructor of SqlDependencyEx that turns out to be very important when attempting to listen to multiple tables, to the same table with multiple application instances, or to multiple tables with multiple application instances. The identity argument will default to 1, but you'll soon find that attempting to register multiple listeners with that same identity will result in previous listeners getting silently overwritten.

To avoid this behavior, we need to assign identities to each instance of SqlDependencyEx we create.

var table1Listener = new SqlDependencyEx(connectionString, "YourDatabase", "Table1", identity: 1);
var table2Listener = new SqlDependencyEx(connectionString, "YourDatabase", "Table2", identity: 2);

This works OK if your application is only ever running on a single server (no local debugging allowed), but causes conflicts if there are multiple instances of your application running at the same time. To solve this, we can create a hashing solution that takes the current environment name (or some other per-application value you configure), combines that with the database and table name and generates an identity specific to this particular SqlDependencyEx.

While your first instinct might be to use a solution involving Object.GetHashCode(), bear in mind that GetHashCode is not deterministic in .NET Core. That means that every time you fire up the application, you'll be creating a listener with a completely different identifier, even if everything you're using to compute your hash hasn't changed. If you're terminating your application mid-runtime as is common during development, these SQL objects will not be getting cleaned up, and they can tend to accumulate.

public SqlDependencyEx CreateSqlDependencyEx(string databaseName, string tableName)
{
    int identityVal;

    using (var mySHA256 = SHA256.Create())
    {
        var bytes = mySHA256.ComputeHash(
            Encoding.UTF8.GetBytes(
                Environment.MachineName + databaseName + tableName));
        identityVal = Math.Abs(BitConverter.ToInt32(bytes)); // Identity needs to be positive
    }

    return new SqlDependencyEx(connectionString, databaseName, tableName, identity: identityVal);
}

While I used Blazor in this article, it is by no means a requirement to implement this pattern. Updates can be sent via standard websockets and received by a Javascript front end, and indeed ASP.NET Core is not required either, as the Service Broker/Trigger/WAITFOR/RECEIVE SQL pattern can be implemented in any language (including raw SQL querying).