Technical Implementation of Passing String Lists to Stored Procedures in C# and SQL Server

Technical Background and Problem Analysis

In modern enterprise application development, the integration of C# and SQL Server represents a common technological combination. Developers frequently encounter scenarios requiring the passing of dynamically generated data collections to stored procedures for batch processing. Particularly in operations such as data filtering and batch updates, traditional approaches often face performance bottlenecks and code complexity issues.

Core Solution: User Defined Table Types

User Defined Table Types, introduced in SQL Server 2008, provide an elegant solution for passing list data. This mechanism allows the definition of reusable table structures at the database level, serving as parameter types for stored procedures, thereby enabling efficient data transmission.

Database-Level Configuration

First, it is necessary to create a User Defined Table Type in the database, defining the data structure for receiving string lists:

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

This type defines a table structure containing a single string field, used for storing the incoming string list. The NVARCHAR(MAX) type ensures the ability to handle strings of various lengths.

Stored Procedure Modification

Next, the stored procedure needs to be modified or created to accept the newly defined table type parameter:

CREATE PROCEDURE [dbo].[sp_ProcessStringList]
    @list StringList READONLY
AS
BEGIN
    SELECT feature 
    FROM table1 t1 
    INNER JOIN @QueryTable t2 ON t1.fid = t2.fid 
    WHERE title IN (SELECT Item FROM @list)
END

The key here is the use of the READONLY keyword, ensuring that the table-valued parameter is not modified within the stored procedure, aligning with parameter passing semantics.

C# Code Implementation

On the C# side, table data needs to be passed using parameters of type SqlDbType.Structured. The following are two common implementation approaches:

Implementation Using DataTable

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    using (var command = new SqlCommand("sp_ProcessStringList", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        
        // Create DataTable and populate data
        var dataTable = new DataTable();
        dataTable.Columns.Add("Item", typeof(string));
        
        foreach (var item in stringList)
        {
            dataTable.Rows.Add(item);
        }
        
        // Configure table-valued parameter
        var parameter = new SqlParameter("@list", SqlDbType.Structured)
        {
            TypeName = "dbo.StringList",
            Value = dataTable
        };
        
        command.Parameters.Add(parameter);
        
        // Execute stored procedure
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // Process returned results
                Console.WriteLine(reader["feature"].ToString());
            }
        }
    }
}

Implementation Using IEnumerable<SqlDataRecord>

For scenarios requiring higher performance, a custom collection class can be implemented:

public class StringCollection : List<string>, IEnumerable<SqlDataRecord>
{
    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
    {
        var record = new SqlDataRecord(
            new SqlMetaData("Item", SqlDbType.NVarChar, -1));
            
        foreach (var item in this)
        {
            record.SetString(0, item);
            yield return record;
        }
    }
    
    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return ((IEnumerable<SqlDataRecord>)this).GetEnumerator();
    }
}

Performance Optimization and Best Practices

In practical applications, the following optimization strategies should be considered:

Batch Size Control

When processing large volumes of data, it is advisable to transmit data in batches to avoid performance issues caused by transmitting excessive data in a single operation. Appropriate batch sizes can be set based on specific business scenarios.

Parameter Validation

Parameter validation should be performed both on the C# side and the database side to ensure data integrity and consistency. Particularly when handling user input, security risks such as SQL injection need to be prevented.

Error Handling

A comprehensive error handling mechanism is crucial for ensuring system stability. Various potential exception scenarios, such as database connection failures and type mismatches, should be caught and handled appropriately.

Technical Comparison and Selection Recommendations

Compared to traditional string concatenation or multiple stored procedure calls, using table-valued parameters offers significant advantages:

Performance Advantages

Table-valued parameters avoid multiple database round trips, significantly improving processing efficiency. Particularly in scenarios involving hundreds of strings, the performance improvement is more pronounced.

Code Maintainability

This approach makes the code clearer, logic more centralized, and facilitates subsequent maintenance and extension.

Type Safety

Through strong type definitions, type errors can be detected at compile time, reducing the occurrence of runtime exceptions.

Practical Application Scenario Extensions

Beyond string lists, this technique is equally applicable to passing lists of other data types, such as integer lists or date lists. Only the field definitions of the User Defined Table Type need to be adjusted accordingly.

In enterprise-level applications, this technique is commonly used in scenarios such as: user permission verification, batch data import/export, and complex query condition passing. By appropriately utilizing table-valued parameters, a data access layer that is both efficient and easy to maintain can be constructed.