reading BLOB image from MySQL database

Question

I'm having some trouble reading a blob back from a MySQL database. I've gotten it to successfully insert into the database, but can't seem to get it to read back. I know some of you might be thinking "why is he using a database to store blobs for images, and not just the file paths / file names", but i want to have flexibility and as a lot of these images will be stored on a server and not locally, this optimises efficiency, as well as allowing me to move images to local if needed. I've followed a (short) tutorial and have written this following method for recieving a blob;

public void getBlob(string query, string fileOut)
    {
        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, mConnection);

            //the index number to write bytes to
            long CurrentIndex = 0;

            //the number of bytes to store in the array
            int BufferSize = 100;

            //The Number of bytes returned from GetBytes() method
            long BytesReturned;

            //A byte array to hold the buffer
            byte[] Blob = new byte[BufferSize];


            //We set the CommandBehavior to SequentialAccess
            //so we can use the SqlDataReader.GerBytes() method.

            MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

            while (reader.Read())
            {
                FileStream fs = new FileStream(DeviceManager.picPath + "\\" + reader["siteBlobFileName"].ToString(), FileMode.OpenOrCreate, FileAccess.Write);
                BinaryWriter writer = new BinaryWriter(fs);
                CurrentIndex = 0;
                BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0,BufferSize);  

                while (BytesReturned == BufferSize)
                {
                    writer.Write(Blob);
                    writer.Flush();
                    CurrentIndex += BufferSize;
                    BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize);
                }

                writer.Write(Blob, 0, (int)BytesReturned);
                writer.Flush();
                writer.Close();
                fs.Close();
            }
            reader.Close();

            this.CloseConnection();
        }
    }

and i'm calling it like so..

 mDBConnector.getBlob("SELECT siteMapPicture, siteBlobFilename FROM sites WHERE siteID = '" + DeviceManager.lastSite + "'", DeviceManager.picPath + "mappicsite" + DeviceManager.lastSite);


PBSite.BackgroundImage = Image.FromFile(DeviceManager.picPath + "mappicsite" + DeviceManager.lastSite);

However it's erroring on the BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0,BufferSize); with the error "GetBytes can only be called on binary or guid columns". I'm assuming this is to do with my field type in my database, but changing the column to type binary means i have to then store that as a blob sort of, but i want to leave the filename just as a regular string. Is there something i'm missing? or another way of doing this?

edit1 : i think the first parameter for bytesreturned is to do with column in the reader, setting this to 0 gives the error "Invalid attempt to read a prior column using SequentialAccess", ill look into this.

edit2 : Removing sequential access gives me a file of size 13 bytes, (which could be just the first row, which is why sequential access reads all rows?) so maybe i'm reading columns in the wrong order..

edit 3: i believe that the reason for this error was due to the way i was inputting into the database. having changed this method, my saveBlob now looks like so:

public void saveBlob(string filePath, string fileName, string siteID)
    {
        if (this.OpenConnection() == true)
        {

            //A stream of bytes that represnts the binary file
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);

            //The reader reads the binary data from the file stream
            BinaryReader reader = new BinaryReader(fs);

            //Bytes from the binary reader stored in BlobValue array
            byte[] BlobValue = reader.ReadBytes((int)fs.Length);

            fs.Close();
            reader.Close();


            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = mConnection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO x (y, z) VALUES (@BlobFile, @BlobFileName)";

            MySqlParameter BlobFileNameParam = new MySqlParameter("@BlobFileName", SqlDbType.NChar);
            MySqlParameter BlobFileParam = new MySqlParameter("@BlobFile", SqlDbType.Binary);
            cmd.Parameters.Add(BlobFileNameParam);
            cmd.Parameters.Add(BlobFileParam);
            BlobFileNameParam.Value = fileName;
            BlobFileParam.Value = BlobValue;



                cmd.ExecuteNonQuery();

            this.CloseConnection();
        }
    }

i've ran through the debugger, and both blobvalue and blobfileparam(@blobfile) have the full size ( around 150k ), but it's erroring upon executing the query, giving the following error;

"unable to cast object of type 'system.byte[]' to type 'system.iconvertible"

i've taken a look into the code and tried to change types binary to image, to allow larger files, but gives the same error. Anyone know anything about this new information?

edit 4: fixed everything. noticed that in my code i was using:

 ("@BlobFile", SqlDbType.Binary);

Changed these to types "MySqlDbType" (derp) and it allowed me to choose types of blob. Things are finally working as intended : )

Solution

Have you tried simplifying first? Instead of reading the BLOB 100 bytes at a time, try simplifying your code to just read all bytes to a file. This way you can easily rule out data layer issues.

The following documentation also suggests you store your file size as another column: Handling BLOB Data With Connector/NET

Comments

[N.B.]

I'm no C# expert, but you are selecting 2 columns - siteMapPicture and siteBlobFilename - are both blobs or is one varchar/char/text?

[Kestami]

siteMapPicture is a BLOB, siteBlobFilename is a varchar

[Marc B]

Not a c# developer, but wouldn't your blob field be column #0, whereas you're trying to getbytes on column #1, which'd be the path field?

[Kestami]

i've just moved this around about 30 minutes ago whilst testing, and it doesn't error anymore, although does only give a file of size 13 bites...i might check if my stored blob is definitely intact.