Downloading large files, saved in Database

We have a lot of files, saved as binary in our SQL Server database.
I have made an .ashx file, that delivers these files, to the users.
Unfortunately, when the files become rather large, it will fail, with the following error:

Overflow or underflow in the arithmetic operation

I assume it runs out of memory, as I load the binary into a byte[].

So, my question is, how can I make this functionality, read in chunks (maybe?), when it is from a database table? It also seems like Response.TransmitFile() is a good option, but again, how would this work with a database?

The DB.GetReposFile(), in the code beneath, gets the file from the database. There are various fields, for the entry:
Filename, ContentType, datestamps and the FileContent as varbinary.

This is my function, to deliver the file:

context.Response.Clear();
try
{
    if (!String.IsNullOrEmpty(context.Request.QueryString["id"]))
    {
        int id = Int32.Parse(context.Request.QueryString["id"]);
        DataTable dtbl = DB.GetReposFile(id);
        string FileName = dtbl.Rows[0]["FileName"].ToString();
        string Extension = FileName.Substring(FileName.LastIndexOf('.')).ToLower();
        context.Response.ContentType = ReturnExtension(Extension);
        context.Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName);

        byte[] buffer = (byte[])dtbl.Rows[0]["FileContent"];
        context.Response.OutputStream.Write(buffer, 0, buffer.Length);
    }
    else
    {
        context.Response.ContentType = "text/html";
        context.Response.Write("<p>Need a valid id</p>");
    }
}
catch (Exception ex)
{
    context.Response.ContentType = "text/html";
    context.Response.Write("<p>" + ex.ToString() + "</p>");
}

Update:
The function I ended up with, is the one listed below.
DB.GetReposFileSize() simply gets the content Datalength, as Tim mentions.
I call this function, in the original code, instead of these two lines:

byte[] buffer = (byte[])dtbl.Rows[0]["FileContent"];
context.Response.OutputStream.Write(buffer, 0, buffer.Length);

New download function:

private void GetFileInChunks(HttpContext context, int ID)
    {
        //string path = @"c:somefile.txt";
        //FileInfo file = new FileInfo(path);
        int len = DB.GetReposFileSize(ID);
        context.Response.AppendHeader("content-length", len.ToString());
        context.Response.Buffer = false;


        //Stream outStream = (Stream)context.Response.OutputStream;

        SqlConnection conn = null;
        string strSQL = "select FileContent from LM_FileUploads where <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="84cdc0b9c4cdc0">[email protected]</a>";
        try
        {
            DB.OpenDB(ref conn, DB.DatabaseConnection.PDM);
            SqlCommand cmd = new SqlCommand(strSQL, conn);
            cmd.Parameters.AddWithValue("@ID", ID);

            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
            reader.Read();
            byte[] buffer = new byte[1024];
            int bytes;
            long offset = 0;

            while ((bytes = (int)reader.GetBytes(0, offset, buffer, 0, buffer.Length)) > 0)
            {
                // TODO: do something with `bytes` bytes from `buffer`
                context.Response.OutputStream.Write(buffer, 0, buffer.Length);

                offset += bytes;
            }
        }

        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            DB.CloseDB(ref conn);
        }
    }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can use DATALENGTH to get the size of the VARBINARY and stream it for instance with a SqldataReader and it’s Read-or ReadBytes-Method.

Have a look at this answer to see an implementation: Best way to stream files in ASP.NET

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0