Reading Objects to PDF from SQL server DB

[Cyberduke]

I have some PDF files saved as an object in SQL Server(I did not put them there, my job is just to retrieve them), and I would like to save those back as PDF's.

I currently have this. It is saving PDF's but they cannot be opened. They are of appropriate size so there are bytes read into them. I just seem to have a small mistake that I cannot find.
I would appreciate any help.

            FileStream fs;                          // Writes the BLOB to a file (*.bmp).
            BinaryWriter bw;                        // Streams the BLOB to the FileStream object.
            int bufferSize = 1000000;               // Size of the BLOB buffer.
            byte[] outbyte = new byte[bufferSize];  // The BLOB byte[] buffer to be filled by GetBytes.
            long retval;                            // The bytes returned from GetBytes.
            long startIndex = 0;                    // The starting position in the BLOB output.
            string filename= "";

                String queryString = "SELECT  dbo.DBDocument.DocumentName,dbo.DBDocBlob.Object FROM dbo.DBDocBlob, dbo.DBDocument WHERE dbo.DBDocBlob.DocumentID = dbo.DBDocument.DocumentID";


                String ConnectionString = "Server=SERVER_NAME;Database=DB_NAME;Trusted_Connection=True;";
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {

                    SqlCommand command = new SqlCommand(queryString, connection);
                    connection.Open();
                    SqlDataReader myReader = command.ExecuteReader(CommandBehavior.SequentialAccess);

                    while (myReader.Read())
                    {

                        // Get the filename, which must occur before getting the file.
                        filename = myReader[0].ToString();

                        // Create a file to hold the output.
                        fs = new FileStream(filename + ".pdf",

                        FileMode.OpenOrCreate, FileAccess.Write);

                        bw = new BinaryWriter(fs);

                        // Reset the starting byte for the new BLOB.
                        startIndex = 0;


                        // Read the bytes into outbyte[] and retain the number of bytes returned.
                        retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

                        // Continue reading and writing while there are bytes beyond the size of the buffer.
                        while (retval == bufferSize)
                        {

                            bw.Write(outbyte);
                            bw.Flush();

                            // Reposition the start index to the end of the last buffer and fill the buffer.
                            startIndex += bufferSize;
                            retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
                        }


                        // Write the remaining buffer.
                        bw.Write(outbyte, 0, (int)retval);
                        bw.Flush();

                        // Close the output file.
                        bw.Close();
                        fs.Close();
                    }


                    // Close the reader and the connection.
                    myReader.Close();
                    connection.Close();
                }

 

[jmcilhinney]

Are the files big enough to make reading in chunks necessary? If not, just get the data as a single byte array and call File.WriteAllBytes. If they are, don't use a BinaryWriter. Just write to the FileStream.

 

[jmcilhinney]

If you are going to transfer the data in chunks, the code should look something like this:

using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
    while (reader.Read())
    {
        var fileName = reader.GetString(0);

        using (var file = File.OpenWrite(fileName + ".pdf"))
        {
            const int BUFFER_SIZE = 4096;
            var buffer = new byte[BUFFER_SIZE];
            var index = 0;
            int byteCount;

            while ((byteCount = (int) reader.GetBytes(1, index, buffer, 0, BUFFER_SIZE)) > 0)
            {
                file.Write(buffer, 0, byteCount);
                index += byteCount;
            }
        }
    }
}

 

[Cyberduke]

Thank you for the help, I have around 270 000 PDF's in a 170GB databse, so under 1mb per PDF.

I implemented your suggestion, and the output from this is the same as before, Same size pdf's but they are not readable. I am starting to think that there could have been something done to the PDF's before being put into the Database? However this seems extremely unlikely.

 

[jmcilhinney]

Did you try File.WriteAllBytes? If that didn't work then I would suggest that the data is definitely corrupt.

 

[Cyberduke]

EDIT, It works, The problem came with some idiot(Me) copy and pasting like a lunatic.

Thanks for the help