SQLKata插入VARBINARY类型数据失败问题求助

使用SqlKata向SQL Server插入数据时，遇到VARBINARY类型列插入失败的问题，错误提示SQL Server不允许从nvarchar隐式转换到varbinary。

现有代码 #

插入方法 #

public async Task<int> InsertRowAsync(InsertRowRequest request)
{
    using var conn = _dbConnectionContext.CreateConnection(request.DbAlias);
    using var db = new QueryFactory(conn, new SqlServerCompiler());

    var data = new List<KeyValuePair<string, object>>();
    for (int j = 0; j < request.ColumnNames.Length; j++)
    {
        data.Add(new KeyValuePair<string, object>(request.ColumnNames[j], request.RowItems[j].ToString()));
    }
    var id = await db.Query(request.TableName).InsertGetIdAsync<int>(data);
    return id;
}

请求DTO定义 #

public class InsertRowRequest
{
    public string DbAlias { get; set; }
    public string TableName { get; set; }
    public string[] ColumnNames { get; set; }
    public object[] RowItems { get; set; }
}

目标表结构 #

create table SqlKataTable
(
    Id INT NOT NULL,
    Doc VARBINARY(MAX) NULL,
    DocType NVARCHAR(255) NULL
)

生成的SQL及错误信息 #

生成的执行SQL：

exec sp_executesql N'INSERT INTO [SqlKataTable] ([Id], [Doc], [DocType]) VALUES (@p0, @p1, @p2);SELECT scope_identity() as Id'
,N'@p0 nvarchar(4000),@p1 nvarchar(max) ,@p2 nvarchar(4000)'
,@p0=N'19751',@p1=N'asdasdasd',@p2=N'pdf'

报错内容：

Msg 257, Level 16, State 3, Line 13
Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

问题根源：代码中把所有RowItems都强制转换为字符串，导致VARBINARY列对应的参数被识别为nvarchar类型，无法完成隐式转换。

方案1：查询表列类型，针对性转换数据 #

在插入前先查询目标表的列类型，识别出VARBINARY类型的列，将对应的JSON传入值（通常是Base64字符串）解码为byte[]后再插入。

修改后的插入方法示例：

public async Task<int> InsertRowAsync(InsertRowRequest request)
{
    using var conn = _dbConnectionContext.CreateConnection(request.DbAlias);
    using var db = new QueryFactory(conn, new SqlServerCompiler());

    // 获取目标表的列类型映射
    var columnTypeMap = await GetTableColumnTypes(conn, request.TableName);

    var data = new List<KeyValuePair<string, object>>();
    for (int j = 0; j < request.ColumnNames.Length; j++)
    {
        var colName = request.ColumnNames[j];
        var value = request.RowItems[j];

        // 判断当前列是否为VARBINARY类型
        if (columnTypeMap.TryGetValue(colName, out var dataType) 
            && dataType.Equals("varbinary", StringComparison.OrdinalIgnoreCase))
        {
            // JSON传入的二进制数据一般是Base64格式，解码为byte数组
            if (value is string base64Str)
            {
                data.Add(new KeyValuePair<string, object>(colName, Convert.FromBase64String(base64Str)));
            }
            else
            {
                // 处理直接传入byte[]的情况（若JSON序列化支持）
                data.Add(new KeyValuePair<string, object>(colName, value));
            }
        }
        else
        {
            // 其他类型保持原有处理逻辑（可根据实际需求调整）
            data.Add(new KeyValuePair<string, object>(colName, value?.ToString()));
        }
    }

    var id = await db.Query(request.TableName).InsertGetIdAsync<int>(data);
    return id;
}

// 辅助方法：获取SQL Server表的列类型信息
private async Task<Dictionary<string, string>> GetTableColumnTypes(SqlConnection conn, string tableName)
{
    var columnTypeMap = new Dictionary<string, string>();
    var querySql = @"
        SELECT COLUMN_NAME, DATA_TYPE 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @TableName";

    using var cmd = new SqlCommand(querySql, conn);
    cmd.Parameters.AddWithValue("@TableName", tableName);

    if (conn.State != ConnectionState.Open)
    {
        await conn.OpenAsync();
    }

    using var reader = await cmd.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        var colName = reader.GetString(0);
        var dataType = reader.GetString(1);
        columnTypeMap.Add(colName, dataType);
    }

    return columnTypeMap;
}

方案2：在请求DTO中传递列类型信息 #

如果前端可以提前知晓列类型，可在DTO中新增ColumnTypes字段，直接告知后端哪些列是VARBINARY类型，避免查询数据库。

修改后的DTO：

public class InsertRowRequest
{
    public string DbAlias { get; set; }
    public string TableName { get; set; }
    public string[] ColumnNames { get; set; }
    public string[] ColumnTypes { get; set; } // 新增：对应每个列的数据类型
    public object[] RowItems { get; set; }
}

修改后的插入方法：

public async Task<int> InsertRowAsync(InsertRowRequest request)
{
    using var conn = _dbConnectionContext.CreateConnection(request.DbAlias);
    using var db = new QueryFactory(conn, new SqlServerCompiler());

    var data = new List<KeyValuePair<string, object>>();
    for (int j = 0; j < request.ColumnNames.Length; j++)
    {
        var colName = request.ColumnNames[j];
        var value = request.RowItems[j];
        var colType = request.ColumnTypes[j];

        if (colType.Equals("varbinary", StringComparison.OrdinalIgnoreCase))
        {
            if (value is string base64Str)
            {
                data.Add(new KeyValuePair<string, object>(colName, Convert.FromBase64String(base64Str)));
            }
            else
            {
                data.Add(new KeyValuePair<string, object>(colName, value));
            }
        }
        else
        {
            data.Add(new KeyValuePair<string, object>(colName, value?.ToString()));
        }
    }

    var id = await db.Query(request.TableName).InsertGetIdAsync<int>(data);
    return id;
}

方案3：使用SqlKata RawValue指定参数类型 #

若已知目标表的VARBINARY列名，可直接构造带指定类型的参数，跳过自动类型推断：

// 示例：针对名为Doc的VARBINARY列
byte[] byteData = Convert.FromBase64String(request.RowItems[j].ToString());
data.Add(new KeyValuePair<string, object>("Doc", RawValue.Create("@docParam", new SqlParameter("@docParam", SqlDbType.VarBinary) { Value = byteData })));

此方式适合固定表结构的场景，无需动态查询列类型。

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