How to handle varbinary columns in repodb queries?

Question

In a project includes RepoDB, it is used by queries but I have problem with varbinary columns.

Dictionary<string, object> param = new Dictionary<string, object>();
param.Add("MyContent", myObject.MyContent));
var sql = "INSERT INTO mytable(myvarbinarycolumn) values (@MyContent);
await connection.ExecuteNonQueryAsync(sql, param);

I got the error:

'The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.'

myObject.MyContent is a byte array but I tried also using string and encoding.

I already solved using classes and

connection.InsertAsync(myObject);

But I would like to know if there is a solution using queries and parameters.

EDIT: The question is about RepoDB, so using DbCommand or other ORM is not an option.

I would expect `TypeMapper.Add(DbType.Binary, true)` to be a default, but in case it's not it's worth trying. Another option is to explicitly use `new SqlBinary(myObject.MyContent)` for the parameter, but that does more or less defeat the purpose of using an ORM and you may in that case as well use regular old `DbCommand`s and `DbParameter`s (which is another option, of course). — Jeroen Mostert, May 16 '22 at 15:46

score 0 · Accepted Answer · answered May 16 '22 at 15:57

0

I found the solution using SqlBinary (thanks to @Jeroen):

param.Add("MyContent", myObject.MyContent == null ? DBNull.Value : new SqlBinary(myObject.MyContent ));

answered May 16 '22 at 15:57

Emanuele

648
12
33

1

Note that `SqlBinary` does tie you to SQL Server -- if that's not desirable, the obvious alternative would be declaring a `record BinaryBlob(byte[] Value)` yourself and using the same solution as for classes (`TypeMapper.Add(e => e.Value, DbType.Binary)`). You would then use `new BinaryBlob(myObject.MyContent)` for the parameter instead. Though it would be a bit surprising if RepoDB doesn't already have a solution along those lines itself; I'd say not treating `byte[]` special qualifies as a bug. – Jeroen Mostert May 16 '22 at 15:59
I can't find BinaryBlob. I need to understand if it's true. – Emanuele May 17 '22 at 07:43

score 0 · Answer 2 · answered May 25 '22 at 09:22

When passing an array to the parameters and if you use the Execute method, RepoDB will execute an array based parameters and not passing that array to the target column. See this link.

To solve this, you have to pass the proper value type to the list of argument like what you did above via new SqlBinary.

var param = new Dictionary<string, object>()
{
    { "Value", new SqlBinary(Encoding.UTF8.GetBytes("WhateverValue")) }
};
var sql = "INSERT INTO [TestTable]([Value]) VALUES (@Value);";
var result = connection.ExecuteNonQuery(sql, param);

How to handle varbinary columns in repodb queries?

2 Answers2