SQL Server stream file output in ASP.NET MVC and disposing connection

Question

I am trying to stream contents stored in sql server as varbinary(max) to the client. I am able to make it work however the connection will stay open until garbage collection. If I dispose the connection, reader or steam prior to returning the results it causes an object disposed error.

I am looking to avoid copying the data into memory (as it can be large) while also properly disposing of the object when done. What would be the best way to achieve both of these goals.

I am using .NET Core 2.0 if that is relevant.

Update: This is not a duplicate from others (How do I dispose my filestream when implementing a file download in ASP.NET?) as I am not asking how to dispose of the stream but rather how to dispose of the related connection object. My question is more about the proper way of ensure the connection object is disposed

The code below successfully returns results but leaves an non-disposed connection:

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

This code below disposes of the object but fails to stream the content because it is disposed first

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

This is not a duplicate from others as I am not asking how to dispose of the stream but rather how to dispose of the related connection object. My question is more about the proper way of ensure the connection object is disposed — webwake, Aug 14 '17 at 13:31
@musefan unfortunately your new example (https://stackoverflow.com/questions/37956254/asp-net-core-mvc-get-file-from-database-and-render-as-image) does not address the need to stream and the accepted answer loads the entire content into a memory stream before sending the response. — webwake, Aug 14 '17 at 15:05
You don't have any reliable options. Connections are limited/precious and you need to dispose of them as soon as you can. Reading all the data then closing the connection and send the data is by far the best way. There are other ways like registering and object to be disposed, but you're just leaving the connection open, and there is *no* advantage to that. — Erik Philips, Aug 14 '17 at 17:07
Erik, it looks like `HttpContext.Response.RegisterForDispose()` works so I can stream the results and not have to worry about the connection living on after complete — webwake, Aug 14 '17 at 20:05

score 5 · Accepted Answer · answered Aug 14 '17 at 20:01

David Browne's answer gave me the information. I needed to register the disposable components using HttpContext.Response.RegisterForDispose();. This ensures it gets disposed after the request is complete.

Below is the updated code

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

I added this answer for clarity if others have the same issue

You should accept your own answer and not the other one, this one is much better, the other one has little effort and probably should have just been a comment — musefan, Aug 15 '17 at 07:47

score 1 · Answer 2 · answered Aug 14 '17 at 15:44

The SqlConnection just needs to be disposed after the response has been consumed by the client. See:

https://andrewlock.net/four-ways-to-dispose-idisposables-in-asp-net-core/

https://www.strathweb.com/2017/03/disposing-resources-at-the-end-of-asp-net-core-request/

SQL Server stream file output in ASP.NET MVC and disposing connection

2 Answers2

Linked