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

Passing a List of Strings to a SQL Server Stored Procedure from C#

This guide demonstrates how to pass a list of strings as a parameter to a SQL Server stored procedure from your C# application. This method utilizes a User-Defined Table Type (UDTT), suitable for SQL Server 2008 and later versions.

Defining the User-Defined Table Type (UDTT)

First, create a UDTT in SQL Server to represent your string list:

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

Modifying the Stored Procedure

Next, modify your stored procedure to accept the UDTT as input:

<code class="language-sql">CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Process the string list
    SELECT l.Item FROM @list l;
END</code>

C# Implementation

Here's how to use the UDTT within your C# code:

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

    using (SqlCommand cmd = new SqlCommand("sp_UseStringList", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@list", CreateStringListTable(new List<string> { "Apple", "Banana", "Orange" }));

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                // Process each string from the reader
                string item = reader["Item"].ToString();
                Console.WriteLine(item);
            }
        }
    }
}


//Helper function to create the DataTable
private DataTable CreateStringListTable(List<string> stringList)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Item", typeof(string));
    foreach (string str in stringList)
    {
        dt.Rows.Add(str);
    }
    return dt;
}</code>

Executing the Stored Procedure (SQL Example)

For testing purposes, you can execute the stored procedure directly in SQL Server Management Studio (SSMS) like this:

<code class="language-sql">DECLARE @list AS StringList;

INSERT INTO @list VALUES ('Apple');
INSERT INTO @list VALUES ('Banana');
INSERT INTO @list VALUES ('Orange');

EXEC sp_UseStringList @list;</code>

This approach offers a robust and efficient way to handle lists of strings when interacting with SQL Server stored procedures from your C# applications. The use of a UDTT ensures data integrity and improves performance compared to other methods.

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