How To Return Filestreamresult With Sqldatareader

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

Solution 1:

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.

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.

Solution 2:

This is the working code, which is dramatically faster than without the streaming:

[HttpGet("download")]
publicasync Task<FileStreamResult> DownloadFileAsync(int id)
{

    var connectionString = _configuration.GetConnectionString("DefaultConnection");

    ApplicationUser user = await _userManager.GetUserAsync(HttpContext.User);

    var fileInfo = awaitthis._attachmentRepository.GetAttachmentInfoByIdAsync(id);

    SqlConnection connection = new SqlConnection(connectionString);
        
    await connection.OpenAsync();
    SqlCommand command = new SqlCommand("SELECT [Content] FROM [Attachments] WHERE [AttachmentId]=@id", connection);
    command.Parameters.AddWithValue("id", fileInfo.Id);

    // The reader needs to be executed with the SequentialAccess behavior to enable network streaming// Otherwise ReadAsync will buffer the entire BLOB into memory which can cause scalability issues or even OutOfMemoryExceptions
    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, fileInfo.ContentType)
            {
                FileDownloadName = fileInfo.FileName
            };
            return result;  
        }
    }
    returnnull;    
}