Request is not finished when using SqlDataReader in FileStreamResult using ASP.NET MVC

Question

For performance considerations I am using SqlConnection and SqlReaderStream for returning a byte[] stream from a SQL Server database:

private static SqlConnection GetConnection()
{
    var sqlConnectionStringBuilder =
        new SqlConnectionStringBuilder(
            ConfigurationManager.ConnectionStrings["StudentsSystemEntities"].ConnectionString)
            {
                Pooling = true
            };
    var connection = new SqlConnection(sqlConnectionStringBuilder.ConnectionString);
    connection.Open();
    return connection;
}

public FileDownloadModel GetFileById(Guid fileId)
{
    var connection = GetConnection();
    var command = new SqlCommand(
        @"SELECT [FileSize], [FileExtension], [Content] FROM [dbo].[Files] WHERE [FileId] = @fileId;",
        connection);
    var paramFilename = new SqlParameter(@"fileId", SqlDbType.UniqueIdentifier) { Value = fileId };
    command.Parameters.Add(paramFilename);

    var reader = command.ExecuteReader(
        CommandBehavior.SequentialAccess | CommandBehavior.SingleResult
        | CommandBehavior.SingleRow | CommandBehavior.CloseConnection);

    if (reader.Read() == false) return null;

    var file = new FileDownloadModel
                    {
                        FileSize = reader.GetInt32(0),
                        FileExtension = reader.GetString(1),
                        Content = new SqlReaderStream(reader, 2)
                    };
    return file;
}

I am using this GetFileById method in ASP.NET MVC action:

[HttpGet]
public ActionResult Get(string id)
{
    // Validations omitted

    var file = this.filesRepository.GetFileById(guid);

    this.Response.Cache.SetCacheability(HttpCacheability.Public);
    this.Response.Cache.SetMaxAge(TimeSpan.FromDays(365));
    this.Response.Cache.SetSlidingExpiration(true);

    this.Response.AddHeader("Content-Length", file.FileSize.ToString());
    var contentType = MimeMapping.GetMimeMapping(
        string.Format("file.{0}", file.FileExtension));
    // this.Response.BufferOutput = false;
    return new FileStreamResult(file.Content, contentType);
}

I am connecting the MVC FileStreamResult with the SqlReaderStream in the following line:

return new FileStreamResult(file.Content, contentType);

When I try to load the resource using Chrome (or Firefox...) the entire file is loaded but I am getting the following error:

CAUTION: request is not finished yet!

Response headers:

HTTP/1.1 200 OK
Cache-Control: public, max-age=31536000
Content-Length: 33429
Content-Type: image/png
Server: Microsoft-IIS/10.0
X-AspNetMvc-Version: 5.2
X-AspNet-Version: 4.0.30319
X-SourceFiles: =?UTF-8?B?QzpcR---trimmed---G5n?=
X-Powered-By: ASP.NET
Date: Tue, 28 Jul 2015 13:02:55 GMT

Additional information:

• I am not using any Chrome extensions
• The problem is only with the given Get action. All other actions are loading normally
• The FilesController (in which the Get action is) inherits directly from the Controller class
• The file is loading successfully but the browser is still waiting the server:
• The exact same problem I am having with Firefox

What are the possible causes of the problem?

Source code of the SqlReaderStream class

public class SqlReaderStream : Stream
{
    private readonly int columnIndex;

    private SqlDataReader reader;

    private long position;

    public SqlReaderStream(
        SqlDataReader reader, 
        int columnIndex)
    {
        this.reader = reader;
        this.columnIndex = columnIndex;
    }

    public override long Position
    {
        get { return this.position; }
        set { throw new NotImplementedException(); }
    }

    public override bool CanRead
    {
        get { return true; }
    }

    public override bool CanSeek
    {
        get { return false; }
    }

    public override bool CanWrite
    {
        get { return false; }
    }

    public override long Length
    {
        get { throw new NotImplementedException(); }
    }

    public override void Flush()
    {
    }

    public override int Read(byte[] buffer, int offset, int count)
    {
        var bytesRead = this.reader.GetBytes(
            this.columnIndex, this.position, buffer, offset, count);
        this.position += bytesRead;
        return (int)bytesRead;
    }

    public override long Seek(long offset, SeekOrigin origin)
    {
        throw new NotImplementedException();
    }

    public override void SetLength(long value)
    {
        throw new NotImplementedException();
    }

    public override void Write(byte[] buffer, int offset, int count)
    {
        throw new NotImplementedException();
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing && null != this.reader)
        {
            this.reader.Dispose();
            this.reader = null;
        }

        base.Dispose(disposing);
    }
}

Answer 1

I would not return SqlReaderStream in FileStreamResult, because the stream is not seekable. I guess it could be a problem.

Try to copy the Stream to MemoryStream or byte array and return it in GetFileById instead.

Furthermore you can close the connection to database if you read the SqlReaderStream locally in function.

return File(byteArray, contentType, name);