A stored procedure returns, amongst other things, a varbinary(max) as an OUTPUT. I can't understand how to access this information using Dapper.
Below is some cut-down sample code which illustrates the problem. I supply some parameters to a StoredProcedure and I expect to get back a Photo which is stored as varbinary(max) on the SQL Server.
There is no DbType for varbinary. I tried using DbType.Binary but this causes an exception in Dapper. If I take the photo parameter away, all the other parameters I expect to get back (which are cut out of the sample for the sake of brevity) are working. So the only issue is with retrieving the varbinary data.
What is the correct way to achieve this?
using (var connection = new System.Data.SqlClient.SqlConnection(HelperClassesBJH.HelperMethods.ConString("ProductionLocal")))
{
connection.Open();
DynamicParameters p = new DynamicParameters();
p.Add("@OpID", id, DbType.Int32, ParameterDirection.Input);
p.Add("@StageID", Properties.Settings.Default.StageID, DbType.Int32, ParameterDirection.Input);
p.Add("@Photo", dbType: DbType.Binary, direction: ParameterDirection.Output);
try
{
connection.Execute(sql, p, commandType: CommandType.StoredProcedure);
op.Photo = p.Get<byte[]>("@Photo");
}
catch {}
}
UPDATE:
I found that I have to supply the 'value' parameter in the DynamicParameters constructor. This avoids the exception I was getting. I am unable to understand why I need to supply a value, since the parameter is an output and the value I supply doesn't get used. Here is the revised code:
DynamicParameters p = new DynamicParameters();
MemoryStream b = new MemoryStream();
p.Add("@OpID", id, DbType.Int32, ParameterDirection.Input);
p.Add("@StageID", Properties.Settings.Default.StageID, DbType.Int32, ParameterDirection.Input);
p.Add("@Photo", b, DbType.Binary, direction: ParameterDirection.Output);
try
{
connection.Execute(sql, p, commandType: CommandType.StoredProcedure);
op.Photo = p.Get<byte[]>("@Photo");
}
catch {}
This results in the retrieval of a byte array which contains the expected image data.
You could try:
p.Add("@Photo", dbType: DbType.Binary, direction: ParameterDirection.Output, size: -1);
It seems to work for me locally, and that is how varchar(max)
and nvarchar(max)
are mapped (except as DbType.AnsiString
and DbType.String
, respectively), so it would be consistent.