How to insert byte[] array with ORMlite into image column

Question

A subtask of my webservice is to save a file (along with some meta data) in a database.
The webservice is based on ServiceStack and its version of ORMlite.

So I've created a small class which represents the attachment in the database:

public class Attachment {
    public string Description { get; set; }
    public string FileName { get; set; }
    public string Type { get; set; }
    public byte[] Data { get; set; }
}

And here is the actual file

MemoryStream ms = new MemoryStream(webclient.DownloadData(...));
byte[] data = new byte[...];
ms.Read(data, 0, data.Length);

Attachment file = new Attachment() {
    /* all the other stuff */
    Data = data
};

No problems until now... :)

Now I have all I need to put this file into database. So lets get it on...

dbCmd.Insert<Attachment>(file);

And there is the problem...

SqlException: "Operand type clash: text is incompatible with image"

ORMlite transforms the byte array into a base64 encoded string

/* stripped-down example of the command string */
INSERT INTO Attachment (Data) VALUES ('CgoKCgoKCjxodG1sPgo8a...AAAA==')

I've searched all day long but did not find a solution to change the way ORMlite handles byte[] arrays. There is no attribute DatabaseField which I could use to set the dataType to BYTE_ARRAY as it is possible in Java.

@DatabaseField(dataType = DataType.BYTE_ARRAY)
byte[] imageBytes;

Have I missed something essential?
Is there another way of getting the file into the database?

Answer 1

In ServiceStack.OrmLite v4, as specified by mythz in the comments, everything should work as-is.

---

In v3, if you want to have byte arrays serialized as binary instead of base 64 strings, you should inherit from SqliteOrmLiteDialectProvider and override the functions responsible for converting CLR values from and to SQL values to handle byte arrays.

public class ByteArrayAwareSqliteOrmLiteDialectProvider
    : SqliteOrmLiteDialectProvider {

    public override object ConvertDbValue(object value, Type type) {
        var bytes = value as byte[];
        if (bytes != null && type == typeof(byte[]))
            return bytes;

        return base.ConvertDbValue(value, type);
    }

    public override string GetQuotedValue(object value, Type fieldType) {
        var bytes = value as byte[];
        if (bytes != null && fieldType == typeof(byte[]))
            return "X'" + BitConverter.ToString(data).Replace("-", "") + "'";

        return base.GetQuotedValue(value, fieldType);
    }

}

Then, configure your dialect provider properly:

OrmLiteConfig.DialectProvider = new ByteArrayAwareSqliteOrmLiteDialectProvider();