Implementing SignalR in ASP.NET Core Web App (MVC) for real-time database updates

ChrisU 111 Reputation points
2023-01-11T13:00:50.5266667+00:00

Hi All,

I am still a bit green with C# and ASP.NET however I have created a ASP.NET Core Web App (MVC) .NET6, that displays an IOT temperature sensor reading summary dashboard and another page that displays the readings and other information about each individual sensor.  This is not using Entity framework but instead using SQL statements to get the information from the database.  This is going to be developed further, but for now I have been working on making the current information display in real-time.

Currently the temperature displayed for each sensor is queried only when the page loads.  To get the latest, the page needs to be refreshed.  Reading around this I understand that SignalR is the go-to for real-time updates when using ASP.NET.

I have been doing some research and understand the high-level concepts, however, I learn best whilst doing and being able to join it up in my head as I go along (mostly following a tutorial).  Then I can start implementing the functionality for my needs.  The SignalR page (Real-time ASP.NET with SignalR | .NET (microsoft.com)) directs to tutorials that are concerned with chat apps and most online tutorials are also concerned with demonstrating this technology with chat apps.  The few tutorials (written and video) concerned with database real-time updates are either old or not well explained, so I have not been able to either follow them or, when I do complete them, get them to run.

My project basic architecture is below.

User's image

As stated, the queries back to the database occur when the application starts and when the user selects a view. 

Currently both views query one DB table.

The Dashboard Views displays the Dashboard class which contains a COUNT of the total amount of sensors, those that are in Alert and those out of tolerance.  So, three integers are displayed.  The counts for these are queried upon startup and when the dashboard is selected.

The Sensors View displays a box for each Sensor class in a List that is looped through on a Razor page.   Each box contains location information and temperature information for a single sensor.  The temperature information is queried when the sensors view is selected.

 

In terms of the real-time function I see two ways of doing this.

1)      Polling the database every few minutes to get updates

2)      Updating the frontend only when changes are made to the database

 

I am not quite sure which is best and whether SignalR can do either, however I suspect that option 2 would be best.

I understand that I would need to add a Hub and JS script to the front end however, I am not entirely sure about how to do this, how it is all connected, and what else I need to do in addition.

Can anyone help?  Can anyone point me in the direction of a tutorial that closely matches what I am trying to do?

 

Thanks in advance.

 

N.B – For some reason I cannot add just ‘SignalR’ and a tag, only ‘Azure SignalR Service’ was allowed.

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
2,599 questions
Azure SignalR Service
Azure SignalR Service
An Azure service that is used for adding real-time communications to web applications.
73 questions
{count} votes

Accepted answer
  1. Zhi Lv - MSFT 18,886 Reputation points Microsoft Employee
    2023-01-12T03:08:59.7133333+00:00

    Hi @ChrisU,

    Implementing SignalR in ASP.NET Core Web App (MVC) for real-time database updates. Updating the frontend only when changes are made to the database

    You can try to use the [SqlDependency's OnChange event] to detect the database updated, then call the SignalR client method to update the web page content.

    More details information, refer to the following sample:

    1. Create an asp.net core MVC application, and add an Employee model with the following test data: User's image
    2. Create an EmployeeRepository: in the SqlDependency onchange event, call the singalR client method and update the content.
     public interface IEmployeeRepository
     {
         List<Employee> GetAllEmployees();
     }
     public class EmployeeRepository : IEmployeeRepository
     {
         private readonly IHubContext<SignalServer> _context;
         string connectionString = "";
         public EmployeeRepository(IConfiguration configuration, IHubContext<SignalServer> context)
         {
             connectionString = configuration.GetConnectionString("DefaultConnection");
             _context = context;
         }
         public List<Employee> GetAllEmployees()
         {
             var employees = new List<Employee>();
    
             using (SqlConnection conn = new SqlConnection(connectionString))
             {
                 conn.Open();
    
                 SqlDependency.Start(connectionString);
    
                 string commandText = "select Id, Name, Age from dbo.Employees";
    
                 SqlCommand cmd = new SqlCommand(commandText, conn);
    
                 SqlDependency dependency = new SqlDependency(cmd);
    
                 dependency.OnChange+=new OnChangeEventHandler(dbChangeNotification);
    
                 var reader = cmd.ExecuteReader();
    
                 while (reader.Read())
                 {
                     var employee = new Employee
                     {
                         Id = Convert.ToInt32(reader["Id"]),
                         Name = reader["Name"].ToString(),
                         Age = Convert.ToInt32(reader["Age"])
                     };
    
                     employees.Add(employee);
                 }
             }
             return employees;
         }
    
         private void dbChangeNotification(object sender, SqlNotificationEventArgs e)
         {
             _context.Clients.All.SendAsync("refreshEmployees");
         }
     }
    
    1. Register the EmployeeRepository:
         builder.Services.AddTransient<IEmployeeRepository, EmployeeRepository>();
      
    2. Add the SignalR client library: @microsoft/signalr@latest
    3. Create SignalR Hub:
          public class SignalServer : Hub
          {
      
          }
      
    4. Configure SignalR:
         builder.Services.AddSignalR();
         ...
         app.MapHub<SignalServer>("/signalServer");
      
    5. Add SignalR client script (signalr.js):
          $(() => {
              //build the hub connection
              let connection = new signalR.HubConnectionBuilder().withUrl("/signalServer").build() 
              connection.start() 
              connection.on("refreshEmployees", function () {
                  loadData() //after database change, it will call this function to refresh the data.
              }) 
              loadData(); //when page load call the loadData method to display the data. 
              function loadData() {
                  var tr = ''
      
                  $.ajax({
                      url: '/Employee/GetEmployees',
                      method: 'GET',
                      success: (result) => {
                          $.each(result, (k, v) => {
                              tr = tr + `<tr>
                                  <td>${v.id}</td>
                                  <td>${v.name}</td>
                                  <td>${v.age}</td>
                              </tr>`
                          })
      
                          $("#tableBody").html(tr)
                      },
                      error: (error) => {
                          console.log(error)
                      }
                  })
              }
          }) 
      
    6. in the EmployeeControler:
          public class EmployeeController : Controller
          {
              private readonly IEmployeeRepository _repository;
      
              public EmployeeController(IEmployeeRepository repository)
              {
                  _repository = repository;
              }
              public IActionResult Index()
              {
                  return View();
              }
              [HttpGet]
              public IActionResult GetEmployees()
              {
                  return Ok(_repository.GetAllEmployees());
              }
      
          }
      
    7. In the Employee Index page: add the singalr client-side reference.
         <table class="table">
              <thead>
                  <tr>
                      <th>Id</th>
                      <th>Name</th>
                      <th>Age</th>
                  </tr>
              </thead>
              <tbody id="tableBody">
              </tbody>
          </table>
          @section Scripts{
              <script src="~/js/signalr/dist/browser/signalr.js"></script>
              <script src="~/js/signalr.js"></script>
          }
      
      enter image description here

    Finally, the result as below: The work flow: When page load, in the signalr.js file, it will call the Employee/GetEmployees method to display the employees, then when update the database, the SqlDependency's onchange event will detect it and call the signalR client function to reload the employee list.

    enter image description here


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Best regards,

    Dillion


0 additional answers

Sort by: Most helpful