Using Dapper with BLOBs and SQL Server CE

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.

If you take a look at the dapper source ( code.google.com/p/dapper-dot-net/source/browse/Dapper/… ) you could add a special case in the function "LookupDbType" returning the appropriate SqlDbType. No clue if it breaks something else though. — Alex
– Alex, Commented Mar 9, 2012 at 13:41
Thanks! I think I'll modify the static SqlMapper constructor and modify the following line: typeMap[typeof(byte[])] = DbType.Binary; — Sameera
– Sameera, Commented Mar 10, 2012 at 7:57
Ooh, that's a pain; I suspect you might be better logging this as a bug against dapper, though — Marc Gravell
– Marc Gravell, Commented Mar 11, 2012 at 20:05

Uwe Keim · Accepted Answer · 2018-04-10 05:44:07Z

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;
}

Collectives™ on Stack Overflow

Using Dapper with BLOBs and SQL Server CE

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related