将流上传到数据库 [英] Uploading Stream to Database

问题描述

我有一个 FileForUploading 类，应将其上传到数据库。

I have a FileForUploading class which should be uploaded to a database.

public class FileForUploading
{
    public FileForUploading(string filename, Stream stream)
    {
        this.Filename = filename;
        this.Stream = stream;
    }

    public string Filename { get; private set; }

    public Stream Stream { get; private set; }
}

我正在使用实体框架将其转换为 FileForUploadingEntity
这是一个非常简单的类，但是只包含 Filename 属性。我不想将 Stream 存储在内存中，而是将其直接上传到数据库中。

I am using the Entity Framework to convert it to a FileForUploadingEntity which is a very simple class that however only contains the Filename property. I don't want to store the Stream in memory but rather upload it directly to the database.

会发生什么是将 Stream 直接流到数据库的最佳方法？

What would be the best way to 'stream' the Stream directly to the database?

到目前为止，我已经提出了此

So far I have come up with this

private void UploadStream(string name, Stream stream)
    {
        var sqlQuery = @"UPDATE dbo.FilesForUpload SET Content =@content WHERE Name=@name;";

        var nameParameter = new SqlParameter()
        {
            ParameterName = "@name",
            Value = name
        };

        var contentParameter = new SqlParameter()
        {
            ParameterName = "@content",
            Value = ConvertStream(stream),
            SqlDbType = SqlDbType.Binary
        };

        // the database context used throughout the application.
        this.context.Database.ExecuteSqlCommand(sqlQuery, contentParameter, nameParameter);
    }

这是我的 ConvertStream 将流转换为 byte [] 。 （它以 varbinary（MAX）的形式存储在数据库中。

And here is my ConvertStream which converts the Stream to a byte[]. (It is stored as a varbinary(MAX) in the database.

private static byte[] ConvertStream(Stream stream)
    {
        using (var memoryStream = new MemoryStream())
        {
            stream.CopyTo(memoryStream);
            return memoryStream.ToArray();
        }
    }

以上解决方案足够好吗？如果 Stream 很大，效果会好吗？

Is the above solution good enough? Will it perform well if the Stream is large?

推荐答案

我不想将Stream存储在内存中，而是直接将其上传到数据库中。

I don't want to store the Stream in memory but rather upload it directly to the database.

使用上述解决方案，您建议您仍然将流的内容保留在应用程序的内存中，而您最初提到的是您要避免的事情。

With the above solution you proposed you still have the content of the stream in memory in your application which you mentioned initially is something you were trying to avoid.

您最好的选择是要绕过EF并使用异步功能上传流，以下示例摘自 MSDN文章SqlClient流支持。

Your best bet is to go around EF and use the async function to upload the stream. The following example is taken from MSDN article SqlClient Streaming Support.

// Application transferring a large BLOB to SQL Server in .Net 4.5
private static async Task StreamBLOBToServer() {

 using (SqlConnection conn = new SqlConnection(connectionString)) {
    await conn.OpenAsync();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO [BinaryStreams] (bindata) VALUES (@bindata)", conn)) {
       using (FileStream file = File.Open("binarydata.bin", FileMode.Open)) {

          // Add a parameter which uses the FileStream we just opened
          // Size is set to -1 to indicate "MAX"
          cmd.Parameters.Add("@bindata", SqlDbType.Binary, -1).Value = file;

          // Send the data to the server asynchronously
          await cmd.ExecuteNonQueryAsync();
       }
    }
 }
}

您可以将此示例转换为以下示例，以使其适合您。请注意，您应该在方法上更改签名以使其异步，以便您可以利用在长时间数据库更新期间不会阻塞线程的优势。

You could convert this sample to the following to make it work for you. Note that you should change the signature on your method to make it async so you can take advantage of not having a thread blocked during a long lasting database update.

// change your signature to async so the thread can be released during the database update/insert act
private async Task UploadStreamAsync(string name, Stream stream) {

    var conn = this.context.Database.Connection; // SqlConnection from your DbContext
    if(conn.State != ConnectionState.Open)
        await conn.OpenAsync();
    using (SqlCommand cmd = new SqlCommand("UPDATE dbo.FilesForUpload SET Content =@content WHERE Name=@name;", conn)) {
          cmd.Parameters.Add(new SqlParameter(){ParameterName = "@name",Value = name});
          // Size is set to -1 to indicate "MAX"
          cmd.Parameters.Add("@content", SqlDbType.Binary, -1).Value = stream;
          // Send the data to the server asynchronously
          await cmd.ExecuteNonQueryAsync();
    }
}

---

还有一张便条。如果要保存大型的非结构化数据集（例如，要上传的流），最好不要将其保存在数据库中。原因有很多，但最重要的是，关系数据库并不是真正考虑到这一点而设计的，它处理数据很麻烦，并且它们可以快速地占用数据库空间，从而使其他操作（例如备份，还原等）更加困难。 ）。

---

One more note. If you want to save large unstructured data sets (ie. the Streams you are getting uploaded) then it might be a better idea to not save them in the database. There are numerous reasons why but foremost is that relational database were not really designed with this in mind, its cumbersome to work with the data, and they can chew up database space real fast making other operations more difficult (ie. backups, restores, etc).

还有另一种方法，它仍然可以原生地将指针保存在记录中，但实际的非结构化数据驻留在磁盘上。您可以使用 Sql Server FileStream >。在ADO.NET中，您将使用 SqlFileStream 。这是有关如何配置Sql Server和数据库以允许Sql File Streams的很好的演练。 Vb.net上还有一些有关如何使用 SqlFileStream 类的示例。

There is an alternative that still natively allows you to save a pointer in the record but have the actual unstructured data reside on disk. You can do this using the Sql Server FileStream. In ADO.NET you would be working with SqlFileStream. Here is a good walk through on how to configure your Sql Server and database to allow for Sql File Streams. It also has some Vb.net examples on how to use the SqlFileStream class.

SQL Server FileStream简介

我确实假设您使用Microsoft Sql Server作为数据存储库。如果此假设不正确，请更新您的问题，并为您要连接的正确数据库服务添加标签。

这篇关于将流上传到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！