Unable to insert file stream into SQL filetable using Dapper.NET

Question

I am using Dapper.NET as ORM layer in my project. I am trying to write WebApis for file upload and download. However I am not able to get it working. I have already searched enough to look for help but I couldn't find any.

If I was simply using ADO.NET, I could have used SqlParameter of type VarBinary for the file stream. But Dapper.NET query parameters is just dynamic object. So following code fails to insert the record into filetable.

var fileStream = await Request.Content.ReadAsStreamAsync();

var streamId = Guid.NewGuid();
var fileName = streamId.ToString();

var sql = @"Insert into Attachments(stream_id, file_stream, name)
            Values(@streamId, @fileStream, @fileName)";

using (var db = new SqlConnection(AppConfig.ConnectionString))
{
    await db.ExecuteAsync(sql, new { streamId, fileStream, fileName);
}

fileStream.Close();

Exception thrown: 'System.NotSupportedException' in mscorlib.dll

Additional information: The member fileStream of type System.IO.Stream cannot be used as a parameter value

Has anyone done this or be aware of any dapper extension plugin I could use?

Answer 1

You need to use the DynamicParameters class and specify the parameter's data type. For the example I created a table TEST with a column called Stream VARBINARY(MAX)

using (var connection = new SqlConnection(Properties.Settings.Default.connectionString))
{
    connection.Open();
    using (var fs = File.Open(Properties.Settings.Default.filePath, FileMode.Open))
    {
        var sql = "INSERT INTO TEST (Stream) VALUES (@fs)";

        var dParams = new DynamicParameters();
        dParams.Add("@fs", fs, DbType.Binary);

        connection.Execute(sql, dParams);
    }
}