How to Download A file stored in SQL DB in Binary

Question

I am simply storing uploaded file into a binary field in SQL Server but I also need to allow users to download it with Asp.NET. How can I do that ?

Thanks in advance.

Answer 1

Read the data into a filestream object with the appropriate extension tacked on to it, and have the user download the resulting file.

You'll want to use the System.IO BinaryWriter object on the filestream to create the file...something like this:

FileStream fs = new FileStream("thisfile.bin", FileMode.Create);
binWriter= new BinaryWriter(fs);    

binWriter.Write(varHoldingSqlRetrievedBinaryData);

Answer 2

Add a Generic Handler (.ashx) page to your web site. The ashx code body below demonstrates how to read an arbitrary stream (in this case a PNG file from disk) and write it out in the response:

using System;
using System.Web;
using System.IO;

namespace ASHXTest
{
    public class GetLetter : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            // Get letter parameter from query string.
            string fileName = context.Request.MapPath(string.Format("{0}.png",
                context.Request.QueryString["letter"]));

            // Load file from disk/database/ether.
            FileStream stream = new FileStream(fileName, FileMode.Open,
                FileAccess.Read);
            byte[] buffer = new byte[stream.Length];
            stream.Read(buffer, 0, buffer.Length);
            stream.Close();

            // Write response headers and content.
            context.Response.ContentType = "image/png";
            context.Response.OutputStream.Write(buffer, 0, buffer.Length);
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

If desired, you can also set the Content-Disposition header as demonstrated in Heinzi's answer:

context.Response.AddHeader("Content-Disposition",
    "attachment;filename=\"letter.png\"");

Answer 3

Here's a Microsoft Knowledge Base article on this.

How to retrieve the file from your database depends on the data access technology you use; I will just assume that you have some Byte array data containing the file (e.g. by filling a DataSet and accessing the field) and some string filename.

Response.Clear()
Response.ContentType = "application/octet-stream"
Response.AddHeader("Content-Disposition", "attachment;filename=""" & filename & """")
Response.BinaryWrite(data)
Response.End()

Put the above code in some download.aspx and link to this file. You probably want to pass some query string information to your download.aspx, so that your code knows which file to get from the database.