Saving PDFs in SQL Server

Question

From the DB schema that we have I think we are saving images in the SQL Server, I found a column called ImageContents(text null) in our Images table.

Now I have a folder that has bunch of PDF files and I want to save them in that table.

How do we do that? Why is it s text type column?

I Googled the topic but mostly they were saying save the image somewhere else and save a link to its address in the SQL Server but that is not how we do it so I couldn't find an answer by myself.

http://stackoverflow.com/questions/2579373/saving-any-file-to-in-the-database-just-convert-it-to-a-byte-array — Dinoel Vokiniv, Sep 24 '14 at 17:40
`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx). Also, for storing **binary data** like a PDF file, `text` is a really bad choice - use `varbinary(max)` instead! — marc_s, Sep 24 '14 at 17:41
@LeonidVinikov My schema is "Text", the link you found is VarBinary I don't have an option of modifying the shcema — ConfusedSleepyDeveloper, Sep 24 '14 at 17:46
There are numerous approaches to loading a file and then saving it within SQL. Is this web based or desktop? PDFs are not text files so you will want to use a `varbinary(max)` column. Lastly, the very act of storing BLOB data within SQL will surely result in a debate of its own as there lots of pros and cons and even more opinions. The alternative would be to store the PDFs in a common folder and then store links to those file within SQL. — andleer, Sep 24 '14 at 17:47
If you are limited to storing your PDFs in a `text` based column, that complicates matters. You will need to encode your PDF files and the result will not be terribly efficient. Producing and end to end answer to your question is likely beyond the scope of a single SO answer. http://stackoverflow.com/questions/475421/ — andleer, Sep 24 '14 at 17:50
@andleer Can you please show me one of those numerous ways? It is a Windows Console application really, they run it, it reads the folder that has those PDFs and Saves them to that Table I mentioned above. — ConfusedSleepyDeveloper, Sep 24 '14 at 17:50
I am confused. Your question states that you need to read files and store them in SQL but your last comment states the application is already doing that. — andleer, Sep 24 '14 at 17:53
2579373 at the top of the comment thread lists a number of approaches. — andleer, Sep 24 '14 at 17:54
Yes, this is the better approach: "save the image somewhere else and save a link to its address in the SQL Server". Look at your application again. Are you sure that's not what's going into this text field? A path and a filename? If the code is sticking the PDF file in there directly, it's probably already broken over the binary/text encoding step, and you'll have to fix it anyway. In general, use file systems for large pieces of data, and databases for small pieces of data that are connected to each other. — criticalfix, Sep 24 '14 at 18:37
@criticalfix yes I confirmed that with some other people at work...I can't change the database schema and I can't save a link of the image to somewhere else..This is what it is: SavePDF in DB column of type Text...Now how do I do that? — ConfusedSleepyDeveloper, Sep 24 '14 at 19:09
http://sqltales.wordpress.com/2012/05/15/varbinary-vs-filestream-and-other-blob-issues-3/ somewhat off topic but may be of interesest — Nick, Sep 24 '14 at 19:57

score 1 · Answer 1 · edited May 23 '17 at 11:49

Since you're stuck with a text field, and you have binary data, you will need to encode your binary data into something that will go in a text column, and you will also have to decode it when you get it out again.

Base 64 encoding is one way to do this. (Note that the encoded form will take somewhat more space.)

Convert.ToBase64String(bytes) and Convert.FromBase64String(string) should get you back and forth, as in this example. (For the byte array step, use File.ReadAllBytes or see Vinikov's link.)

Again, it would probably be better to store a path and filename in the database, and keep the PDF files in the file system, but I understand you don't have the option to do that in this case.

P.S. SQL Server now has a FileStream attribute on varbinary(max) fields, that will store large files like this in the file system (though you have to enable it and configure it). Again, I understand that you don't have the option to use varbinary(max) in this case.

score 0 · Answer 2 · answered Sep 24 '14 at 19:55

If you are already storing images in your database, look again and probably that you will find a column of type Image or varbinary(max). This is what you need to store your PDF files as they are binary files and not text files. Possibly that you can use the same type of code as what you are already using for storing your images; if such code is available. Otherwise, you shouldn't have any problem to find on the internet how to store an image file into a column of type Image or Varbinary(max). Storing a PDF file is exactly the same as storing an image file; except maybe for the extraction of metadata (such as decoding the width and the length of an image) and the checking of the suffix (.GIF, .PNG, etc.).

Do not use the column of type Text (or of varchar(max) or nvarchar(max)) because there would be a real possibility of corrupting your PDF files.

With the older versions of SQL Server such as 6.5 or 7.0; there was a real performance issue with storing files in a database but these problems have since been corrected.

Saving PDFs in SQL Server

2 Answers2