Cacher is the code snippet organizer for pro developers

private void RegisterNotification()
        {
	//Get the connection string from the Web.Config file. Make sure that the key exists and it is the connection string for the Notification Database and the NotificationList Table that we created

            string connectionString = ConfigurationManager.ConnectionStrings["NotificationConnection"].ConnectionString;

	//We have selected the entire table as the command, so SQL Server executes this script and sees if there is a change in the result, raise the event
            string commandText = @"
                                    Select
                                        dbo.NotificationList.ID,
                                        dbo.NotificationList.Text,
                                        dbo.NotificationList.UserID,
                                        dbo.NotificationList.CreatedDate                                      
                                    From
                                        dbo.NotificationList                                     
                                    ";

	//Start the SQL Dependency
            SqlDependency.Start(connectionString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                using (SqlCommand command = new SqlCommand(commandText, connection))
                {
                    connection.Open();
                    var sqlDependency = new SqlDependency(command);


                    sqlDependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);

                    // NOTE: You have to execute the command, or the notification will never fire.
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                    }
                }
            }
        }

using Microsoft.AspNetCore.SignalR;
using SignalR_Test4.Data;
using SignalR_Test4.Hubs;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace SignalR_Test4.Models
{
    public class CityRepository
    {
    private readonly ApplicationDbContext _context;
    private readonly IHubContext<CityHub> _hubcontext;
    public CityRepository(ApplicationDbContext context, IHubContext<CityHub> hubcontext)
    {
        _context = context;
        _hubcontext = hubcontext;
    }

    public IEnumerable<City> GetCities()
    {
        List<City> listOf = new List<City>();
        //listOf = _context.Cities;

        using (var conn = new SqlConnection(GlobalVar.connectionString))
        {
            conn.Open();
            using (var cmd = new SqlCommand(@"SELECT * FROM Cities", conn))
            {
                cmd.Notification = null;
                SqlDependency dependency = new SqlDependency(cmd);
                dependency.OnChange += Dependency_OnChange;

                if (conn.State == System.Data.ConnectionState.Closed)
                    conn.Open();


                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    listOf.Add(new City { Id = (string)reader["Id"], Name_en = (string)reader["name_en"], CountryId = (string)reader["CountryId"], Code = (string)reader["Code"] });
                }
            }
        }
        return listOf;
    }
    private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            _hubcontext.Clients.All.SendAsync("GetCities");
        }
    }
}

}

http://elvanydev.com/SignalR-Core-SqlDependency-part2/