I have this block of code that inserts data into tables like this:
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;
}
And this is the request DTO:
public class InsertRowRequest
{
public string DbAlias { get; set; }
public string TableName { get; set; }
public string[] ColumnNames { get; set; }
public object[] RowItems { get; set; }
}
The problem I face now is that with tables with columns of varbinary type, it fails and I have no idea if there is any way I can make it work correctly.
Given this setup (MSSQL server):
create table SqlKataTable
(
Id INT NOT NULL,
Doc VARBINARY(MAX) NULL,
DocType NVARCHAR(255) NULL
)
The above method will generate a sql script for insert such as this:
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'
Which fails with this error message:
Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.
The problem is that for the varbinary column it generates a @p1 nvarchar(max) which makes sql server fail.
Any ideas on how to make this work with varbinary types?