C# 调用SQL Server表值参数(TVP)存储过程获取数据集方法咨询

C# 调用TVP存储过程实现方案 #

1. 实现逻辑说明 #

你已经在数据库侧完成了自定义表类型（TVP）和存储过程的创建，C#侧只需要构造和TVP结构匹配的DataTable作为参数传入存储过程即可，原生ADO.NET原生支持该操作。

2. 完整代码示例 #

// .NET Framework 引用System.Data.SqlClient，.NET Core/.NET 5+ 引用Microsoft.Data.SqlClient
using System.Data;
using System.Data.SqlClient;

// 结果接收实体类，可根据实际字段类型调整
public class ServLocMeterResult
{
    public int ServLocID { get; set; }
    public int MeterID { get; set; }
    public string Stuff { get; set; }
}

/// <summary>
/// 调用存储过程查询结果
/// </summary>
/// <param name="servLocIds">需要查询的ServLocID集合</param>
/// <param name="connectionString">数据库连接字符串</param>
/// <returns>查询结果集合</returns>
public List<ServLocMeterResult> GetServLocMeters(List<int> servLocIds, string connectionString)
{
    var result = new List<ServLocMeterResult>();
    // 1. 构造和TVP结构完全匹配的DataTable
    var tvpTable = new DataTable();
    tvpTable.Columns.Add("ServLocID", typeof(int));
    // 批量填充需要查询的ID
    foreach (var id in servLocIds)
    {
        tvpTable.Rows.Add(id);
    }

    // 2. 建立数据库连接调用存储过程
    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand("dbo.sp_ServLocMeters", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            // 3. 添加TVP类型参数
            var tvpParam = cmd.Parameters.AddWithValue("@newServLocMeters", tvpTable);
            tvpParam.SqlDbType = SqlDbType.Structured;
            // 必须和数据库中自定义表类型的全限定名完全一致
            tvpParam.TypeName = "dbo.ServLocMeters";

            // 4. 读取返回结果
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    result.Add(new ServLocMeterResult
                    {
                        ServLocID = reader.GetInt32(0),
                        MeterID = reader.GetInt32(1),
                        Stuff = reader.IsDBNull(2) ? null : reader.GetString(2)
                    });
                }
            }
        }
    }
    return result;
}

3. 注意事项 #

• 即便你有4万条ServLocID需要传入，TVP也可以高效承载，性能远高于拼接IN查询字符串
• TypeName属性必须和数据库中自定义表类型的名称完全匹配，不可省略dbo.前缀
• DataTable的列名、数据类型、列顺序必须和自定义表类型dbo.ServLocMeters完全一致，不要添加多余列
• 如果你使用的是.NET Core/.NET 5+ 版本，需要先安装Microsoft.Data.SqlClient NuGet包，命名空间对应改为using Microsoft.Data.SqlClient;

内容的提问来源于stack exchange，提问作者Ryan