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

C# and SQL Server: Efficiently Handling String Lists in Stored Procedures

A common challenge when interacting with SQL Server stored procedures from C# involves passing dynamic lists of string values. This article demonstrates a robust solution using User Defined Table Types (UDTTs).

The Problem: Dynamic String Lists

Imagine a stored procedure needing to filter results based on a variable-length list of strings. Directly passing this list can be problematic.

Solution: User Defined Table Types (UDTTs)

SQL Server 2008 and later versions offer UDTTs as an elegant solution. We'll create a UDTT called StringList to encapsulate our string collection.

Creating the Stored Procedure

This stored procedure uses the StringList type:

<code class="language-sql">CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Retrieve and return items from the input list
    SELECT l.Item FROM @list l;
END</code>

C# Implementation

The C# code below demonstrates how to utilize this stored procedure:

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

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

            // Add sample data – replace with your actual list
            for (int i = 0; i < 10; i++)
            {
                table.Rows.Add("String" + i);
            }

            // Add the DataTable as a parameter to the command.
            SqlParameter tvpParam = cmd.Parameters.AddWithValue("@list", table);
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.StringList";

            // Execute the command.
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                // Process the results
                while (reader.Read())
                {
                    Console.WriteLine(reader["Item"].ToString());
                }
            }
        }
    }
}</code>

SSMS Execution Example

For testing in SQL Server Management Studio (SSMS):

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

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

EXEC sp_UseStringList @list;</code>

This approach provides a clean, efficient method for passing lists of strings from C# to SQL Server stored procedures, leveraging the power of UDTTs for structured data transfer. Remember to replace "dbo.StringList" with the correct schema and name if you've altered them.

The above is the detailed content of How to 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!