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

C# SQL Server: Passing a List to a Stored Procedure

In C#, it is possible to call a SQL Server stored procedure through code. However, passing a list of strings is not straightforward. This article explores the alternative method of using a User Defined Table Type (UDT) instead.

Creating a User Defined Table Type (UDT)

SQL Server 2008 introduced UDTs, which provide a structured way to pass complex data types to stored procedures. To create a UDT for a list of strings:

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

Modifying the Stored Procedure

To accept the UDT parameter, the stored procedure needs to be modified as follows:

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

Passing the List from C# Code

Finally, here's how to pass the list from C# code:

using System.Data;

var con = new SqlConnection(connstring);
con.Open();

using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
{
    using (var table = new DataTable())
    {
        table.Columns.Add("Item", typeof(string));

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

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

        cmd.Parameters.Add(pList);

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

Conclusion

Using a UDT is an effective way to pass a list of strings to a SQL Server stored procedure from C# code. By following the steps outlined above, developers can easily handle dynamic and large lists of strings in their database interactions.

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