5

I am building a web app that has a dashboard page in which data must be updated in real-time. I'm using ASP.NET Core 3.0, MVC, ADO.NET, SignalR, and SqlDependency in my project. I chose SignalR and SqlDepdendency (first time i'm developing with those) because data is fed into my SqlServer database from other db sources, and data needs to be pushed to my web app and to my clients. I found a few websites to explain how to use SqlDependency but I found no code examples with ASP.NET Core 3.0. https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/detect-changes-sqldependency?view=sql-server-ver15

Where and how should I put the SqlDependency.Start() / SqlDependency.Stop() in the Startup.cs ? I currently have them inside my Repository DAL (Data Access Layer) class' method but I don't think it's correct to have the Start() and Stop() there because it doesn't make sense the SqlDependency is initiated and stopped on each call to that method is made by a client loading the dashboard page.

Also, any drawbacks or problems that you know with SqlDependency in terms of performance or bugs?

Dan Wind
  • 71
  • 1
  • 5
  • 2
    `I found no code examples with ASP.NET Core 3.0` You can try to [create a github issue](https://github.com/aspnet/SignalR-samples/issues) to give a feedback and confirm if sample team can share an example about implementing real-time database notification with using ASP.NET Core SignalR and SqlDependency. – Fei Han Jul 30 '20 at 06:18
  • 1
    Thanks for the suggestion Fei Han. – Dan Wind Jul 30 '20 at 18:00
  • 1
    did you get anything or were you able to implement this? @DanWind – Jitendra Pancholi Aug 10 '20 at 09:27
  • 1
    Yes, I was able to implement it. Many things had to be done to get it to work. 1) the service broker and permissions in SQLServer database permission need to be done first 2) There are many restrictions on how the SELECT query can be written otherwise SlqDependency will not work. 3) In my Repository DAL class method, SqlDependency.Stop(connectionString) has to go before SqlDependency.Start(connectionString) to ensure no previous dependency object is running. I found some helpful YouTube videos: https://www.youtube.com/watch?v=JmyxrZRr3Hw https://www.youtube.com/watch?v=bNNOpFndsP8 – Dan Wind Aug 28 '20 at 20:08
  • 1
    This YouTube video also helped me in the beginning, although the example is with ASP.NET Core 2 https://www.youtube.com/watch?v=JmyxrZRr3Hw – Dan Wind Aug 28 '20 at 20:14

0 Answers0