在.NET 7和WinForms应用程序中使用SqlDependency

huangapple go评论82阅读模式
英文:

Using SqlDependency in .NET 7 and WinForms application

问题

在我的Winforms应用程序中,我尝试使用SqlDependency,因此我创建了这个数据访问类:

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

然后我这样使用它:

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

在我的Winforms应用程序中,我在窗体显示事件中调用GetWeldingPaintProduction方法,就像这样:

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("焊接油漆生产数据已更改。");
}

但是OnDependencyChange 没有被触发。

我已经启用了服务代理(service broker)。

这是我的存储过程:

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 Total',
        [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

我尝试了插入、更新和删除。在数据库中找不到队列。

我漏掉了什么?

我使用的是 Microsoft.Data.SqlClient 版本为 "5.1.1"。

英文:

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&lt;DataTable&gt; 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(() =&gt; da.Fill(dt)).ConfigureAwait(false);

        return dt;
    }
}

And I use it like this

internal class CLS_Welding
{
    public static async Task&lt;DataTable&gt; GetWeldingPaintProduction(OnChangeEventHandler onChangeEventHandler = null)
    {
        using var DAL = new DataAccessLayer();
        return await DAL.SelectDataAsync(&quot;GetWeldingPaintProduction&quot;, 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(&quot;The Welding Paint Production data has changed.&quot;);
}

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 &#39;Reste Qt&#233;&#39;,
        [Length],
		[Surface],
		ProShip.[Weight],
		CAST(ProShip.[Weight] * [Quantity] AS decimal(18, 2)) AS &#39;Poids Tot&#39;,
		[Designation],
		[Note],
		[CreationDate],
		CONCAT(daiPaiPro.[UserID], &#39; &#39;, emp.LastName_Employee, &#39; &#39;, emp.FirstName_Employee) AS &#39;Utilisateur&#39;,
		cust.Name AS &#39;Client&#39;,
		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"

答案1

得分: 3

我想感谢你们所有人帮助我解决这个问题
我对数据库进行了以下更改

ALTER AUTHORIZATION ON DATABASE::SIM TO sa;
之前的所有者(MYLIFE\MBoua)具有(sysadmin和public)角色和Windows身份验证
(sa) 具有相同的角色(sysadmin和public)与SQL身份验证。
这对我来说很奇怪。身份验证类型可能会造成这种差异吗?
我还更改了连接字符串,如下所示

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

英文:

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 

答案2

得分: 1

SqlCommand parameters

另一个堆栈溢出用户遇到了类似的问题,在这里,你可以看到如何使用OnDependencyChange()钩子执行函数。他们的解决方案是:

  1. 将命令类型设置为 CommandType.StoredProcedure

他们在获取SQL依赖之前将 command.CommandType = CommandType.StoredProcedure; 设置为存储过程。 (这看起来你已经做过了。)

  1. 将SQLConnection函数与存储过程名称和连接作为参数。

在你的情况下,你应该将 await using var sqlCommand = new SqlCommand(); 替换为 await using var sqlCommand = new SqlCommand(stored_procedure, sqlconnection);

Service Broker

您的问题也可能是因为您的数据库不支持Service Broker,而SqlDependency需要它才能工作。您可以使用以下SQL命令来检查和启用此功能: ALTER DATABASE &lt;database_name&gt; SET ENABLE_BROKER 这里有更多关于这个的信息 here

Query Notification Requirements

您的代码之所以无法工作的另一个原因是因为它没有必要的权限。您可以像这个用户做的那样,这里也有一个启用一些权限的示例 here

希望这有所帮助。

英文:

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.)

  1. 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 &lt;database_name&gt; 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.

huangapple
  • 本文由 发表于 2023年7月14日 02:54:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/76682460.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定