C # user-defined call A scalar function in SQL Server that takes a table type as a parameter

Question

I hit my head on a stone. I wrote a scalar function that takes the type of table I created as a parameter and returns a simple varchar, here is the sql code if it helps

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

on my C # side, I have a function that accepts a list of strings that should be placed in Datatable to be used as a parameter to the function. I believe that I wrote it all correctly, as my project always throws when it runs ExecuteScalar on SQLCommand

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

    }

This is the information I receive from the exception:

"An exception of type" System.ArgumentException "occurred in System.Data.dll, but was not processed in user code

: System.Data.SqlClient.SqlParameter ".

----

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 .

, * FROM T-SQL.

---

: AddWithValue , -; -):

• .Add Parameters.AddWithValue
• AddWithValue() ?

Answer 2

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

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

, , SqlParameter, .

SqlParameterCollection.AddWithValue