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!

Before you do anything, is there a reason you are doing this manually with ADO.NET in 2012? — TimothyP
– TimothyP, Commented Jan 24, 2012 at 15:24
I suggest you take a look at technologies such as the entity framework, while I am certainly not in any position to tell you whether or not it will be suitable for your project, I think it "may" be. It allows you to focus on the what and not so much the how. Please take no offence, non intended — TimothyP
– TimothyP, Commented Jan 24, 2012 at 17:12
can you directly inject a binary file into a media player so that there is no download just an instant playing of file? — user1674991
– user1674991, Commented Sep 15, 2012 at 23:14

Rich O'Kelly · Accepted Answer · 2012-01-24 15:38:25Z

5

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!

edited Jan 24, 2012 at 15:38

answered Jan 24, 2012 at 15:31

Rich O'Kelly

41.8k9 gold badges87 silver badges114 bronze badges

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

Comments

Simon Mourier · Accepted Answer · 2012-01-24 15:40:20Z

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).

Justin Pihony · Accepted Answer · 2012-01-24 15:31:07Z

1

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);
}

answered Jan 24, 2012 at 15:31

Justin Pihony

67.2k20 gold badges154 silver badges185 bronze badges

Collectives™ on Stack Overflow

Open Binary File From Database C#

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related