Reading Objects to PDF from SQL server DB

Cyberduke

Member
Joined
Nov 18, 2019
Messages
17
Programming Experience
1-3
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.

C#:
            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();
                }
 
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.
 
If you are going to transfer the data in chunks, the code should look something like this:
C#:
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;
            }
        }
    }
}
 
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.
 
EDIT, It works, The problem came with some idiot(Me) copy and pasting like a lunatic.

Thanks for the help
 
Last edited:
Back
Top Bottom