How do I retrieve a file from a SQL Server database?

Question

I have successfully uploaded files into my SQL Server database. I can bring back the information into a GridView. I am unable to figure out how to create a hyperlink to actually open the file.

Answer 1

Have not tried this myself, but how about streaming the data into a temporary file on the local file system and then providing a link to that temporary file?

Answer 2

Normally you would use your server side code to send the appropriate headers, then just echo the content.

From the PHP manual:

// We'll be outputting a PDF
header('Content-type: application/pdf');

// It will be called downloaded.pdf
header('Content-Disposition: attachment; filename="downloaded.pdf"');

// The PDF source is in original.pdf
readfile('original.pdf');

Answer 3

If your file is stored in the database, you are probably storing it as a blog or byte array. On the hyperlink click event, you will need to pass the byte array into a stream, and use a stream writer to create the file. Then execute the file.

Answer 4

You need to create an URL that handles the pictures and returns the DB content into the response stream. As it happens at SQL Saturday #26 I had a presentation that showed exactly this. You can doaloand my slides from the link, go into Demo 2 and in the lotsOfPictures solution you'll find Picture.aspx.cs, that does exactly what you ask for:

using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand(
@"SELECT picture 
FROM resized_pictures 
WHERE picture_id = @id
AND picture_size = @size;", conn);
                cmd.Parameters.AddWithValue("@id", pictureId);
                cmd.Parameters.AddWithValue("@size", size);

                using (SqlDataReader rdr = cmd.ExecuteReader(
                    CommandBehavior.SequentialAccess))
                {
                    if (rdr.Read())
                    {
                        Response.ContentType = "image/jpeg";
                        byte[] bytes = new byte[1024];
                        long offSet = 0;
                        int countRead = (int) rdr.GetBytes(
                            0, offSet, bytes, 0, 1024);
                        while (countRead > 0)
                        {
                            Response.OutputStream.Write(bytes, 0, countRead);
                            offSet += countRead;
                            countRead = (int)rdr.GetBytes(
                                0, offSet, bytes, 0, 1024);
                        }
                    }
                }
            }

The important pieces of the puzzle are the SequentialAccess flag passed to the SqlCommand reader that will return a true stream, so the page does not load the whole image in memory before returnning. For a high performance server you should use async operations, like described in Asynchronous Pages in ASP.NET 2.0.