Open Binary File From Database C#

Question

I have PDF file data in a SQL Server database, in the column type image (bad previous db designer). What I need to do is read that binary data out to the client, so that they could download the PDF directly to their computer.

So far, my code is as follows:

SqlConnection con = new SqlConnection();
con.ConnectionString = "casIntranetConnectionString";

SqlCommand com = new SqlCommand("SELECT [File], [FileName] FROM [com].[catalog1] WHERE [FileName] = @filename");
com.Connection = con;
com.Parameters.AddWithValue("filename", Request.QueryString["filename"]);

con.Open();

SqlDataReader reader = com.ExecuteReader();

if (reader.Read())
{
    Response.Clear();
    Response.AddHeader("Content-Type", "application/pdf");
    Response.AddHeader("Content-Disposition", "inline; filename=" + Request.QueryString["filename"] + ".pdf");
}

I'm assuming I'm going to need the reader to read out the bytes, but that's where I don't really know what I do. Any suggestions?

Thanks!

Solution 1

You can use the HttpResponse BinaryWrite method:

var bytes = reader.GetSqlBytes(index);
Response.BinaryWrite(bytes.Value);

As an aside, please consider separating responsibilities, you have a method which is responsible for accessing the database AND writing to the response. This will lead to maintenance problems in the future. See here for a useful blog post describing SOLID principles. Apologies if your code snippet was contrived, but in case any one else stumbles across this question figured I'd include a "but don't do it like this" disclaimer!

Solution 2

If you want to avoid heap fragmentation (especially with server-side code), and thus avoid allocating huge byte arrays, you can do streaming, something like this:

        using (SqlConnection cnx = new SqlConnection(@"your connection string"))
        {
            cnx.Open();
            using (SqlCommand cmd = cnx.CreateCommand())
            {
                cmd.CommandText = "SELECT [File] FROM [com].[catalog1] WHERE [FileName] = @filename";
                cmd.Parameters.AddWithValue("filename", Request.QueryString["filename"]);

                // sequential access is used for raw access
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                {
                    if (reader.Read())
                    {
                        // must be lower than 85K (to avoid Large Object Heap fragmentation)
                        byte[] chunk = new byte[0x10000]; 
                        long read;
                        long offset = 0;
                        do
                        {
                            read = reader.GetBytes(0, offset, chunk, 0, chunk.Length);
                            if (read > 0)
                            {
                                Response.OutputStream.Write(chunk, 0, (int)read);
                                offset += read;
                            }
                        }
                        while (read > 0);
                        Response.AddHeader("Content-Type", "application/pdf");
                        Response.AddHeader("Content-Disposition", "inline; filename=" + Request.QueryString["filename"] + ".pdf");                        
                    }
                }
            }
        }

And if you serve the same file often, then it's better to write this file directly to some Cache directory on the server and then re-use it for subsequent request, with the Response.TransmitFile API which is the best in terms of performance (uses kernel mode if possible).

Solution 3

I believe that this should work:

if(reader.Read())
{
    Byte[] pdfData = (byte[])reader.GetValue(0);;
    Response.Buffer = true;
    Response.ContentType = "application/PDF";
    Response.BinaryWrite(pdfData);
}