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?

Answer 1

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.

Answer 2

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!