通过WebApi将大文件(1-3GB)上传到SQL [英] Large File (1-3GB) upload to SQL via WebApi

问题描述

我有一个JS/HTML5前端，该前端调用C#WebAPI上载需要加密然后存储在SQL DB中的大文件(1-3GB).

I have a JS/HTML5 front end that calls a C# WebAPI to upload a large file (1-3GB) that needs to be encrypted and then stored in a SQL DB.

我的限制是我不能存储未加密的文件，也不能存储在SQL外部.我也不能使用SQL FileStream.

My restrictions are that I can't store file unecnrypted and it can't be stored outside of SQL. I also can't use SQL FileStream.

对于WebAPI，我禁用了BufferStream(通过重写WebHostBufferPolicySelector)来处理任何内存异常.这使我可以直接将文件上传到磁盘，而不会显着增加内存占用.

For the WebAPI, I disabled the BufferStream (via overriding WebHostBufferPolicySelector) to take care of any memory exceptions. This allows me to upload files directly to disk without increasing the memory footprint significantly.

我想将构成文件的一系列加密块按顺序存储在SQL表中.然后，我可以拉出这些块，解密它们，并在请求时将它们流回给用户.

I would like to store the series of encrypted chunks that make up the file in order in rows in a SQL table. Then I could pull the chunks out, decrypt them, and stream them back to the user when requested.

我不知道如何获取多数据部分消息，加密数据块并将其写入数据库中的行.有人可以为我指出正确的方向吗?

I do not know how I can take a multi-data part message, encrypt the chunks, and write it to rows in the database. Can someone point me in the right direction with how I can do this?

推荐答案

确定.因此，您已经解决了WebAPI内存不足的问题.但是接下来要担心的是，您必须将数据传递给SQL Server.您可以通过任何.NET流加密器通过管道传输请求流，但之后必须将其发送到某个地方.

OK. So you've solved the issue of WebAPI not running out of RAM. But the next concern is that you've got to pass the data to SQL Server. You can pipe the request stream through any of the .NET stream encryptors, but then it's gotta go somewhere after that.

我建议您执行以下操作:

I suggest you do something like:

请求流->加密器->自定义流

Request stream -> encryptor -> custom stream

自定义流将负责将数据发送到块中的sql服务器.您可以微调块的大小，但这实际上是为了避免发送1GB或更大参数大小的sql命令.在给定固定缓冲区大小的情况下，您的自定义流sql命令会将上传文件分成多个字节.当缓冲区超过一定数量时，您将一行写入SQL Server，刷新缓冲区，然后等待更多内容从加密器流中传入.写入的行将与某种ID绑定在一起，当然还有一个序列号，该序列号将告诉您重新组装它们时要使用的顺序.

The custom stream will be responsible for sending data to sql server in chunks. You can fine tune the size of the chunks but it's essentially so that you don't try to send a sql command with a 1GB or more argument size. Your custom stream sql command will break the upload into chunks of bytes, given a fixed buffer size. When the buffer gets over a certain amount, you write a row to SQL Server, flush the buffer, and wait for more to come in from the encryptor stream. The rows that get written will be tied together with some sort of ID, and of course, a sequence number that will tell you the order to use when re-assembling them.

当您要发送回数据时，只需再次执行一次，但是相反.

When you want to send the data back, you just do this again, but in reverse.

这篇关于通过WebApi将大文件(1-3GB)上传到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！