英文:
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<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"
答案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()
钩子执行函数。他们的解决方案是:
- 将命令类型设置为
CommandType.StoredProcedure
他们在获取SQL依赖之前将 command.CommandType = CommandType.StoredProcedure;
设置为存储过程。 (这看起来你已经做过了。)
- 将SQLConnection函数与存储过程名称和连接作为参数。
在你的情况下,你应该将 await using var sqlCommand = new SqlCommand();
替换为 await using var sqlCommand = new SqlCommand(stored_procedure, sqlconnection);
Service Broker
您的问题也可能是因为您的数据库不支持Service Broker,而SqlDependency需要它才能工作。您可以使用以下SQL命令来检查和启用此功能: ALTER DATABASE <database_name> 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
- 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.)
- 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论