c# - SQL 依赖 C#

Question

我希望在 UI 表上有实时数据，我想从使用表值函数的 SQL Server 存储过程中获取数据，但是在调用依赖项更改时出现无效订阅错误。

SqlConnection co = new SqlConnection(_connectionStringTest);

var messages = new List<WorkToListHeaderModel>();

SqlDependency.Stop(_connectionStringTest);
SqlDependency.Start(_connectionStringTest);

using (var cmd = new SqlCommand(@"[DBO].[spTest]", co))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Notification = null;

    SqlDataAdapter da = new SqlDataAdapter(cmd);

    var dependency = new SqlDependency(cmd);
    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

    DataSet ds = new DataSet();
    da.Fill(ds);

    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
        messages.Add(item: new WorkToListHeaderModel
                    {
                        SalesOrderNumber = ds.Tables[0].Rows[i][0].ToString(),
                    });
    }
}

return messages;

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Type == SqlNotificationType.Change)
    {
        MyHub.SendMessages();
    }
}

SQL

ALTER PROCEDURE [dbo].[spTest]   
AS 
BEGIN
    SELECT SalesOrderNumber, Test 
    FROM dbo.[TableTest]('z')
END

功能

ALTER FUNCTION [dbo].[TableTest]
    (@SalesOrderNumber NVARCHAR(100))
RETURNS 
    @Table TABLE
           (
               SalesOrderNumber NVARCHAR(100),
               Test NVARCHAR(150)

           )
AS
BEGIN 
    ;WITH Selects AS 
    (
        SELECT 
            AdamTest.SalesOrderNumber,
            TEST AS CustomerName
        FROM 
            [dbo].[Test] 
        LEFT JOIN 
            DBO.Test2 ON Test2.SALESORDERNUMBER = Test.SalesOrderNumber
    )
    INSERT INTO @Table
        SELECT DISTINCT s.SalesOrderNumber, s.CustomerName
        FROM Selects AS S

    RETURN 

标签： c#sql-serversignalr