如何解决C#向SQL Server存储过程传DataTable的类型冲突问题

问题场景 #

在将C#中的DataTable作为参数传递给SQL Server存储过程时，触发以下类型不兼容错误，已尝试调整列顺序但问题未解决，需要定位并修复：

Msg 206, Level 16, State 2, Procedure usp_UpdateQuantityInWorkOrders, Line 0

Operand type clash: table is incompatible with MultipleQuantitiesType

相关代码 #

C# 调用代码 #

objCmd = new SqlCommand("usp_UpdateQuantityInWorkOrders", objConn);
objCmd.CommandType = CommandType.StoredProcedure;

var multipleQuantitiesDataTable = ConvertListToDataTable(request.MultipleQuantitiesViewModel.MultipleQuantities);

objCmd.Parameters.AddWithValue("@multipleQuantities", multipleQuantitiesDataTable);
objCmd.Parameters.AddWithValue("@SelectedWorkOrdersType", request.SelectedWorkOrdersType);
objCmd.Parameters.AddWithValue("@OldProductCode", request.OldProduct.ProductCode);
objCmd.Parameters.AddWithValue("@NewProductCode", request.NewProduct.ProductCode);

SqlParameter parameter = new SqlParameter("@affectedRecords", SqlDbType.Int);
parameter.Direction = ParameterDirection.Output;
objCmd.Parameters.Add(parameter);

objConn.Open();
objCmd.ExecuteNonQuery();
objConn.Close();

int output = Convert.ToInt32(objCmd.Parameters["@affectedRecords"].Value);

SQL Server 存储过程定义 #

create procedure usp_UpdateQuantityInWorkOrders
    @multipleQuantities as MultipleQuantitiesType readonly,
    @SelectedWorkOrdersType nvarchar(100) = null,
    @OldProductCode as nvarchar(50),
    @NewProductCode as nvarchar(50),
    @affectedRecords integer output
as
begin try
    --Commented the complete code for testing purposes
end try
begin catch
    rollback transaction myTran
    declare @Message varchar(MAX) = ERROR_MESSAGE()
    declare @Severity int = ERROR_SEVERITY()
    declare @State smallint = ERROR_STATE()
    RAISERROR (@Message, @Severity, @State)
end catch

自定义表类型定义 #

CREATE TYPE dbo.MultipleQuantitiesType AS TABLE
(
    OldProductCode varchar(16) NULL,
    OldProductId int NULL,
    OldQuantity int NULL,
    NewProductCode varchar(16) NULL,
    NewProductId int NULL,
    NewQuantity int NULL
)

问题定位与修复方案 #

1. 显式指定表参数的类型信息（核心修复） #

使用AddWithValue传递表类型参数时，SQL Server无法自动识别自定义表类型的元数据，必须显式指定SqlDbType.Structured和对应的类型名称：

// 替换原来的AddWithValue表参数代码
var tableParam = new SqlParameter("@multipleQuantities", SqlDbType.Structured);
tableParam.Value = multipleQuantitiesDataTable;
// 指定自定义表类型的完整名称（schema + 类型名）
tableParam.TypeName = "dbo.MultipleQuantitiesType";
objCmd.Parameters.Add(tableParam);

2. 校验DataTable与自定义表类型的完全匹配 #

检查ConvertListToDataTable生成的DataTable是否满足以下要求：

• 列名完全一致：包括拼写、大小写（SQL Server默认不区分大小写，但C# DataTable列名需与类型列名完全匹配）
• 数据类型严格对应：
  • OldProductCode：C#中对应string类型，长度不超过16
  • OldProductId/OldQuantity/NewProductId/NewQuantity：C#中对应int类型（不可为string或其他类型）
• 无多余/缺失列：确保DataTable的列数量、列名与MultipleQuantitiesType完全一致

3. 验证自定义表类型的有效性 #

• 确认MultipleQuantitiesType是在目标数据库的dbo架构下创建的
• 存储过程中引用的类型名称需与实际创建的类型全名一致（若类型不在dbo下，需指定完整架构名）

4. 修复事务逻辑（可选） #

当前存储过程的catch块中执行了rollback transaction myTran，但try块中无对应的begin transaction myTran，这可能导致额外错误，建议补充事务开启逻辑或移除无效的回滚语句。

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