Using SqlDependency in .NET 7 and WinForms application

Question

In my Winforms application I am trying to use SqlDependency so I created this data access class:

internal class DataAccessLayer 
{
    private readonly SqlConnection sqlconnection;

    public DataAccessLayer()
    {
        sqlconnection = new SqlConnection(LoginUserDetails.Connection);
    }

    // Method to open the connection
    public async Task Open()
    {
        if (sqlconnection.State != ConnectionState.Open)
        {
            await sqlconnection.OpenAsync().ConfigureAwait(false);
        }
    }

    // Method to close the connection
    public void Close()
    {
        if (sqlconnection.State == ConnectionState.Open)
        {
            sqlconnection.Close();
        }
    }

    private void AddSqlCommandParameters(SqlCommand sqlcmd, string stored_procedure)
    {
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.CommandText = stored_procedure;
        sqlcmd.Connection = sqlconnection;
    }

    // Method to read data from database
    public async Task<DataTable> SelectDataAsync(string stored_procedure, SqlParameter[] param, OnChangeEventHandler onChangeEventHandler = null)
    {
        if (string.IsNullOrEmpty(stored_procedure))
        {
            return null;
        }

        await using var sqlCommand = new SqlCommand();

        AddSqlCommandParameters(sqlCommand, stored_procedure);

        if (param != null)
        {
            sqlCommand.Parameters.AddRange(param);
        }

        if (onChangeEventHandler != null)
        {
            var sqlDependency = new SqlDependency(sqlCommand);
            sqlDependency.OnChange += onChangeEventHandler;
        }

        using DataTable dt = new();
        using SqlDataAdapter da = new(sqlCommand);

        await Task.Run(() => da.Fill(dt)).ConfigureAwait(false);

        return dt;
    }
}

And I use it like this

internal class CLS_Welding
{
    public static async Task<DataTable> GetWeldingPaintProduction(OnChangeEventHandler onChangeEventHandler = null)
    {
        using var DAL = new DataAccessLayer();
        return await DAL.SelectDataAsync("GetWeldingPaintProduction", null, onChangeEventHandler).ConfigureAwait(false);
    }
}

In my Winforms, I call GetWeldingPaintProduction method on form shown event like this

 private async Task GetData()
 {
       GCBlack.DataSource = await ClsWelding.GetWeldingPaintProductionAsync(OnDependencyChange).ConfigureAwait(false);
 }

 private void OnDependencyChange(object sender, SqlNotificationEventArgs e)
 {
     // Refresh the data.
     Invoke(new MethodInvoker(async delegate
        {
            await GetData().ConfigureAwait(false);
        }));

     // Notify the user that the data has changed.
     MessageBox.Show("The Welding Paint Production data has changed.");
}

The OnDependencyChange is never being hit.

I have already enabled the service broker.

This is my stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetWeldingPaintProduction]
AS
    SELECT 
        daiPaiPro.[id],
        [FK_idPartShip],
        proinfo.ProjectN,
        [Parts],
        [Profile],
        [Quantity],
        [Qty] - [WeldingPaintQty] AS 'Reste Qté',
        [Length],
        [Surface],
        ProShip.[Weight],
        CAST(ProShip.[Weight] * [Quantity] AS decimal(18, 2)) AS 'Poids Tot',
        [Designation],
        [Note],
        [CreationDate],
        CONCAT(daiPaiPro.[UserID], ' ', emp.LastName_Employee, ' ', emp.FirstName_Employee) AS 'Utilisateur',
        cust.Name AS 'Client',
        ShiftTime.ShiftTime,
        FK_ShiftTime,
        FK_idNextProcess,
        ProShip.Qty,
        IdDailyWeldingProduction,
        IdDailyPrefabrication,
        SupBS.Structure
    FROM 
        [dbo].[DailyWeldingPaintProduction] daiPaiPro
    INNER JOIN 
        ProjectShipping ProShip ON ProShip.id = [FK_idPartShip]
    INNER JOIN 
        ProjectInfo proinfo ON proinfo.id = ProShip.IdProject
    INNER JOIN 
        Employee emp ON ID_Employee = daiPaiPro.[UserID]
    INNER JOIN 
        Customer cust ON cust.ID = proinfo.FK_Customer
    INNER JOIN 
        ShiftTime ON ShiftTime.id = FK_ShiftTime
    LEFT JOIN 
        StructureType SupBS ON SupBS.id = FKSupBS
    ORDER BY 
        [CreationDate] DESC
GO

I try with insert, update and delete. In the database no queues are found.

What I am missing?

I am using Microsoft.Data.SqlClient Version="5.1.1"

Answer 1

I would like to thank all of you for helping me resolve this issue
I made this change to the database

ALTER AUTHORIZATION ON DATABASE::SIM TO sa;

the previews owner (MYLIFE\MBoua) has (sysadmin and public) roles and windows authentication
The (sa) has the same roles (sysadmin and public) with sql authentication.
Which is strange to me. Could the authentication type make this difference?
I have also changed the connection string to be like this

Server=(localdb)\\MSSQLLocalDB;Database=SIM;user id=sa;password=PassWord;Encrypt=False