Open binary file from c # database

Question

I have the PDF file data in a SQL Server database, in the image column type (bad previous db developer). What I need to do is read the binary data for the client so that it can download the PDF directly to my 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 assume that I will need to read bytes to the reader, but I do not know what I am doing. Any suggestions?

Thanks!

Answer 1

You can use the HttpResponse BinaryWrite method:

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

Aside, please consider the separation of duties, you have a method that is responsible for accessing the database and writing to the response. This will lead to future maintenance problems. See here for a helpful blog post describing SOLID principles. I apologize if your code snippet was invented, but if anyone else came across this question, I assumed that I would include a “but don't do it like this” disclaimer!

Answer 2

If you want to avoid fragmentation of the heap (especially with server-side code) and thus avoid allocating huge arrays of bytes, 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 often serve the same file, it is better to write this file directly to some Cache directory on the server, and then reuse it for the subsequent request with Response.TransmitFile , which is the best in terms of performance (it uses kernel mode if possible) .

Answer 3

I believe this should work:

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