使用SqlParameter批量插入SQL Server时遇Object[]转Decimal类型转换错误

错误原因 #

你当前的代码试图将整列数据以Object[]的形式赋值给单个SqlParameter的Value属性，但SQL Server的常规参数（非表值参数）仅接受单个值，而非数组。当列类型为Decimal时，系统尝试把Object[]转换成Decimal类型，因此抛出转换失败的错误。

解决方案 #

以下三种方法可解决该问题，根据你的数据量和场景选择：

方法一：使用表值参数（推荐） #

表值参数（TVP）是SQL Server专门用于批量数据传递的特性，支持直接传入DataTable作为参数，无需拆分数据。

1. 在SQL Server中创建表值类型
   根据你的DataTable结构，创建对应的表值类型（列名、类型需匹配）：

   CREATE TYPE YourBatchType AS TABLE (
       Column1 INT,
       Column2 DECIMAL(18,2),
       -- 其他列按实际结构定义
   )
   

2. 修改C#代码
   无需循环列，直接将DataTable作为表值参数传入：

   // 构建表值参数
   SqlParameter tvpParam = new SqlParameter
   {
       ParameterName = "@BatchData",
       SqlDbType = SqlDbType.Structured,
       TypeName = "dbo.YourBatchType", // 对应创建的表值类型名称
       Value = data // 直接传入你的DataTable
   };
   
   // 构建插入SQL（替换为你的目标表和列名）
   cmd.CommandText = @"
       INSERT INTO YourTargetTable (Column1, Column2)
       SELECT Column1, Column2 FROM @BatchData
   ";
   cmd.Parameters.Add(tvpParam);
   
   int result = cmd.ExecuteNonQuery();
   

   如果需要分批上传，只需将DataTable拆分为多个小DataTable，重复执行上述逻辑即可。

方法二：构建批量INSERT语句（适合小批量数据） #

如果不想创建表值类型，可以动态构建包含多组值的INSERT语句，为每个字段单独创建参数：

// 获取列名（转义避免关键字冲突）
var columnNames = string.Join(", ", data.Columns.Cast<DataColumn>().Select(c => $"[{c.ColumnName}]"));

var valuePlaceholders = new List<string>();
var parameters = new List<SqlParameter>();
int paramIndex = 0;

// 筛选要插入的行（支持分批）
var targetRows = UploadEverythingInSingleBatch 
    ? data.AsEnumerable() 
    : data.AsEnumerable().Skip(skipOffset).Take(batchSize);

foreach (var row in targetRows)
{
    var rowParams = new List<string>();
    foreach (DataColumn col in data.Columns)
    {
        var paramName = $"@p{paramIndex}";
        rowParams.Add(paramName);
        
        // 创建参数并处理空值
        var param = new SqlParameter(paramName, GetSqlDbTypeFromDotnetType(col.DataType))
        {
            Value = row.IsNull(col) ? DBNull.Value : row[col]
        };
        parameters.Add(param);
        paramIndex++;
    }
    valuePlaceholders.Add($"({string.Join(", ", rowParams)})");
}

// 构建完整SQL
cmd.CommandText = $"INSERT INTO YourTargetTable ({columnNames}) VALUES {string.Join(", ", valuePlaceholders)}";
cmd.Parameters.AddRange(parameters.ToArray());

int result = cmd.ExecuteNonQuery();

注意：此方法受SQL语句长度限制，不适合超大规模批量插入。

方法三：使用SqlBulkCopy（适合超大量数据） #

如果数据量极大，SqlBulkCopy是性能最优的选择，它直接将数据批量写入目标表：

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_connection))
{
    bulkCopy.DestinationTableName = "YourTargetTable";
    
    // 映射列名（如果DataTable与目标表列名完全一致，可省略此步骤）
    foreach (DataColumn col in data.Columns)
    {
        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
    }
    
    // 处理分批逻辑
    DataTable batchData = UploadEverythingInSingleBatch 
        ? data 
        : data.AsEnumerable().Skip(skipOffset).Take(batchSize).CopyToDataTable();
    
    bulkCopy.WriteToServer(batchData);
}

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