Dapper and Varbinary(max) stream parameters

c# dapper sql-server-2012

Question

I'm trying to store a binary blob in a database table and get an output back from it. The data is stored in a MemoryStream object. I'm trying to save this to a sql server 2012 table using query async. The call succeeds but no data is inserted into the column. (e.g. I get a 0x0 entry when I query it back).

Sure enough, actually checking a trace I see dapper sending a 0x0. The memorystream has a length so am I doing something wrong or does dapper not support this scenario? My query string is just a simple insert and gets the id and insertion time back.

I'm using the following call

using(var conn=new SqlConnection(_connstr)){

var dynParams = new DynamicParameters();
dynParams.Add("BinaryBlob",
_memoryStream,DbType.Binary,ParameterDirection.Input,-1);
var res = await conn.QueryAsync<MyResultType>(_queryStr, dynParams);
}

The query inserts a row and gets a timestamp back, but no data is actually inserted. What am I doing wrong?

Accepted Answer

Make sure you seek to the beginning of the Memory stream. Streams have a positional state. Another approach would be to convert the memory stream to a Byte[] before trying to persist it.

e.g.

_memorystream.Seek(0, SeekOrigin.Begin);



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why