How to Efficiently Pass Table Value Parameters to SQL Server Stored Procedures from .NET?

.NET to SQL Server: Optimizing Stored Procedure Table Parameter Passing

Migrating databases to SQL Server 2008 often necessitates leveraging table-valued parameters within stored procedures for efficient data transfer. This approach significantly improves performance over older methods like comma-separated strings, which are now considered outdated.

This article demonstrates how to effectively pass data to stored procedures using DataTable, DbDataReader, or IEnumerable<SqlDataRecord> in your .NET code. The following example showcases the use of DataTable and IEnumerable<SqlDataRecord>:

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

public class StoredProcedureParameters
{
    public static void ExecuteProcedure(bool useDataTable, string connectionString, IEnumerable<long> ids)
    {
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "dbo.procMergePageView";
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter parameter;
                if (useDataTable)
                {
                    parameter = command.Parameters.AddWithValue("@Display", CreateDataTable(ids));
                }
                else
                {
                    parameter = command.Parameters.AddWithValue("@Display", CreateSqlDataRecords(ids));
                }
                parameter.SqlDbType = SqlDbType.Structured;
                parameter.TypeName = "dbo.PageViewTableType";

                command.ExecuteNonQuery();
            }
        }
    }

    private static DataTable CreateDataTable(IEnumerable<long> ids)
    {
        var table = new DataTable();
        table.Columns.Add("ID", typeof(long));
        foreach (var id in ids)
        {
            table.Rows.Add(id);
        }
        return table;
    }

    private static IEnumerable<SqlDataRecord> CreateSqlDataRecords(IEnumerable<long> ids)
    {
        var metaData = new SqlMetaData[1];
        metaData[0] = new SqlMetaData("ID", SqlDbType.BigInt);
        var record = new SqlDataRecord(metaData);
        foreach (var id in ids)
        {
            record.SetInt64(0, id);
            yield return record;
        }
    }
}

By setting SqlDbType to Structured and mapping TypeName to the corresponding table type defined in your stored procedure (dbo.PageViewTableType in this example), you ensure seamless and optimized data transmission. This method guarantees efficient and reliable communication between your .NET application and SQL Server stored procedures.

The above is the detailed content of How to Efficiently Pass Table Value Parameters to SQL Server Stored Procedures from .NET?. For more information, please follow other related articles on the PHP Chinese website!