CODEWITHSUNDEEP
c#sql-server-2012dapper
Michael B
Michael B

Reputation: 7577

Dapper and Varbinary(max) stream parameters

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?

Upvotes: 4

Views: 5618

Answers (1)

David C
David C

Reputation: 3810

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

Upvotes: 5

Related Questions