How to Pass a C# String List to a SQL Server Stored Procedure?

Passing list to SQL Server stored procedure in C#

Question:

You need to pass the list of strings from your C# code to the SQL Server stored procedure. However, this list is dynamic and contains hundreds of items.

Solution:

Use user-defined table types (UDTT)

SQL Server 2008 and later offers User-Defined Table Types (UDTT), allowing you to create custom data types for use in stored procedures. Please follow these steps:

1. Create UDTT: Create a new UDTT using a SQL statement similar to the following:

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

2. Modify the stored procedure: Modify the stored procedure to receive UDTT as parameter:

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

3. Populate a DataTable in C#: Create a DataTable to hold a list of strings:

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

    for (int i = 0; i < 1000; i++) // 假设列表包含1000个项目
    {
        table.Rows.Add("Item " + i);
    }
}</code>

4. Create SqlParameter: Create a new SqlParameter using UDTT type:

<code class="language-csharp">var pList = new SqlParameter("@list", SqlDbType.Structured);
pList.TypeName = "dbo.StringList";
pList.Value = table;</code>

5. Execute the stored procedure: Execute the stored procedure and retrieve the results:

<code class="language-csharp">using (var con = new SqlConnection(connstring))
{
    con.Open();

    using (SqlCommand 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>

Remember to replace connstring with your own connection string. This code example assumes your list contains 1000 items, you will need to adjust the loop based on the actual list size. Additionally, ensure that your SQL Server instance has enabled the use of the READONLY parameter.

The above is the detailed content of How to Pass a C# String List to a SQL Server Stored Procedure?. For more information, please follow other related articles on the PHP Chinese website!