How to Pass a List of Strings to a SQL Server Stored Procedure Using a User-Defined Table Type?

Passing a list of strings to a SQL Server stored procedure using a user-defined table type

When calling a SQL Server stored procedure from C#, you may need to pass a list of strings as parameters. While standard parameters are sufficient for handling simple data types, for complex data structures such as lists, a more sophisticated approach is required.

To pass a list of strings, we can leverage the User-Defined Table Type (UDTT) feature of SQL Server. This feature allows us to create a custom data type that simulates a table to store a collection of data.

Create user-defined table type

First, we need to create a User-Defined Table Type (UDTT):

<code class="language-sql">CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);</code>

This UDTT defines a single column named "Item" to hold strings.

Modify stored procedure

Next, we modify the stored procedure to accept UDTT parameters:

<code class="language-sql">CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- 只返回我们传入的项目
    SELECT l.Item FROM @list l;
END</code>

Populating UDTT in C#

In C# we use DataTable to populate UDTT:

<code class="language-csharp">using (var table = new DataTable()) {
    table.Columns.Add("Item", typeof(string));

    // ...此处添加循环填充字符串列表到DataTable的代码...

    SqlParameter pList = new SqlParameter("@list", SqlDbType.Structured);
    pList.Value = table;
}</code>

Pass UDTT parameters to stored procedures

Finally, we pass the UDTT parameters to the stored procedure:

<code class="language-csharp">using (var cmd = new SqlCommand("exec sp_UseStringList @list", con)) {
    cmd.Parameters.Add(pList);

    using (var dr = cmd.ExecuteReader()) {
        while (dr.Read())
            Console.WriteLine(dr["Item"].ToString());
    }
}</code>

This approach allows us to efficiently pass a list of strings to a SQL Server stored procedure.

The above is the detailed content of How to Pass a List of Strings to a SQL Server Stored Procedure Using a User-Defined Table Type?. For more information, please follow other related articles on the PHP Chinese website!