Get blob from SQL and write to browser for download

Question

ASP / .NET 3.5 & SQL Server 2005

I'm trying to download a blobbed PDF from our SQL database, then without physically writing the file anywhere, output the file and download to user with the "Save / Open".

The code processes through without throwing an Exception, but no download occurs.

To be honest I'm not certain how close this is to "working"... I've tried piecing together several different samples from various posts, being that nothing seems to match my particular situation.

Response.ClearContent()
Response.Clear()
Response.ContentType = "application/pdf"
Response.AddHeader("Content-Disposition", "attachment; filename=MY_DOWNLOAD.pdf")

Dim bufferSize As Integer = 100
Dim outByte(bufferSize - 1) As Byte

Dim strSql As String = "SELECT BlobData, BlobData FROM Attachment WHERE RecordID = " & CInt(Request.QueryString("pid").ToString)
Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlServer").ConnectionString)
    connection.Open()

    Dim scSql As SqlCommand = New SqlCommand(strSql, connection)

    Dim sdrSql As SqlDataReader = scSql.ExecuteReader(CommandBehavior.SequentialAccess)

    Do While sdrSql.Read

        Dim startIndex As Long = 0

        Dim retVal As Long = sdrSql.GetBytes(1, startIndex, outByte, 0, bufferSize)

        Do While retVal = bufferSize

             Response.BinaryWrite(outByte)
             Response.Flush()

             startIndex += bufferSize
             retVal = sdrSql.GetBytes(1, startIndex, outByte, 0, bufferSize)
        Loop

        Response.BinaryWrite(outByte)
        Response.Flush()

        Response.End()

    Loop

    sdrSql.Close()

End Using

competent_tech · Accepted Answer · 2014-10-06 19:13:54Z

2

I would strongly recommend saving the files to a temporary location on disk, then letting Response.TransmitFile send the file for you.

The problem with the approach that you are using is that you will have two copies of each file in memory. If your files are 100MB each and you have 100 simultaneous users, this will result in 20GB of memory being used by your app.

Also, your current code will send at most one file to the user since you end the response within the SQL loop.

Finally, Response.End is no longer recommended since it causes problems in non-IE browsers. Instead, use Context.ApplicationInstance.CompleteRequest().

As pointed out in the comments, you should wrap the creation of the temp file and TransmitFile operation in a try/finally statement and delete the temp file in the finally portion. This will handle various exceptions (such as the end user getting disconnected part way through the transmission process) and ensure that you don't have a bunch of temp files stranded on your disk.

edited Oct 6, 2014 at 19:13

answered Oct 6, 2014 at 18:46

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrew Morton Over a year ago

Not a question, just pointing something out: do you have anything to add to that for cleaning up old files from the disk? I've had success with using TransmitFile followed by code to delete stale files, and no CompleteRequest.

competent_tech Over a year ago

@AndrewMorton: thanks for pointing that out: in cases like this, I just wrap everything in a try/finally and nuke the temp file in the finally statement. This helps in various scenarios such as the recipient shutting down their browser or losing their connection part way through the transmission process, which will result in an exception being thrown. I will add this info to the answer.

Andrew Morton Over a year ago

Or, to give the OP more ideas, something more sophisticated could be constructed, like giving the temporary file a unique name based on something in the database, check if the file already exists on disk before fetching it from the DB, and deleting files older than, say, 24 hours (to be tuned according to frequency/size of fetches, available disk space and chances of the same file being needed within some period of time).

Saturn K · Accepted Answer · 2014-10-06 19:06:56Z

2

While I agree with competent_tech's answer above, if you still feel like you don't want to write the file to disk and want to fetch it, then convert it to byte array and stream it as a "Save As", then you could try the following (sorry it's C#, but you can convert it to VB.NET pretty easily):

byte[] byteArray = GetBytesFromSource();

Response.AddHeader("Content-Disposition", "attachment; filename=Test.pdf");
Response.AddHeader("Content-Length", byteArray.Length.ToString(CultureInfo.CurrentCulture));
Response.ContentType = "application/octet-stream";
Response.OutputStream.Write(byteArray, 0, byteArray.Length);
Response.Flush();

I just tested this and it works like a charm :)

answered Oct 6, 2014 at 19:06

Saturn K

2,8154 gold badges27 silver badges38 bronze badges

Collectives™ on Stack Overflow

Get blob from SQL and write to browser for download

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related