C#向SQL Server更新文件字节时的用户定义表类型数据传递错误

Microsoft.Data.SqlClient.SqlException: '不允许从数据类型nvarchar(max)隐式转换为varbinary(max)。请使用CONVERT函数运行此查询。'

相关代码与定义 #

C# 批量更新方法 #

public async Task<dynamic> UpdateBulkData(IList<EodUpdateDto> eods)
{
    string strTrace = MethodLogger.GetExecutionInfo(MethodBase.GetCurrentMethod());
    logger.LogDebug($"Executing {strTrace} with parameter : {JsonSerializer.Serialize(eods)}");
 
    string sqlQuery = $"{StoredProcedures.UPDATE_BULK_EOD} @EODS";
    var paramEods = new SqlParameter() {{ ParameterName = "@EODS", Value = eods.ToDataTable(), SqlDbType = SqlDbType.Structured, TypeName = "EODType" }};
    await sqlExecutor.ExecuteSqlRawAsync(sqlQuery, [paramEods]);

    return true;
}

列表转DataTable扩展方法 #

public static DataTable ToDataTable<T>(this IList<T> items)
{
     DataTable dataTable = new(typeof(T).Name);
 
     //Get all the properties
     PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
     foreach (PropertyInfo prop in Props)
     {
         //Defining type of data column gives proper data table 
         dynamic? type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
         //Setting column names as Property names
         dataTable.Columns.Add(prop.Name, type);
     }
     if (items != null && items.Count > 0)
     {
         foreach (T item in items)
         {
             dynamic values = new object[Props.Length];
             for (int i = 0; i < Props.Length; i++)
             {
                 //inserting property values to datatable rows
                 values[i] = Props[i].GetValue(item, null);
             }
             dataTable.Rows.Add(values);
         }
     }
     //put a breakpoint here and check datatable
     return dataTable;
}

EodUpdateDto 模型类 #

public class EodUpdateDto
{
    public int Id { get; set; }
    public string ActionBy { get; set; } = default!;
    public bool Accepted { get; set; }
    public bool VerifySelfPay { get; set; }
    public string Claim { get; set; } = default!;
    public bool OnHold { get; set; }
    public string HoldReason { get; set; } = default!;
    public string AttachFileName { get; set; } = default!;
    public string AttachFileType { get; set; } = default!;
    public byte[]? AttachFileContent { get; set; } = default!;
}

SQL Server 用户定义表类型 EODType #

CREATE TYPE [dbo].[EODType] AS TABLE
(
    [ID] [int] NULL,
    [ActionBy] [varchar](200) NOT NULL,
    [Accepted] [bit] NULL,
    [VerifySelfPay] [bit] NULL,
    [Claim] [varchar](50) NULL,
    [OnHold] [bit] NULL,
    [HoldReason] [varchar](200) NULL,
    [AttachFileName] [varchar](300) NULL,
    [AttachFileContent] [varbinary](max) NULL,
    [AttachFileType] [varchar](50) NULL
)

SQL Server 存储过程 usp_UpdateEODData_U #

ALTER PROCEDURE [dbo].[usp_UpdateEODData_U]
    @EODS [dbo].[EODType] READONLY
AS
BEGIN
    SET NOCOUNT ON;
 
    UPDATE EOD 
    SET 
        Accepted = E2.Accepted, 
        VerifySelfPay = E2.VerifySelfPay, 
        Claim = E2.Claim, 
        OnHold = E2.OnHold, 
        HoldReason = E2.HoldReason,
        HoldDate = IIF(E2.HoldReason IS NOT NULL, dbo.GetDateEST(), null),
        AttachFileName = E2.AttachFileName,
        AttachFileContent = E2.AttachFileContent,
        AttachFileType = E2.AttachFileType,
        ActionBy = E2.ActionBy
    FROM EOD E1
    INNER JOIN @EODS E2 ON E1.ID = E2.ID;
END

问题原因与解决方案 #

原因 #

ToDataTable方法处理byte[]?类型的AttachFileContent属性时，直接将byte[]作为DataTable列类型，但DataTable会将该类型映射为nvarchar(max)，与SQL Server用户定义表类型中的varbinary(max)列不匹配，触发隐式转换错误。

解决办法 #

修改ToDataTable方法，针对byte[]类型显式指定DataTable列的类型为对应varbinary(max)的配置：

public static DataTable ToDataTable<T>(this IList<T> items)
{
    DataTable dataTable = new(typeof(T).Name);

    PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in Props)
    {
        Type? columnType;
        // 处理可空类型
        if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            columnType = Nullable.GetUnderlyingType(prop.PropertyType);
        }
        else
        {
            columnType = prop.PropertyType;
        }

        // 针对byte[]类型，创建对应varbinary(max)的列
        DataColumn column;
        if (columnType == typeof(byte[]))
        {
            column = new DataColumn(prop.Name, typeof(byte[]));
            column.MaxLength = -1; // 标记为max长度
        }
        else
        {
            column = new DataColumn(prop.Name, columnType);
        }

        dataTable.Columns.Add(column);
    }

    if (items != null && items.Count > 0)
    {
        foreach (T item in items)
        {
            object[] values = new object[Props.Length];
            for (int i = 0; i < Props.Length; i++)
            {
                // 替换null为DBNull.Value，避免DataTable空值问题
                values[i] = Props[i].GetValue(item, null) ?? DBNull.Value;
            }
            dataTable.Rows.Add(values);
        }
    }

    return dataTable;
}

补充说明 #

• 新增byte[]类型判断，显式配置列对应SQL的varbinary(max)类型
• 处理属性值为null的情况，替换为DBNull.Value，避免DataTable插入空值时的异常

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