Build ASP.NET Core 3.x SignalR with real-time database notification SqlDependency

[doncore]

Can someone share a project example of a MVC web application implementing real-time database notification with using ASP.NET Core 3.0 or 3.1, ADO.NET, SignalR 3.0 and SqlDependency ?
I am building a dashboard web application ASP.NET Core 3.1 that has a grid in which real-time data gets updated/inserted/deleted by multiple concurrent users. I found a few examples but they are for ASP.NET Core 2.x and not complete project examples. I don't know how I should be implementing the SignalR and SqlDependency pieces the "proper" way in my project.

[BjoernLaemmerzahl]

I wonder if you got any further on your quest. Did people change to a different approach? I can't really find any documentation on .Net Core 3 with SqlDependency and SignalR either.

[jasonsheridan79]

Agreed, can't find any good 3.1+ ways of tying together SignalR and SqlDependency. I'm running a research spike this sprint to implement this on geospatial info we get into our DB from an outside feed. Needs to be able to pick up those changes, then deliver as geojson to a leaflet map. I quickly threw together some code, pulling from several tutorial (they all do it differently), but I can't get SqlDependency to fire onchanges. No errors, just never fires when a new record comes in.

[doncore]

@BjoernLaemmerzahl : yes, I was able to put together my project. It is currently in beta testing. However we've had troubles with database transactions deadlocking because of SqlDependency, and constant queries to reload the entire dataset (about 1000 records) on the grid because each time one user updates a record then SqlDependency detects that change and fires a call to all clients via SignalR to reload their grid. I wish I can find a way for each client to just refresh one specific row, not the entire grid.

[doncore]

@jasonsheridan79 : if you can't get SqlDependency to fire onchange, first check your database service broker is enabled and check your SQL select statement to which your SqlDependency is running on (there are a lot of restrictions to how SELECT statement can be written with SqlDependency). For example, can't have joins, can't have computer columns, etc. We've had trouble getting the EventHandler to fire the onchange at first because one of our tables had a computed column.

[jasonsheridan79]

I found that you can view a log of exceptions to SqlDependency using select * from sys.transmissionqueue (or something close to it, I'm not on my dev machine). Googled the error in that table, turned out I needed to assign an owner on the database and set enable broker to true in SSMS. It started working, I got my proof of concept up and running.

[rajunarola]

Use this link and the GitHub demo it will helpfull
https://www.youtube.com/watch?v=f6cZhp9NETY
https://github.com/mewanindula/SignalR-SQL_Dependency