C# 在以表类型为参数的 SQL Server 中调用用户定义标量函数

Question

在这个问题上我一直在磕磕绊绊。我写了一个标量函数，它将我创建的表类型作为参数，它返回一个简单的 varchar，如果有帮助，这里是 sql 代码

ALTER FUNCTION [dbo].[pe_Get_Manufacturer]
(
-- Add the parameters for the function here
@Row [dbo].[pe_StringList] READONLY
)

RETURNS VARCHAR(103)
AS
BEGIN

DECLARE @OUT VARCHAR(50)
DECLARE @tempTable TABLE
(
    Position INT,
    ManuName CHAR(100),
    ManuCat CHAR(3)
)

INSERT INTO @tempTable
    SELECT DISTINCT r.Position, c.ima_mfg, c.ima_cat
    FROM dbo.[Admin_ MFR Aliases] as c
        INNER JOIN @Row r
            ON c.orig_mfg = r.Candidate 
            OR c.ima_mfg = r.Candidate
            OR c.orgmfgstrp = r.Candidate
    ORDER BY r.Position

SELECT TOP 1 @OUT = LTRIM(RTRIM(ManuName))  + '^' + COALESCE(ManuCat,'')
FROM @tempTable 
ORDER BY Position DESC

-- Return the result of the function
RETURN @OUT
END

在我的 C# 方面，我有一个函数，它采用的是一个字符串列表，这些字符串将被放入数据表中以用作函数的参数。我相信我写的都是正确的，但是我的项目在 SQLCommand 上运行 ExecuteScalar 时抛出什么

public string getManufacturer(IList<string> list)
    {

        int counter = 1;
        DataTable dataTable = new DataTable();
        dataTable.Columns.Add("Position", typeof (int));
        dataTable.Columns.Add("Candidate", typeof (string));

        foreach (var candidate in list)
        {
            DataRow dataRow = dataTable.NewRow();
            dataRow["Position"] = counter++;
            dataRow["Candidate"] = candidate;
            dataTable.Rows.Add(dataRow);
        }

        SqlParameter tableType = new SqlParameter("@Row" , SqlDbType.Structured)
        {
            TypeName = "dbo.pe_StringList",
            Value = dataTable
        };

        string query = " SELECT * FROM dbo.pe_Get_Manufacturer(@Row)";

        SqlCommand sqlCommand = new SqlCommand(query, conn);
        sqlCommand.Parameters.AddWithValue("@Row", tableType);

        return sqlCommand.ExecuteScalar().ToString();

    }

这是我从异常中得到的信息:

“System.Data.dll 中出现类型为‘System.ArgumentException’的异常，但未在用户代码中处理

Additional information: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type."

编辑----这是我创建的表类型

CREATE TYPE pe_StringList
AS TABLE 
(
    Position INT,
    Candidate VARCHAR(50)
)

我更改了查询

string query = " SELECT * FROM dbo.pe_Get_Manufacturer(@Row)";

到

string query = " SELECT dbo.pe_Get_Manufacturer(@Row)";

Answer 1

只需更改:

sqlCommand.Parameters.AddWithValue("@Row", tableType);

成为:

sqlCommand.Parameters.Add(tableType);

因为它已经是一个 SqlParameter 类型。您将使用 AddWithValue 即时创建 SqlParameter(尽管请参阅底部关于不使用该弃用方法的说明)，但您已经创建了SqlParameter 并且只需要将它添加到集合中即可。

是的，从纯 T-SQL 语法的角度来看，从查询中删除 * FROM 也是必要的。

---

旁注:最好不要在任何情况下使用 AddWithValue ;-):

• Difference between Parameters.Add and Parameters.AddWithValue
• Can we stop using AddWithValue() already?