如何使用SqlDataReader返回FileStreamResult [英] How to return FileStreamResult with SqlDataReader

问题描述

我有一个ASP.NET Core项目，该项目可以下载存储在SQL Server中的大文件.它适用于小文件，但大文件在下载之前被读入内存时通常会超时.

I have an ASP.NET Core project that downloads large files which are stored in SQL Server. It works fine for small files, but large files often time out as they are read into memory before getting downloaded.

所以我正在努力改善这一点.

So I am working to improve that.

基于 SQL Client流支持示例我已将代码更新为以下内容:

Based on SQL Client streaming support examples I have updated the code to the following:

public async Task<FileStreamResult> DownloadFileAsync(int id)
{
     ApplicationUser user = await _userManager.GetUserAsync(HttpContext.User);
     var file = await this._attachmentRepository.GetFileAsync(id);

     using (SqlConnection connection = new SqlConnection(this.ConnectionString))
     {
         await connection.OpenAsync();

         using (SqlCommand command = new SqlCommand("SELECT [Content] FROM [Attachments] WHERE [AttachmentId] = @id", connection))
        {
             command.Parameters.AddWithValue("id", file.AttachmentId);
             SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess);

             if (await reader.ReadAsync())
             {
                 if (!(await reader.IsDBNullAsync(0)))
                 {
                     Stream stream = reader.GetStream(0);
                     var result = new FileStreamResult(stream, file.ContentType)
                     {
                         FileDownloadName = file.FileName
                     };

                     return result;
                 }
             }
         }
     }

     return null;   
 }

但是当我测试时，它会抛出此异常:

But when I test, it throws this exception:

无法访问已处置的对象.对象名称:"SqlSequentialStream"

Cannot access a disposed object. Object name: 'SqlSequentialStream'

是否有解决此异常的方法?

Is there a way to fix this exception?

推荐答案

您的 using 语句在您执行 return 时都会触发，从而处理了连接和命令，但这的全部目的是让流复制在完成功能后 后在后台发生.

Your using statements are all triggering when you do your return, thus disposing your connection and command, but the whole point of this is to leave the stream copy to happen in the background after your function is done.

对于此模式，您将必须删除 using 调用，并在完成流复制后触发垃圾回收. FileStreamResult 至少应在您提供的流上调用 Dispose ，这应取消命令和连接的根目录，以便稍后完成和关闭.

For this pattern you're going to have to remove the using calls and let garbage collection trigger when the stream copy is done. FileStreamResult should at the very least call Dispose on the stream you give it, which should un-root the command and connection to be later finalized and closed.

这篇关于如何使用SqlDataReader返回FileStreamResult的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！