无法使用 Dapper.NET 将文件流插入 SQL 文件表

问题描述

我在我的项目中使用 Dapper.NET 作为 ORM 层.我正在尝试编写用于文件上传和下载的 WebApis.但是我无法让它工作.我已经搜索了足够多的帮助来寻求帮助，但我找不到任何帮助.

如果我只是使用 ADO.NET，我可以使用 VarBinary 类型的 SqlParameter 作为文件流.但是 Dapper.NET 查询参数只是动态对象.所以下面的代码无法将记录插入到文件表中.

var fileStream = await Request.Content.ReadAsStreamAsync();var streamId = Guid.NewGuid();var fileName = streamId.ToString();var sql = @"插入附件(stream_id, file_stream, name)值(@streamId，@fileStream，@fileName)"；使用 (var db = new SqlConnection(AppConfig.ConnectionString)){等待 db.ExecuteAsync(sql, new { streamId, fileStream, fileName);}fileStream.Close();

抛出异常:mscorlib.dll 中的System.NotSupportedException"

附加信息:System.IO.Stream 类型的成员 fileStream不能作为参数值

有没有人做过这个或者知道我可以使用的任何 dapper 扩展插件?

解决方案

您需要使用 DynamicParameters 类并指定参数的数据类型.例如，我创建了一个表 TEST，其中有一列名为 Stream VARBINARY(MAX)

using (var connection = new SqlConnection(Properties.Settings.Default.connectionString)){连接.打开()；使用 (var fs = File.Open(Properties.Settings.Default.filePath, FileMode.Open)){var sql =插入测试(流)值(@fs)"；var dParams = new DynamicParameters();dParams.Add("@fs", fs, DbType.Binary);connection.Execute(sql, dParams);}}

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

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

解决方案

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

这篇关于无法使用 Dapper.NET 将文件流插入 SQL 文件表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！