如何通过SqlDataRecord单次往返将多行导入临时表？

原循环执行INSERT的方式会产生多次数据库往返，针对动态类型T无法预定义表值类型的问题，以下是补全后的完整实现代码，同时兼顾性能与动态类型适配：

完整补全代码 #

using Microsoft.Data.SqlClient; // 或 System.Data.SqlClient，根据使用的库版本
using System.Data;

// 假设connection是已打开的SqlConnection实例，values是IEnumerable<object>类型，T是泛型参数
using (var cmd = connection.CreateCommand()) 
{
    cmd.CommandText = "INSERT INTO #Ids SELECT * FROM @0;";

    var param = cmd.CreateParameter();
    param.ParameterName = "@0";
    // 补全参数属性：标记为结构化表值参数，无需预定义数据库表类型
    param.SqlDbType = SqlDbType.Structured;
    // 自定义类型名称（无需在数据库中存在，仅作为SQL Server的标识用）
    param.TypeName = "TempIdList";
    cmd.Parameters.Add(param);

    var clrType = typeof(T);
    // 补全SqlDataRecord创建逻辑：根据CLR类型生成对应SQL元数据与记录
    var sqlDbType = GetSqlDbType(clrType);
    // 若为字符串类型，指定-1表示MAX长度，适配任意VARCHAR/NVARCHAR长度
    var metaData = clrType == typeof(string) 
        ? new SqlMetaData("IdValue", sqlDbType, -1) 
        : new SqlMetaData("IdValue", sqlDbType);

    param.Value = values.Cast<T>().Select(x => 
    {
        var record = new SqlDataRecord(metaData);
        record.SetValue(0, x);
        return record;
    }).ToList();

    cmd.ExecuteNonQuery();
}

// 辅助方法：CLR类型转SqlDbType
private static SqlDbType GetSqlDbType(Type clrType)
{
    return clrType switch
    {
        Type t when t == typeof(int) => SqlDbType.Int,
        Type t when t == typeof(long) => SqlDbType.BigInt,
        Type t when t == typeof(short) => SqlDbType.SmallInt,
        Type t when t == typeof(byte) => SqlDbType.TinyInt,
        Type t when t == typeof(string) => SqlDbType.NVarChar,
        Type t when t == typeof(Guid) => SqlDbType.UniqueIdentifier,
        Type t when t == typeof(DateTime) => SqlDbType.DateTime2,
        Type t when t == typeof(decimal) => SqlDbType.Decimal,
        Type t when t == typeof(bool) => SqlDbType.Bit,
        // 补充业务中需要支持的其他类型
        _ => throw new NotSupportedException($"不支持的CLR类型：{clrType.Name}")
    };
}

关键逻辑说明 #

1. 表值参数配置

   • 必须将参数的SqlDbType设为Structured，这是SQL Server识别表值参数的核心标识。
   • TypeName可自定义任意名称，无需在数据库中预先创建对应的用户自定义表类型，SQL Server会自动根据SqlDataRecord的元数据推断临时表结构。

2. 动态类型适配

   • 通过GetSqlDbType方法完成CLR类型到SqlDbType的映射，覆盖业务中所有可能的动态类型T。
   • 针对字符串类型，使用-1指定MAX长度，避免为不同VARCHAR/NVARCHAR长度创建专属类型，完美适配临时表的列类型。

3. 性能优势

   • 无论数据行数多少（从平均10行到1000+行），仅需一次数据库往返，彻底消除循环INSERT的性能损耗。
   • 相比DataTable，SqlDataRecord的内存开销更低，更适合长尾分布的数据场景。

注意事项 #

• 确保临时表#Ids的列类型与传入的SqlDataRecord列类型兼容（如#Ids列是INT则不能传入字符串值）。
• 若使用旧版System.Data.SqlClient，代码逻辑完全一致，仅命名空间不同。

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