Binary Retrieval From SQL Server

Retrieving binary from a database is often a necessity in online application development. For accessibility reasons you can store PDF files in a database so they can be retrieved quickly without relying on a file system that is not accessible.

What is varbinary?

In SQL Server I like to store these files in a “varbinary(max)” datatype. According to Microsoft’s web site a varbinary(max) field is defined as:

varbinary [ ( n | max) ] Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.

This is a little code used to retrieve a PDF file from an SQL Server database.

System.IO.FileStream fs;                // Writes the binary to a file (*.pdf).
System.IO.BinaryWriter bw;              // Streams the binary to the FileStream object.
int bufferSize = 100;                   // Initial size of the binary buffer.
byte[] outbyte = new byte[bufferSize];  // The binary byte[] buffer to be filled by GetBytes.
long retval;                            // The bytes returned from GetBytes.
long startIndex = 0;                    // The starting position in the binary output.
string rec_id = "";                     // The id to use in the file name.
 
while (myReader.Read())
{
    // Get the id.
    rec_id = myReader["DocumentationUploadID"].ToString();
 
    // Create a file to hold the output.
    fs = new System.IO.FileStream("DocumentID-" + rec_id + ".pdf", System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);
    bw = new System.IO.BinaryWriter(fs);
 
    // Reset the starting byte for the new Binary.
    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();