How do you insert a file (PDF) into a varbinary SQL Server column and later retrieve it?

Question

I'm looking to take the results of a report run (a PDF file from Crystal Reports), serialize it, stick it into a varbinary field, and then later be able to deserialize it and present it back to the user.

For now I have to just plain old ADO .NET (SqlClient, SqlCommand, etc.)

Are there any pitfalls to this? What is the basic syntax to accomplish this given the fact that I would know the path on the local machine where the PDF file was currently saved?

Answer 1

EDIT: Just seen in the MSDN documentation that Image is going to be removed. Better use use VARBINARY(MAX). (Hey I did not know that).

MY OLD ANSWER: VARBINARY has a limit of 8 KB. Can be too small to store PDF's. You'd better use the IMAGE datatype.

Use parameters to insert/select your data via the SqlCommand, and you can use just plain SQL like any ordinary field.

The data you pass into the image field is an array of bytes.

Edit 2: Since you are using C#, you should retrieve the data using a DataReader. To create the DataReader use the SequentialAccess flag on the SqlCommand.ExecuteReader, to prevent the data being read into memory too early.

Answer 2

I really wouldn't use a byte[] parameter to an IDbCommand at all. What if your PDF is 100 Mb big? As far as I know, this would get all those 100 Mb from your database (needing that amount of memory on your server) before sending the result to the requesting browser. Now think about getting a number of concurrent requests for different PDFs of roughly this size... per second... That is not very scalable.

For a possible solution, please check out "Blob + Stream = BlobStream", an article I wrote for the .NET Magazine here in the Netherlands a couple of years ago. This method uses SQL Server commands to access parts of a BLOB from a Stream derived class.

The article is in Dutch, but the example code that goes with it should be enough to get you started. Also, Peter De Jonghe seems to have written an article on CodeProject that expands on my article a bit, generalizing it to more than just image columns in SQL Server. This article is in English.

I hope this helps you with your problem.