Issue with saving files into SQL as varbinary(max)

Question

So, I have a web application that saves some attachments into SQL database as VarBinary(max), everything working smoothly until I realized that when I download the attachment from the database, the file is very big comparing to the original size, so a file with 400KB, the downloaded one would be 5MB! what I'm doing wrong?

The upload code is:

Dim img As FileUpload = CType(FileUpload1, FileUpload)
Dim imgByte As Byte() = Nothing
If img.HasFile AndAlso Not img.PostedFile Is Nothing Then
    Dim File As HttpPostedFile = FileUpload1.PostedFile
    imgByte = New Byte(File.ContentLength - 1) {}
    File.InputStream.Read(imgByte, 0, File.ContentLength)
    cmd.Parameters.AddWithValue("@filetype", Path.GetExtension(FileUpload1.FileName).ToLower)
    cmd.Parameters.AddWithValue("@attachment", imgByte)
End If

The download code:

Dim imagem As Byte() = CType((dr("attachment")), Byte())
Response.Clear()
Response.AddHeader("Cache-Control", "no-cache, must-revalidate, post-check=0, pre-check=0")
Response.AddHeader("Pragma", "no-cache")
Response.AddHeader("Content-Description", "File Download")
Response.AddHeader("Content-Type", "application/force-download")
Response.AddHeader("Content-Transfer-Encoding", "binary\n")
Dim fileName As String = "attachment; filename=" & "DOC_REFNO_" & "_" & regref_txt.Text.Replace("/", "_") & dr("filetype")
Response.AddHeader("content-disposition", fileName)
Response.BinaryWrite(imagem)

Thank you all.

the ratio would suggest that you get a base64 encoded version on download. but I don't see where and why that would be. measure the size of a stored file with DATALENGTH in the sql server table, to determine if the bloat occurs on store or on retrieve. what does the `\n` mean after the transfer encoding? — Cee McSharpface, Aug 23 '21 at 08:14
to be honest i don't now, I've copied the code from the internet. — Osama Gadour, Aug 23 '21 at 08:18
Don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/) — SMor, Aug 23 '21 at 11:07

score 0 · Answer 1 · answered Aug 23 '21 at 10:54

so I found out that the download code is not correct, I changed it to the following code and worked perfectly.

 Dim imagem As Byte() = CType((dr("attachment")), Byte())
            Response.Buffer = True
            Response.Charset = ""
            Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Response.ContentType = ContentType
            Dim fileName As String = "attachment; filename=" & "DOC_REFNO_" & "_" & regref_txt.Text.Replace("/", "_") & dr("filetype")

            Response.AddHeader("content-disposition", fileName)
            Response.BinaryWrite(imagem)
            Response.Flush()
            Response.End()

Though you fixed your own problem, may I suggest you have a look at MS SQL's [FileTables](https://learn.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server?view=sql-server-ver15) for storing files instead of a VarBinary(max) column? It's a useful table type introduced in MS SQL 2012. — Hel O'Ween, Aug 23 '21 at 15:22

Issue with saving files into SQL as varbinary(max)

1 Answers1