How can I store a PDF file in a SQL database?

[Karen Grube]

Hi!

Each week, we receive a two-page PDF file from UPS along with a separate
flat file (a CSV) The PDF file contains the overview of our weekly invoice
and the CSV contains the details of each shipment.

I download the file from UPS and then use DTS to import the data into
SQL. At that point, I have a Crystal report that prints what looks like a
regular UPS invoice. The only problem is that I wind up with having to print
two things: the overview PDF and my detailed report.

What I'd like to do is store the PDF in a field in SQL and then simply
include that field in the report header (or a subreport) of the crystal
report so that the PDF file displays as the first and second pages of the
report (it's always two pages).

My problem is that I have absolutely no clue how to use DTS or even
VB.Net to take this PDF file sitting in a folder on our network and load it
into a SQL field. I don't even know what kind of field it should be. An
Image field? Binary? I really don't even know where to begin.

I've seen code to upload images and even word docs, but nothing that
would tell me what file type to use or how to code storing a PDF file. I
can't even seem to find out from anyone if I can even display a stored PDF in
a Crystal report, though I know you can an image file.

Any specific suggestions, code snippets, or referrals to examples that
explictly describe how to upload and store a PDF file in a SQL field would be
greatly appreciated. Again, I'm already using DTS to upload the CSV of the
detail data. Might there be a way within DTS? If there is, I couldn't find
it. Perhaps there might be a little piece of VB Script code that I can
include in a field transformation that will upload the file.

Thanks!! I know I can always count on someone here having a great answer.

Karen Grube
kl*****@yahoo.com
Just hyperlinking to the pdf file stored on the network from within the
report won't work. The PDF file has to display fully and be printable the
moment the report opens. It can't depend on someone having to click on a
link.

[aiKeith]

Hello Karen,

You should store the file in a image data type.

The binary data type only allows up to 8k of storage, whereas the image data
type allows up to 2GB of storage.

My first question would be:
1.) Are you sure you want to store the PDF's in the SQL table as opposed to
just a reference/link to the file? This could be quite cumbersome if you
have MANY rows of data.

If you do, then one way would be as follows:

1.) Create a MemoryStream
2.) Use a BinaryFormatter
3.) Call the Serialize method of the formatter and put the contents into
the stream
4.) close the stream
5.) Add to SQL

Here is a quick example.. for reference only..

MemoryStream myStream = new MemoryStream(); BinaryFormatter
myFormatter = new BinaryFormatter();
myFormatter.Serialize(myStream, obj);
long binaryLength = myStream.Length;
myStream.Close();
myCommand.Parameters.Add("@ObjectLen", binaryLength);
myCommand.Parameters.Add("@Object", myStream.ToArray());

Let me know if thats clear...

"Karen Grube" wrote:

Hi!

Each week, we receive a two-page PDF file from UPS along with a separate
flat file (a CSV) The PDF file contains the overview of our weekly invoice
and the CSV contains the details of each shipment.

I download the file from UPS and then use DTS to import the data into
SQL. At that point, I have a Crystal report that prints what looks like a
regular UPS invoice. The only problem is that I wind up with having to print
two things: the overview PDF and my detailed report.

What I'd like to do is store the PDF in a field in SQL and then simply
include that field in the report header (or a subreport) of the crystal
report so that the PDF file displays as the first and second pages of the
report (it's always two pages).

My problem is that I have absolutely no clue how to use DTS or even
VB.Net to take this PDF file sitting in a folder on our network and load it
into a SQL field. I don't even know what kind of field it should be. An
Image field? Binary? I really don't even know where to begin.

I've seen code to upload images and even word docs, but nothing that
would tell me what file type to use or how to code storing a PDF file. I
can't even seem to find out from anyone if I can even display a stored PDF in
a Crystal report, though I know you can an image file.

Any specific suggestions, code snippets, or referrals to examples that
explictly describe how to upload and store a PDF file in a SQL field would be
greatly appreciated. Again, I'm already using DTS to upload the CSV of the
detail data. Might there be a way within DTS? If there is, I couldn't find
it. Perhaps there might be a little piece of VB Script code that I can
include in a field transformation that will upload the file.

Thanks!! I know I can always count on someone here having a great answer.

Karen Grube
kl*****@yahoo.com
Just hyperlinking to the pdf file stored on the network from within the
report won't work. The PDF file has to display fully and be printable the
moment the report opens. It can't depend on someone having to click on a
link.

[Karen Grube]

Hi!

Wow! What I ended up having to do is save each of the two pages of each
pdf as a jpg file, and then storing the jpg files in two SQL image fields!

Crystal reports just wouldn't work and play well with the pdf, whether it
was linked or not. I could never get the pdf to simply display as the first
two pages of the report the way I wanted. BUT, converting the pdf to a jpg,
storing the jpg's and then including those two image (blob) fields in 'group
headers' on the report allows them to display perfectly! I'm a tad bit
concerned about the quality of the jpg conversoin, but it actually prints out
really well and looks fine on the screen.

Thanks for your help! The sample code provided really pointed me in the
right direction.

Thanks again!
Karen

[aiKeith]

Sure thing, glad you got it figured out....

"Karen Grube" wrote:

Hi!

Wow! What I ended up having to do is save each of the two pages of each
pdf as a jpg file, and then storing the jpg files in two SQL image fields!

Crystal reports just wouldn't work and play well with the pdf, whether it
was linked or not. I could never get the pdf to simply display as the first
two pages of the report the way I wanted. BUT, converting the pdf to a jpg,
storing the jpg's and then including those two image (blob) fields in 'group
headers' on the report allows them to display perfectly! I'm a tad bit
concerned about the quality of the jpg conversoin, but it actually prints out
really well and looks fine on the screen.

Thanks for your help! The sample code provided really pointed me in the
right direction.

Thanks again!
Karen