title: Downloading large files, saved in Database
body: <p>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:</p>
  
  <blockquote>
    <p>Overflow or underflow in the arithmetic operation</p>
  </blockquote>
  
  <p>I assume it runs out of memory, as I load the binary into a byte[].</p>
  
  <p>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?</p>
  
  <p>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.</p>
  
  <p>This is my function, to deliver the file:</p>
  
  <pre><code>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("&lt;p&gt;Need a valid id&lt;/p&gt;");
      }
  }
  catch (Exception ex)
  {
      context.Response.ContentType = "text/html";
      context.Response.Write("&lt;p&gt;" + ex.ToString() + "&lt;/p&gt;");
  }
  </code></pre>
  
  <p><strong>Update:</strong>
  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:</p>
  
  <pre><code>byte[] buffer = (byte[])dtbl.Rows[0]["FileContent"];
  context.Response.OutputStream.Write(buffer, 0, buffer.Length);
  </code></pre>
  
  <p>New download function:</p>
  
  <pre><code>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 ID=@ID";
          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)) &gt; 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);
          }
      }
  </code></pre>