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

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:
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>
       }
   

   

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.

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