C# saving images to MySql database as blob

Question

For some reason my code fails when I try to update the image for a user. The image is not saved properly. For instance an image of 38 kib is saved as 13 bytes in the database.

This is my code:

    public void UploadImage(Image img)
    {
        OpenConnection();
        MySqlCommand command = new MySqlCommand("", conn);
        command.CommandText = "UPDATE User SET UserImage = '@UserImage' WHERE UserID = '" + UserID.globalUserID + "';";
        byte[] data = imageToByte(img);
        MySqlParameter blob = new MySqlParameter("@UserImage", MySqlDbType.Blob, data.Length);
        blob.Value = data;

        command.Parameters.Add(blob);

        command.ExecuteNonQuery();
        CloseConnection();
    }

    public byte[] imageToByte(Image img)
    {
        using (var ms = new MemoryStream())
        {
            img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
            return ms.ToArray();
        }
    }

OpenConnection and closeconnection are simply conn.Open() and conn.Close().

The conversion however doesn't fail:

But in the database I see this:

Does anyone have any idea what is going on here?

Your problem is the `UserImage = '@UserImage'`, replace it with `UserImage = @UserImage` or more conventional `UserImage = ?UserImage` and it will work. You're sending a string, hence 10 bytes. — bokibeg, Dec 18 '15 at 15:11
just a side note, but are you sure you want to save as a blob. http://stackoverflow.com/questions/1347461/saving-images-files-or-blobs — Chris Hawkes, Dec 18 '15 at 15:18

score 2 · Accepted Answer · edited May 23 '17 at 12:23

Replace this code:

OpenConnection();
MySqlCommand command = new MySqlCommand("", conn);
command.CommandText = "UPDATE User SET UserImage = '@UserImage' WHERE UserID = '" + UserID.globalUserID + "';";
byte[] data = imageToByte(img);
MySqlParameter blob = new MySqlParameter("@UserImage", MySqlDbType.Blob, data.Length);
blob.Value = data;

command.Parameters.Add(blob);

command.ExecuteNonQuery();
CloseConnection();

With

var userImage = imageToByte(img);

OpenConnection();

var command = new MySqlCommand("", conn);

command.CommandText = "UPDATE User SET UserImage = @userImage WHERE UserID = @userId;";

var paramUserImage = new MySqlParameter("@userImage", MySqlDbType.Blob, userImage.Length);
var paramUserId = new MySqlParameter("@userId", MySqlDbType.VarChar, 256);  

paramUserImage.Value = userImage;
paramUserId.Value = UserID.globalUserID;    

command.Parameters.Add(paramUserImage);
command.Parameters.Add(paramUserId);

command.ExecuteNonQuery();  

CloseConnection();

You were sending '@UserImage' which is a 10 byte long string, remove the quotes and it should work.

Above code also uses parameters for both of your variables which you should always do.

Either way hope this helps you.

score 1 · Answer 2 · answered Dec 18 '15 at 15:21

At first, i like to save blobs in the database good one :)

If you pass a parameter, do not encapsulate it in ', because than ADO.Net/MySql will not recognize it as a parameter, rather than a string:

command.CommandText = "UPDATE User SET UserImage = @UserImage WHERE UserID = '" + UserID.globalUserID + "';";

If you start using parameter, why not pass UserID also as a parameter:

command.CommandText = "UPDATE User SET UserImage = @UserImage WHERE UserID = @userId;";

This would make every think much clearer.

One important thing: If you store blobs in the database, never use select * from ..., because often you don't want to retrieve the blob, but you will with the star. This cause unnecessary trafic and decrease the performance.

Hope this helps!

C# saving images to MySql database as blob

2 Answers2

Linked

Related