How to retrieve image blob from MySQL into C# application?

Question

I stored a collection of PNG images in my MySQL database of type LONGBLOB. I am trying to retrieve them using C#. This is my first attempt of retrieving type LONGBLOB, and everything that I read didn't seem to help so far. The ones on StackOverflow relate but use different languages or different types of SQLs, so I haven't found an exact answer for this specific language and SQL version, as well as how to convert it over to System.Drawing.Image.

This is my structure:

public struct HeadshotOBJ
{
    public int ID;
    public System.Drawing.Image Headshot;  
    public int HairColorID;
    public int HairLengthID;
    public int HairTypeID;
    public int EyeColorID;
    public int GenderID;
    public int SkinToneID;
    public int BodyTypeID;
}

Next, I have the following to retrieve my fields:

            query.Append("SELECT * FROM headshots;");

            cmd = new MySqlCommand(query.ToString(), conn);

            // Execute query
            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                HeadshotOBJ headshot;

                headshot.ID = Convert.ToInt32(dr["id"]);
                headshot.Headshot = dr["headshot"];   // Need to convert somehow to System.Drawing.Image here.
                headshot.HairColorID = Convert.ToInt32(dr["hair_color_id"]);
                headshot.HairLengthID = Convert.ToInt32(dr["hair_length_id"]);
                headshot.HairTypeID = Convert.ToInt32(dr["hair_type_id"]);
                headshot.EyeColorID = Convert.ToInt32(dr["eye_color_id"]);
                headshot.GenderID = Convert.ToInt32(dr["gender_id"]);
                headshot.SkinToneID = Convert.ToInt32(dr["skin_tone_id"]);
                headshot.BodyTypeID = Convert.ToInt32(dr["body_type_id"]);

                lstHeadShots.Add(headshot);
            }

I'm certain I need to read the bytes in, but I'm lost on how to do so and how that should look in code. I'm not new to SQL, and my job used BLOBs in the past, but I never tried retrieving one before on my own, especially when storing the data into a System.Drawing.Image object.

If MySQL handles it the same way SQL Server does, at least in code, then you should be able to read the BLOB as a `byte[]`. Then you can convert that to a `System.Drawing.Image`: https://stackoverflow.com/questions/9173904/byte-array-to-image-conversion — David, Apr 12 '18 at 00:13
I often don't work directly with bytes, to be honest, so seeing an example of this will be helpful for learning with. — Bob, Apr 12 '18 at 00:21
`byte[] imageBytes = dr["headshot"];` (If that, combined with the question linked above, works then I'll gladly add it as an answer below. The only reason I'm using the comments is because I'm not certain it would work and don't have MySQL handy to test.) — David, Apr 12 '18 at 00:22
https://stackoverflow.com/questions/2658054/converting-to-byte-array-after-reading-a-blob-from-sql-in-c-sharp — David, Apr 12 '18 at 00:25
Possible duplicate of [Display an image stored in MySql databse in BLOB format using c#](https://stackoverflow.com/questions/12131087/display-an-image-stored-in-mysql-databse-in-blob-format-using-c-sharp) — Ňɏssa Pøngjǣrdenlarp, Apr 12 '18 at 00:30

score 2 · Accepted Answer · answered Apr 12 '18 at 00:28

Not sure if this really counts as a "duplicate" question since it's the combination of a couple questions I found, but...

According to this you can read the BLOB as a byte[] array:

byte[] imageBytes = (byte[])dr["headshot"];

From there, you can convert that to an Image:

using (var ms = new MemoryStream(imageBytes))
{
    headshot.Headshot = System.Drawing.Image.FromStream(ms);
}

How to retrieve image blob from MySQL into C# application?

1 Answers1