Reading Binary Data From Database
Storing binary data directly in the database is a fairly common practice. I recently found myself wondering how to read binary data from the database and write it to the browser.
For example, suppose you have a PDF file stored in the database. How do you send it to the browser? I ended up with this:
protected void Page_Load(object sender, EventArgs e){ using (SqlConnection connection = new SqlConnection("Connection string goes here")) { connection.Open();using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "dbo.GetPdf"; command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Id", 1);
using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read() == false) { return; }
<span class="kwd">int</span> contentLength = Convert.ToInt32(reader.GetBytes(0, 0, <span class="kwd">null</span>, 0, <span class="kwd">int</span>.MaxValue)); <span class="kwd">byte</span>[] buffer = <span class="kwd">new byte</span>[contentLength]; reader.GetBytes(0, 0, buffer, 0, contentLength); Response.ContentType = <span class="str">"application/pdf"</span>; Response.BinaryWrite(buffer);} } } }
This works, but I wonder if it's the most efficient way to solve the problem? For example, we're calling the GetBytes method twice, which means that we're actually reading the whole document just to get its length. If the document is huge, this is wasteful. In that situation, I'm guessing having a fixed-sized buffer and reading the data in a while loop would be the way to go.
