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

Ctenoid

Could you post code for SqlReaderStream? Issue might be inside this class. – Righthander

Sure :). See my edited post. – Ctenoid

This could be the problem of "Keep-Alive" which why the connection is not being terminated from server. – Varix

Does your [Content] field really contain 33429 bytes of data? Or does it have less? What value do you get when you execute: reader.GetBytes(2, 0, null, 0, 0);? – Stanza

I think there is no way that the FileStreamResult knows the stream has ended. You don't return the Length and also don't have the EndOfStream property – Variform

try this.reader.GetBytes( this.columnIndex, offset, buffer, 0, count); – Variform

Try proactively closing your SqlDataReader inside your SqlReaderStream.Read when you detect that bytesRead==0 – Beatty

Answer 1

The real problem is that in this line:

this.Response.AddHeader("Content-Length", file.FileSize.ToString());

you are giving away a wrong filesize which is larger than the real file size. This is why browsers are waiting for additional content.

To quickly check that this is indeed the issue, modify the line to the following

this.Response.AddHeader("Content-Length", (file.FileSize/10).ToString());

This will limit a download to 1/10th of the file in question - but since filesizes are probably off much less than by an order of magnitude, the files will have enough data to serve 10 percent of the asked filesize, and the issue will not be reproducible anymore.

Stimulative

This was the issue! Thank you very much. To be precise I've fixed it by not sending Content-Length header to the browser (removing this.Response.AddHeader("Content-Length", file.FileSize.ToString()); line) – Ctenoid

I don't usually leave thanks.. it just clutters up the responses, but I had to make an exception. Thank you. – Weekender

Answer 2

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

Largely

this solution is not suitable for large files, i used this method for long time, but now I need to handle large files and it doesn't work like before (sometimes nothing happens :) – Toleration

Answer 3

This problem is related to Chrome Browser,only when the first request is not completed. Try using Firefox or Safari.

See Links below for further info. http://www.codeproject.com/Questions/821716/PHP-serve-MP-Chrome-Provisional-headers-are-shown

"CAUTION: provisional headers are shown" in Chrome debugger

Chrome block requests

Vertebrate

As I've mentioned in the question: The exact same problem I am having with Firefox – Ctenoid