Asp.net core2.2 webapi, SQL dependency changed event handler gets triggered only once not more

Question

I have a simple project where I want to implement SQL dependency. my problem is that when I insert a row in a database, only once the changed event handler of SQL dependency gets triggered and not anymore.

in startup.cs I have put this:

public Startup(IConfiguration configuration)
    {
        Configuration = configuration; 
        string cs = "Data Source=.;Initial Catalog=signalr;Integrated Security=True;user id=sa;password=xyz";

        SqlDependency.Start(cs);
    }

my controller:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;

namespace SignalrChat.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ChatController : ControllerBase
    {
        [Route("get")]
        [HttpGet]
        public async Task<IActionResult> Get()
        {
            RegisterTradeInformationNotification().Wait();
            return Ok("dasdasd");
        }

        private async Task<object> RegisterTradeInformationNotification()
        {
            IEnumerable<object> list;
            try
            {
                string cs = "Data Source=.;Initial Catalog=signalr;Integrated Security=True;user id=sa;password=xyz";

                using (SqlConnection con = new SqlConnection(cs))
                {
                    con.Open();
                    string cmdText = @"SELECT [Id],[Message] FROM [dbo].[Chat]";
                    using (SqlCommand cmd = new SqlCommand(cmdText, con))
                    {
                        cmd.Notification = null;


                        SqlDependency tradeInfoDependency = new SqlDependency(cmd);
                        tradeInfoDependency.OnChange += TradeInfoDependency_OnChange;
                        if (con.State == ConnectionState.Closed)
                        {
                            con.Open();
                        }

                        SqlDataReader reader = cmd.ExecuteReader();
                        list = reader.Cast<IDataRecord>()
                            .Select(x => new
                            {
                                Id = (int)reader["Id"],
                                Message = (string)reader["Message"]
                            }).ToList();

                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            return Ok(new { success = list });
        }

        private void TradeInfoDependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            var dependency = sender as SqlDependency;

            if (dependency == null) return;

            if (e.Info == SqlNotificationInfo.Insert)
            {


                //  _hubContext.Clients.All.SendAsync("TradeInfo");
            }

        }
    }

when I insert rows in the table from the SQL server, only once TradeInfoDependency_OnChange() gets triggered which is for the first insertion while the state of dependency is equal to "e.Info == SqlNotificationInfo. Insert". Unfortunately the rest of row insertions won't be trigger the handler. how could I solve it to trigger for each and every insertion?

Answer 1

As "@Nan Yu" Mentioned, i had to recall the RegisterTradeInformationNotification() function