Downloading a 50 MB file from SQL Server in ASP.NET stops in middle for a while

When I'm trying to download a 50 MB file from a database (it is not an issue with a smaller file size), it stops in the middle sometimes and resumes again after a long time. Am I mssing something?

The code,

    SqlConnection con = new SqlConnection(ConnectionString);
    SqlCommand cmd = new SqlCommand("DownloadFile", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandTimeout = 60;

    cmd.Parameters.AddWithValue("@Id", Id);

    try
    {
        conPortal.Open();
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        // File name
        string fileName = reader.GetString(0);

        // Total bytes to read
        int dataToRead = reader.GetInt32(1);

        Context.Server.ScriptTimeout = 600;
        Context.Response.Buffer = true;
        Context.Response.Clear();

        Context.R开发者_如何学Pythonesponse.ContentType = "application/octet-stream";
        Context.Response.AddHeader("Content-Disposition", 
                                   "attachment; filename=\"" + fileName + "\";");

        Context.Response.AddHeader("Content-Length", dataToRead.ToString());

        int ChunkSize = 262144;

        // Buffer to read 10K bytes in chunk
        byte[] buffer = new Byte[ChunkSize];
        long offset = 0;
        long length;

        // Read the bytes.
        while (dataToRead > 0)
        {
            // Verify that the client is connected.
            if (Context.Response.IsClientConnected)
            {
                // Read the data in buffer
                length = reader.GetBytes(2, offset, buffer, 0, ChunkSize);
                offset += ChunkSize;

                // Write the data to the current output stream.
                Context.Response.OutputStream.Write(buffer, 0, (int) length);

                // Flush the data to the HTML output.
                Context.Response.Flush();

                buffer = new Byte[ChunkSize];
                dataToRead = dataToRead - (int) length;
            }
            else
            {
                //prevent infinite loop if user disconnects
                dataToRead = -1;
            }
        }
    }
    finally
    {
        cmd.Dispose();
    }

---

int ChunkSize = 262144;

There you go, that's 25 MB - right in the middle of a 50 MB download. Try modifying this and see what happens. Any reason you set it to that particular value?

---

That's not how you stream content from a SQL Server query result. You must specify CommandBehavior.SequentialAccess:

Provides a way for the DataReader to handle rows that contain columns with large binary values. Rather than loading the entire row, SequentialAccess enables the DataReader to load data as a stream. You can then use the GetBytes or GetChars method to specify a byte location to start the read operation, and a limited buffer size for the data being returned.

And, as others have pointed out, 25MB is not a reasonable buffer chunk size. Try something like 4K (typical TDS packet size) or 16k (typical SSL frame size if connection is encrypted).