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 

Answer 2

SqlCommand parameters

Another stack overflow user has a similar issue, here you can see how to execute a function with the OnDependencyChange() hook. Their solution was to

1. Have the command type as CommandType.StoredProcedure

They put command.CommandType = CommandType.StoredProcedure; before getting the SQL dependency. (Which it looks like you did already.)

2. Have the SQLConnection function with the stored procedure name and connection as parameters.

In your case, you should replace await using var sqlCommand = new SqlCommand(); with await using var sqlCommand = new SqlCommand(stored_procedure, sqlconnection);

Service Broker

Your problem could also be because your database does not support Service Broker, which is required for SqlDependency to work. You can check and enable this feature by using the following SQL command: ALTER DATABASE <database_name> SET ENABLE_BROKER There's some more info about that here.

Query Notification Requirements

Another reason why your code isn't working is because it doesn't have the permissions. You can do what this user did, and there's an example of enabling some permissions here as well.

Hope this is helpful.