How to Pass a List of Strings to a SQL Server Stored Procedure from C#?

Passing a List of Strings to a Stored Procedure in C#

When calling a SQL Server stored procedure from C#, passing a list of strings as a parameter can be challenging. However, there is a solution using the User Defined Table Type (UDTT) feature in SQL Server 2008 and later.

Creating the UDTT

First, create a UDTT to represent the list of strings:

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

Modifying the Stored Procedure

In the stored procedure, declare the UDTT as a parameter:

CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

Populating the UDTT in C#

In C#, create a DataTable and add a column of type string named "Item":

using System.Data;
using System.Data.SqlClient;

...

DataTable table = new DataTable();
table.Columns.Add("Item", typeof(string));

Fill the DataTable with the list of strings:

for (int i = 0; i < 10; i++)
    table.Rows.Add("Item " + i.ToString());

Create a SqlParameter to pass the DataTable as the UDTT:

SqlParameter pList = new SqlParameter("@list", SqlDbType.Structured);
pList.TypeName = "dbo.StringList";
pList.Value = table;

Passing the List to the Stored Procedure

Add the SqlParameter to the SqlCommand and execute the stored procedure:

using (SqlConnection con = new SqlConnection(connstring))
{
    con.Open();

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
    {
        cmd.Parameters.Add(pList);

        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
        }
    }
}

This approach allows you to pass a list of strings to a stored procedure in a structured format, enabling complex queries.

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