isnemoequaltrue/blazor-notification-db-record-change

Blazor notification on database recond changes

Blazor client notifications on database record change

This example uses .NET CORE 3.0 Blazor server side to real-time update a HTML page on any database record changes.

Based on the observer design pattern in which an object, called the subject, maintains a list of its dependents, called observers, and notifies them automatically of any state changes, usually by calling one of their methods.

Database table

Table for which I want to receive notifications every time its content changes:

CREATE TABLE [dbo].[WeatherForecasts](
	[City] [nvarchar](50) NOT NULL,
	[Temperature] [int] NOT NULL
)

Subject

Singleton instance wrapping SqlTableDependency and forwarding record table changes to subscribers:

    public delegate void WeatherForecastDelegate(object sender, WeatherForecastChangeEventArgs args);

    public class WeatherForecastChangeEventArgs : EventArgs
    {
        public WeatherForecast NewWeatherForecast { get; }
        public WeatherForecast OldWeatherForecast { get; }

        public WeatherForecastChangeEventArgs(WeatherForecast newWeatherForecast, WeatherForecast oldWeatherForecast)
        {
            this.NewWeatherForecast = newWeatherForecast;
            this.OldWeatherForecast = oldWeatherForecast;
        }
    }

    public interface IWeatherForecastService
    {
        public event WeatherForecastDelegate OnWeatherForecastChanged;
        IList<WeatherForecast> GetForecast();
    }

    public class WeatherForecastService : IWeatherForecastService, IDisposable
    {
        private const string TableName = "WeatherForecasts";
        private SqlTableDependency<WeatherForecast> _notifier;
        private IConfiguration _configuration;
        
        public event WeatherForecastDelegate OnWeatherForecastChanged;

        public WeatherForecastService(IConfiguration configuration)
        {
            _configuration = configuration;

            _notifier = new SqlTableDependency<WeatherForecast>(_configuration["ConnectionString"], TableName);
            _notifier.OnChanged += this.TableDependency_Changed;
            _notifier.Start();
        }

        private void TableDependency_Changed(object sender, RecordChangedEventArgs<WeatherForecast> e)
        { 
            if (this.OnWeatherForecastChanged != null)
            {
                this.OnWeatherForecastChanged(this, new WeatherForecastChangeEventArgs(e.Entity, e.EntityOldValues));
            }
        }

        public IList<WeatherForecast> GetForecast()
        {
            var result = new List<WeatherForecast>();

            using (var sqlConnection = new SqlConnection(_configuration["ConnectionString"]))
            {
                sqlConnection.Open();

                using (var command = sqlConnection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM " + TableName;
                    command.CommandType = CommandType.Text;

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                result.Add(new WeatherForecast
                                {
                                    City = reader.GetString(reader.GetOrdinal("City")),
                                    Temperature = reader.GetInt32(reader.GetOrdinal("Temperature"))
                                });
                            }
                        }
                    }
                }
            }

            return result;
        }

        public void Dispose()
        {
            _notifier.Stop();
            _notifier.Dispose();
        }
    }

Registration as Singleton:

public void ConfigureServices(IServiceCollection services)
{
    ...
    ...
    services.AddSingleton<IWeatherForecastService, WeatherForecastService>();
}

Observer

Index.razor page code (event subscriber):

@page "/"

@using DataBaseRecordChaneNotificationWithBlazor.Data

@inject IWeatherForecastService ForecastService
@implements IDisposable

<h1>Weather forecast</h1>

<p>Immediate client notification on record table change with Blazor</p>

<table class="table">
    <thead>
        <tr>
            <th>City</th>
            <th>Temp. (C)</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var forecast in forecasts)
        {
            <tr>
                <td>@forecast.City</td>
                <td>@forecast.Temperature</td>
            </tr>
        }
    </tbody>
</table>

@code {
    IList<WeatherForecast> forecasts;

    protected override void OnInitialized()
    {
        this.ForecastService.OnWeatherForecastChanged += this.WeatherForecastChanged;
        this.forecasts = this.ForecastService.GetForecast();
    }

    private async void WeatherForecastChanged(object sender, WeatherForecastChangeEventArgs args)
    {
        var recordToupdate = this.forecasts.FirstOrDefault(x => x.City == args.NewWeatherForecast.City);
        if (recordToupdate == null)
        {
            this.forecasts.Add(args.NewWeatherForecast);
        }
        else
        {
            recordToupdate.Temperature = args.NewWeatherForecast.Temperature;
        }

        await InvokeAsync(() =>
        {
            base.StateHasChanged();
        });
    }

    public void Dispose()
    {
        this.ForecastService.OnWeatherForecastChanged += this.WeatherForecastChanged;
    }
}

More info on https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency.

blazor-notification-db-record-change's Issues

SqlTableDependency Error

In my line:

_notifier = new SqlTableDependency<DeviceModel>(stringConnection, TableName);

i get this error:

MONO] /__w/1/s/src/mono/mono/metadata/loader.c:1940
Error: [MONO] * Assertion at /__w/1/s/src/mono/mono/metadata/loader.c:1817, condition `' not met

at xe (https://localhost:7145/_framework/dotnet.7.0.0.vfpev60v1y.js:5:509)
at Object.je (https://localhost:7145/_framework/dotnet.7.0.0.vfpev60v1y.js:5:792)
at _mono_wasm_trace_logger (https://localhost:7145/_framework/dotnet.7.0.0.vfpev60v1y.js:14:104923)
at wasm://wasm/009924a6:wasm-function[105]:0x9a42
at wasm://wasm/009924a6:wasm-function[2696]:0xa585d
at wasm://wasm/009924a6:wasm-function[8599]:0x1bfc4a
at wasm://wasm/009924a6:wasm-function[8602]:0x1bfcd9
at wasm://wasm/009924a6:wasm-function[8604]:0x1bfd1c
at wasm://wasm/009924a6:wasm-function[8603]:0x1bfcef
at wasm://wasm/009924a6:wasm-function[706]:0x3bd50

The thread 0xbde0 has exited with code 0 (0x0).
The thread 0xcbb8 has exited with code 0 (0x0).
The thread 0xd7a4 has exited with code 0 (0x0).
Uncaught ExitStatus ExitStatus
at Ie (localhost꞉7145/_framework/blazor.webassembly.js:1:28630)
--- await ---
at (localhost꞉7145/_framework/blazor.webassembly.js:1:20041)
at (localhost꞉7145/_framework/blazor.webassembly.js:1:13355)
at onGlobalEvent (localhost꞉7145/_framework/blazor.webassembly.js:1:13343)

please help x)

Example is subscribing again on Dispose()

Errors

This gives me a whole bunch of errors when trying to run it in VS 2019: (edited: nevermind, just had to add the sqlclient package through NuGet)

An unhandled exception occurred while processing the request.
DllNotFoundException: Unable to load DLL 'sni.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)
System.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize(IntPtr pmo)

TypeInitializationException: The type initializer for 'System.Data.SqlClient.SNILoadHandle' threw an exception.
System.Data.SqlClient.TdsParser..cctor()

TypeInitializationException: The type initializer for 'System.Data.SqlClient.TdsParser' threw an exception.
System.Data.SqlClient.TdsParser..ctor(bool MARS, bool fAsynchronous)

Stack Query Cookies Headers Routing
DllNotFoundException: Unable to load DLL 'sni.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)
System.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize(IntPtr pmo)
System.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize()
System.Data.SqlClient.SNILoadHandle..ctor()
System.Data.SqlClient.SNILoadHandle..cctor()

Show raw exception details
TypeInitializationException: The type initializer for 'System.Data.SqlClient.SNILoadHandle' threw an exception.
System.Data.SqlClient.TdsParser..cctor()

Show raw exception details
TypeInitializationException: The type initializer for 'System.Data.SqlClient.TdsParser' threw an exception.
System.Data.SqlClient.TdsParser..ctor(bool MARS, bool fAsynchronous)
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, bool redirectedUserInstance, SqlConnectionString connectionOptions, TimeoutTimer timeout)
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, bool redirectedUserInstance)
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, object providerInfo, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling)
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, uint waitForMultipleObjectsTimeout, bool allowCreate, bool onlyOneCheckConnection, DbConnectionOptions userOptions, out DbConnectionInternal connection)
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource retry, DbConnectionOptions userOptions, out DbConnectionInternal connection)
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, out DbConnectionInternal connection)
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource retry, DbConnectionOptions userOptions)
System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource retry, DbConnectionOptions userOptions)
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource retry)
System.Data.SqlClient.SqlConnection.Open()
TableDependency.SqlClient.SqlTableDependency.CheckIfConnectionStringIsValid()
TableDependency.SqlClient.Base.TableDependency..ctor(string connectionString, string tableName, string schemaName, IModelToTableMapper mapper, IUpdateOfModel updateOf, ITableDependencyFilter filter, DmlTriggerType dmlTriggerType, bool executeUserPermissionCheck)
TableDependency.SqlClient.SqlTableDependency..ctor(string connectionString, string tableName, string schemaName, IModelToTableMapper mapper, IUpdateOfModel updateOf, ITableDependencyFilter filter, DmlTriggerType notifyOn, bool executeUserPermissionCheck, bool includeOldValues)
DataBaseRecordChaneNotificationWithBlazor.Data.WeatherForecastService..ctor(IConfiguration configuration) in WeatherForecastService.cs
+
_notifier = new SqlTableDependency(_configuration["ConnectionString"], TableName);
System.RuntimeMethodHandle.InvokeMethod(object target, object[] arguments, Signature sig, bool constructor, bool wrapExceptions)
System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, object[] parameters, CultureInfo culture)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor<TArgument, TResult>.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitRootCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor<TArgument, TResult>.VisitCallSite(ServiceCallSite callSite, TArgument argument)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine+<>c__DisplayClass1_0.b__0(ServiceProviderEngineScope scope)
Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
Microsoft.AspNetCore.Components.ComponentFactory+<>c__DisplayClass5_0.g__Initialize|2(IServiceProvider serviceProvider, IComponent component)
Microsoft.AspNetCore.Components.ComponentFactory.PerformPropertyInjection(IServiceProvider serviceProvider, IComponent instance)
Microsoft.AspNetCore.Components.ComponentFactory.InstantiateComponent(IServiceProvider serviceProvider, Type componentType)
Microsoft.AspNetCore.Components.RenderTree.Renderer.InstantiateChildComponentOnFrame(ref RenderTreeFrame frame, int parentComponentId)
Microsoft.AspNetCore.Components.RenderTree.RenderTreeDiffBuilder.InitializeNewComponentFrame(ref DiffContext diffContext, int frameIndex)
Microsoft.AspNetCore.Components.RenderTree.RenderTreeDiffBuilder.InitializeNewSubtree(ref DiffContext diffContext, int frameIndex)
Microsoft.AspNetCore.Components.RenderTree.RenderTreeDiffBuilder.InsertNewFrame(ref DiffContext diffContext, int newFrameIndex)
Microsoft.AspNetCore.Components.RenderTree.RenderTreeDiffBuilder.AppendDiffEntriesForRange(ref DiffContext diffContext, int oldStartIndex, int oldEndIndexExcl, int newStartIndex, int newEndIndexExcl)
Microsoft.AspNetCore.Components.RenderTree.RenderTreeDiffBuilder.ComputeDiff(Renderer renderer, RenderBatchBuilder batchBuilder, int componentId, ArrayRange oldTree, ArrayRange newTree)
Microsoft.AspNetCore.Components.Rendering.ComponentState.RenderIntoBatch(RenderBatchBuilder batchBuilder, RenderFragment renderFragment)
Microsoft.AspNetCore.Components.RenderTree.Renderer.RenderInExistingBatch(RenderQueueEntry renderQueueEntry)
Microsoft.AspNetCore.Components.RenderTree.Renderer.ProcessRenderQueue()
Microsoft.AspNetCore.Components.Rendering.HtmlRenderer.HandleException(Exception exception)
Microsoft.AspNetCore.Components.RenderTree.Renderer.ProcessRenderQueue()
Microsoft.AspNetCore.Components.RenderTree.Renderer.ProcessPendingRender()
Microsoft.AspNetCore.Components.RenderTree.Renderer.AddToRenderQueue(int componentId, RenderFragment renderFragment)
Microsoft.AspNetCore.Components.ComponentBase.StateHasChanged()
Microsoft.AspNetCore.Components.ComponentBase.CallOnParametersSetAsync()
Microsoft.AspNetCore.Components.ComponentBase.RunInitAndSetParametersAsync()
Microsoft.AspNetCore.Components.Rendering.HtmlRenderer.HandleException(Exception exception)
Microsoft.AspNetCore.Components.RenderTree.Renderer.AddToPendingTasks(Task task)
Microsoft.AspNetCore.Components.Rendering.ComponentState.SetDirectParameters(ParameterView parameters)
Microsoft.AspNetCore.Components.RenderTree.Renderer.RenderRootComponentAsync(int componentId, ParameterView initialParameters)
Microsoft.AspNetCore.Components.Rendering.HtmlRenderer.CreateInitialRenderAsync(Type componentType, ParameterView initialParameters)
Microsoft.AspNetCore.Components.Rendering.HtmlRenderer.RenderComponentAsync(Type componentType, ParameterView initialParameters)
Microsoft.AspNetCore.Components.Rendering.RendererSynchronizationContext+<>c__11+<b__11_0>d.MoveNext()
Microsoft.AspNetCore.Mvc.ViewFeatures.StaticComponentRenderer.PrerenderComponentAsync(ParameterView parameters, HttpContext httpContext, Type componentType)
Microsoft.AspNetCore.Mvc.ViewFeatures.ComponentRenderer.PrerenderedServerComponentAsync(HttpContext context, ServerComponentInvocationSequence invocationId, Type type, ParameterView parametersCollection)
Microsoft.AspNetCore.Mvc.ViewFeatures.ComponentRenderer.RenderComponentAsync(ViewContext viewContext, Type componentType, RenderMode renderMode, object parameters)
DataBaseRecordChaneNotificationWithBlazor.Pages.Pages__Host.b__10_1() in _Host.cshtml
+
@(await Html.RenderComponentAsync(RenderMode.ServerPrerendered))
Microsoft.AspNetCore.Razor.Runtime.TagHelpers.TagHelperExecutionContext.SetOutputContentAsync()
DataBaseRecordChaneNotificationWithBlazor.Pages.Pages__Host.ExecuteAsync()
Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageCoreAsync(IRazorPage page, ViewContext context)
Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageAsync(IRazorPage page, ViewContext context, bool invokeViewStarts)
Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderAsync(ViewContext context)
Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, string contentType, Nullable statusCode)
Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, string contentType, Nullable statusCode)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|29_0<TFilter, TFilterAsync>(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext<TFilter, TFilterAsync>(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker)
Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)