C# Extract File From varbinary(max) field - file is corrupted

Question

Problem: I have a database, that has files in a varbinary(max) column. I know the type of file because there is an extension field. I need to pull the data out and save the files to the filesystem. I don't have a whole lot of information about how the data is stored. It looks like this in the DB:

I've tried a several different approaches to pull this value and save it as a file. I can create files from the data easy enough, but they are always corrupted. I get this message when opening a PDF.

I get a similar type of message if it's an excel file. (xls) I've tried the following methods to access the data, the first 2 using a SQL reader with or without the commandBehaviour.sequentialAccess. The third using SQL Substring.

byte[] data = (byte[])reader["Data"];

// OR

int ordinal = reader.GetOrdinal("Data");
long bufferSize = reader.GetBytes(ordinal, 0, null, 0, 0);
byte[] outbyte = new byte[bufferSize];
reader.GetBytes(ordinal, 0, outbyte, 0, (int)bufferSize);

// OR

        public System.IO.Stream GetStream(AttachmentModel info)
    {
        var attachment = (AttachmentModel)info;

        int start = 0;
        int packetSize = 65535;
        int length = packetSize;
        byte[] data = new byte[info.length];

        while (start < info.length)
        {
            length = Math.Min(packetSize, ((int)info.length - start));
            byte[] buffer = repo.ReadAttachmentData(attachment.id, start, length);
            buffer.CopyTo(data, start);
            start += length;
        }

        return new MemoryStream(data);
    }
    // ReadAttachmentData method
    public byte[] ReadAttachmentData(Guid attachmentID, int start, int length)
    {
        using (SqlConnection connection = GetOpenSqlConnection())
        {
            using (SqlCommand cmd = connection.CreateCommand())
            {
                byte[] buffer = null;

                cmd.CommandText = @"SELECT SUBSTRING([Data], @start, @length) FROM myBlobTable WHERE [Attachment] = @attachmentID";
                cmd.CommandType = CommandType.Text;
                AddParamWithValue(cmd, "@start", DbType.Int32, start + 1); // index starts at 1
                AddParamWithValue(cmd, "@length", DbType.Int32, length);
                AddParamWithValue(cmd, "@attachmentID", DbType.Guid, attachmentID);
                buffer = (byte[])cmd.ExecuteScalar();

                return buffer;
            }
        }
    }
    private IDataParameter AddParamWithValue(SqlCommand cmd, string name, DbType type, object value)
    {
        IDbDataParameter param = cmd.CreateParameter();
        param.ParameterName = name;
        param.DbType = type;
        param.Value = (value ?? DBNull.Value);
        param.Size = 0;
        param.Direction = ParameterDirection.Input;
        cmd.Parameters.Add(param);
        return param;
    }

To save the data to a file, I've tried:

System.IO.File.WriteAllBytes(fileName, byteArray);

// OR

using (Stream st = GetStream(att))
{
    using (FileStream fs = new FileStream(att.filePath + "File1" + att.extension, FileMode.Create, FileAccess.Write))
    {
        st.CopyTo(fs);
        st.Flush();
        fs.Flush();
        st.Close();
        fs.Close();
     }
};

// I tried using the deflate stream thinking maybe it was compressed.

using (Stream st = GetStream(attachment))
using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
    st.CopyTo(fs);
    st.Flush();
    fs.Flush();
    st.Close();
    fs.Close();
}

I also tried using buffer size options on my FileStream. I've tried pretty much every combination of the data access and data save methods above with no luck.

One other clue - is if i open the file in notepad, the data looks like this:

data:;base64,JVBERi0xLjQKJcfsj6IKNSAwIG9iago8PC9MZW5ndGg....etc, etc.

Any help is appreciated, thank you!

score 1 · Answer 1 · answered May 06 '20 at 15:36

I figured it out - basically through brute trial and error. Based on the data i found within the file when I opened in notepad I extract the base64 string and convert that to a file. So my process was, get the bytes from the DB, extract base64 string, convert back to bytes, write the file.

Access the varbinary(max) file this way:

using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
    List<AttachmentModel> attachments = new List<AttachmentModel>();
    while (reader.Read())
    {
        AttachmentModel att = new AttachmentModel();
        int ordinal = reader.GetOrdinal("Data");
        long bufferSize = reader.GetBytes(ordinal, 0, null, 0, 0);
        byte[] outbyte = new byte[bufferSize];
        reader.GetBytes(ordinal, 0, outbyte, 0, (int)bufferSize);
        att.data = outbyte; 

        attachments.Add(att);
    }

    return attachments;
}

And convert to file this way:

    using (MemoryStream ms = new MemoryStream(att.data)) {
    string fileContents;
    using (StreamReader reader = new StreamReader(ms))
    {
        fileContents = reader.ReadToEnd();
        List<string> arr = fileContents.Split(',').ToList<string>();

        // convert base 64 string back to bytes
        var myBytes = Convert.FromBase64String(arr[1]);
        System.IO.File.WriteAllBytes(att.filePath + "File1" + att.extension, myBytes);
     }
}

C# Extract File From varbinary(max) field - file is corrupted

1 Answers1