Question
When using BLOBs with more than 8000 bytes of data, you need to specifically set Parameter.SqlDbType = SqlDbType.Image to make it work (as explained here).
Dapper, when it sees a byte[] field, defaults to a SqlDbType.Binary, which means for larger blobs, the inserts and updates will fail with a data truncation error.
Is there an elegant solution to this problem? Only option I can see is to code the entire transaction with ADO.NET methods.
Popular Answer
I had the same problem. Resolved as follows:
private static IDbCommand SetupCommand(IDbConnection cnn, IDbTransaction transaction,
string sql, Action<IDbCommand, object> paramReader,
object obj, int? commandTimeout,
CommandType? commandType)
{
var cmd = cnn.CreateCommand();
var bindByName = GetBindByName(cmd.GetType());
if (bindByName != null) bindByName(cmd, true);
if (transaction != null)
cmd.Transaction = transaction;
cmd.CommandText = sql;
if (commandTimeout.HasValue)
cmd.CommandTimeout = commandTimeout.Value;
if (commandType.HasValue)
cmd.CommandType = commandType.Value;
if (paramReader != null)
{
paramReader(cmd, obj);
}
//CODTEC SISTEMAS
foreach (System.Data.SqlServerCe.SqlCeParameter item in cmd.Parameters)
{
if (item.SqlDbType == System.Data.SqlDbType.VarBinary)
item.SqlDbType = System.Data.SqlDbType.Image;
}
//CODTEC SISTEMAS
return cmd;
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why