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);
}
public async Task Open()
{
if (sqlconnection.State != ConnectionState.Open)
{
await sqlconnection.OpenAsync().ConfigureAwait(false);
}
}
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;
}
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)
{
Invoke(new MethodInvoker(async delegate
{
await GetData().ConfigureAwait(false);
}));
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?
<PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.1" />
What I have tried:
There are no ideas left in my head