How to work with SQL Server table dependency with ASP.NET Core

Question

The context was the SQL Server table dependency not working, but my SignalR is working fine. There is no error showing its just that when I change some data through SSMS, the code below is firing.

private void TableDependency_OnChanged(object sender, TableDependency.SqlClient.Base.EventArgs.RecordChangedEventArgs<Announcement> e)
{
    if (e.ChangeType != TableDependency.SqlClient.Base.Enums.ChangeType.None)
    {
        announcementHub.NotifyAnnouncement();
    }
}

This is my code to handle when there is update from database:

public async Task NotifyAnnouncement()
{
    await Clients.All.SendAsync("ShowAnnouncement");
}

This is the code in my hub:

const connection = new signalR.HubConnectionBuilder()
    .withUrl("/resident/home")
    .build();

connection.start().then(function () {
    alert('Connected to home');
    
}).catch(function (err) {
    return console.error(err.toString());
});

connection.on("ShowAnnouncement", function () {
    toastr.info("something new");
    updateAnnouncement();
});

This is my script on the client side - this code is working fine when I insert a new data through the system, because after inserting the data I put this code

await _hubContext.Clients.All.SendAsync("ShowAnnouncement");

inside the controller. Then it reflects to my client side.

I don't know if I should call the controller in SqlTableDependency code or is it possible to do, so that it reflects to the client side?

I'm new to using SignalR and SqlTableDependency. Hope you understand thanks.

I already enable the is_broker in the ssms.

Answer 1

I suggest you could follow below steps and codes to check why your sql dependcy not working.

1.Make sure you have enabled the sql ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

2.Make sure you have set the SqlDependency well. Pass the SqlCommand with the right sql query.

IEmployeeRepository:

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");
    }
}

Hub method:

public class SignalServer : Hub
{

}

Controller:

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());
    }

}

View:

<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="~/microsoft/signalr/dist/browser/signalr.js"></script>
    <script>
        $(() => {
            //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)
                    }
                })
            }
        })
    </script>
}

Not Modified:

Modified the Jason name to Jasontest