c# - ASP.NET MVC 中的 SQL Server 流文件输出和处理连接

Question

我正在尝试将存储在 sql server 中的内容作为 varbinary(max) 流式传输到客户端。我能够使它工作但是连接将保持打开状态直到垃圾收集。如果我在返回结果之前处理连接、阅读器或 Steam ，它会导致对象处理错误。

我希望避免将数据复制到内存中(因为它可能很大)，同时也在完成后正确处理对象。实现这两个目标的最佳方式是什么。

如果相关，我正在使用 .NET Core 2.0。

更新:这不是与其他人 (How do I dispose my filestream when implementing a file download in ASP.NET?) 的重复，因为我不是在问如何处理流，而是在问如何处理相关的连接对象。我的问题更多是关于确保处理连接对象的正确方法

下面的代码成功返回了结果但是留下了一个未处理的连接:

public async Task<IActionResult> DownloadFile(Guid FileId)
{
    var connection = new SqlConnection(DatabaseService.ConnectionString);

    await connection.OpenAsync();
    var command = connection.CreateCommand();

    command.CommandText = "select FileName, FileContent from Files where FileId=@FileId";
    command.CommandType = System.Data.CommandType.Text;
    command.Parameters.AddWithValue("@FileId", FileId);

    var reader = await command.ExecuteReaderAsync(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.SingleRow);

    if (!await reader.ReadAsync())
        return NotFound();
    var attachmentName = Convert.ToString(reader[0]);

    var stream = reader.GetStream(1);

    var response = File(stream, "application/octet-stream", attachmentName);
    return response;
}

下面的代码处理了对象，但无法流式传输内容，因为它先被处理

public async Task<IActionResult> DownloadFile(Guid FileId)
{
    using (var connection = new SqlConnection(DatabaseService.ConnectionString))
    {

        await connection.OpenAsync();
        using (var command = connection.CreateCommand())
        {

            command.CommandText = "select FileName, FileContent from Files where FileId=@FileId";
            command.CommandType = System.Data.CommandType.Text;
            command.Parameters.AddWithValue("@FileId", FileId);

            using (var reader = await command.ExecuteReaderAsync(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.SingleRow))
            {

                if (!await reader.ReadAsync())
                    return NotFound();
                var attachmentName = Convert.ToString(reader[0]);

                using (var stream = reader.GetStream(1))
                {

                    var response = File(stream, "application/octet-stream", attachmentName);
                    return response;
                }
            }
        }
    }
}

Answer 1

David Browne 的回答给了我信息。我需要使用 HttpContext.Response.RegisterForDispose(); 注册一次性组件。这确保它在请求完成后得到处理。

下面是更新后的代码

public async Task<IActionResult> DownloadFile(Guid FileId)
{
    var connection = new SqlConnection(DatabaseService.ConnectionString);
    HttpContext.Response.RegisterForDispose(connection);

    await connection.OpenAsync();
    var command = connection.CreateCommand();
    HttpContext.Response.RegisterForDispose(command);

    command.CommandText = "select FileName, FileContent from Files where FileId=@FileId";
    command.CommandType = System.Data.CommandType.Text;
    command.Parameters.AddWithValue("@FileId", FileId);

    var reader = await command.ExecuteReaderAsync(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.SingleRow);
    HttpContext.Response.RegisterForDispose(reader);

    if (!await reader.ReadAsync())
        return NotFound();
    var attachmentName = Convert.ToString(reader[0]);

    var stream = reader.GetStream(1);
    HttpContext.Response.RegisterForDispose(stream);

    var response = File(stream, "application/octet-stream", attachmentName);
    return response;
}

如果其他人有同样的问题，我添加这个答案是为了清楚