Home > Backend Development > C++ > How to Efficiently Pass Table Value Parameters to SQL Server Stored Procedures from .NET?

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

Linda Hamilton
Release: 2025-01-31 00:21:08
Original
336 people have browsed it

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;
        }
    }
}
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template