[SOLVED] how to retrieve img from MySQL into pictureBox

Issue

I want get an image from a Blob in MySQL then display image in a PictureBox. My incoming image is not correct and I don’t understand how I can retrieve the byte array because my current array is not correct.

My code:

add img into database:

using(OpenFileDialog ofd = new OpenFileDialog())
{
     if (ofd.ShowDialog() == DialogResult.OK)
     {
         byte[] bytes = File.ReadAllBytes(ofd.FileName);
         imageUrl = ofd.FileName.ToString();
         //roundPictureBox1.Image = Image.FromFile(ofd.FileName);
         roundPictureBox2.ImageLocation = imageUrl;
         MySqlConnection con = new MySqlConnection(connectionString);
         con.Open();
         MySqlCommand cmd = new MySqlCommand("INSERT INTO reg.img_table(image, id) VALUES (@image, @id)", con);
         long id = cmd.LastInsertedId;
         Properties.Settings.Default.idImg = id;
         cmd.Parameters.AddWithValue("@image", bytes);
         cmd.Parameters.AddWithValue("@id", id);
         cmd.ExecuteNonQuery();
         con.Close();
     }
}

return Img:

private Image byteArrayToImage(byte[] byteArrayIn)
{
    MemoryStream ms = new MemoryStream(byteArrayIn);
    Image returnImage = Image.FromStream(ms);
    return returnImage;
}

main code:

private void photoLoad()
{
    string connectionString = "datasource=localhost;" +
                     "port=3306;" +
                     "database=reg;" +
                     "username=root;" +
                     "password=Admin123";

    MySqlConnection con = new MySqlConnection(connectionString);

    byte[] ImageByte = new byte[0];
    string query1 = "select image from reg.img_table where id= @id";
    MySqlCommand cmd = new MySqlCommand(query1, con);
    cmd.Parameters.AddWithValue("@id", Properties.Settings.Default.idImg);
    
    try
    {
         con.Open();
         MySqlDataReader row;
         row = cmd.ExecuteReader();

         while (row.Read())
         {
             ImageByte = (Byte[])(row["image"]); 
         }

         if (ImageByte != null)
         {
             // You need to convert it in bitmap to display the image
             roundPictureBox1.Image = byteArrayToImage(ImageByte);
             roundPictureBox1.Refresh();
         }
    }
    catch (Exception ex)
    {
         MessageBox.Show("Error Img");
    }
}

An error doesn’t show. The login form shows but PictureBox doesn’t show the photo.

Solution

According to your OP, you’re storing your image in a database BLOB column in a MySql database. The following shows how to insert an image into a MySql database table. Then retrieve that image and display it in a PictureBox.

Download/install NuGet package: MySql.Data

Database Table: Student

enter image description here

I’ve chosen to store the connection string for the database in App.config.

App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="MySqlConnectionString" connectionString="Server=localhost;Port=3306;Database=University123;Uid=testAdmin;Pwd=password123;" />
    </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
    </startup>
</configuration>

Create a class: (name: HelperMySql.cs)

  • In VS menu, click Project
  • Select Add Class…

Add the following using statements:

  • using System.Configuration;
  • using System.IO;
  • using MySql.Data.MySqlClient;

HelperMySql.cs

Note: I’ve chosen to use LongBlob instead of Blob – this can be changed, if desired.

public class HelperMySql
{
    private string _connectionStr = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
    //private string _connectionStr = "Server=localhost;Port=3306;Database=University123;Uid=testAdmin;Pwd=password123;";

    public void ExecuteNonQuery(string sqlText)
    {
        using (MySqlConnection con = new MySqlConnection(_connectionStr))
        {
            //open
            con.Open();

            using (MySqlCommand cmd = new MySqlCommand(sqlText, con))
            {
                //execute
                cmd.ExecuteNonQuery();
            }
        }
    }

    public byte[] GetImageAsByteArray(string filename)
    {
        //reads image from file and returns as byte[]

        if (String.IsNullOrEmpty(filename))
            throw new Exception("Error (GetImageAsByteArray) - Filename not specified.");
        else if(!File.Exists(filename))
            throw new Exception($"Error (GetImageAsByteArray) - File '{filename}' doesn't exist.");
        
        return System.IO.File.ReadAllBytes(filename);
    }


    public void TblStudentCreate()
    {
        //for mySQL, use backticks (ex: `First Name`) if tablename has space in it
        string sqlText = @"CREATE TABLE Student (Id int NOT NULL AUTO_INCREMENT,
                                                       FirstName varchar(50),
                                                       LastName  varchar(75),
                                                       Img longblob,
                                                       CONSTRAINT PK_Student_ID PRIMARY KEY(ID));";

        ExecuteNonQuery(sqlText);

        Debug.WriteLine("Info: Table created (Student)");
    }

    public void TblStudentInsert(string firstName, string lastName, string filename)
    {
        if (String.IsNullOrEmpty(filename))
            throw new Exception("Error (TblStudentInsert) - Filename not specified.");
        else if (!File.Exists(filename))
            throw new Exception($"Error (TblStudentInsert) - File '{filename}' doesn't exist.");

        byte[] imageBytes = File.ReadAllBytes(filename);

        TblStudentInsert(firstName, lastName, imageBytes);
    }

    public void TblStudentInsert(string firstName, string lastName, byte[] imageBytes)
    {
        string sqlText = "INSERT INTO Student (FirstName, LastName, Img) VALUES (@firstName, @lastName, @imageBytes);";

        using (MySqlConnection con = new MySqlConnection(_connectionStr))
        {
            //open
            con.Open();

            using (MySqlCommand cmd = new MySqlCommand(sqlText, con))
            {
                //add parameters

                //FirstName
                if (!String.IsNullOrEmpty(firstName))
                    cmd.Parameters.Add("@firstName", MySqlDbType.VarChar).Value = firstName;
                else
                    cmd.Parameters.Add("@firstName", MySqlDbType.VarChar).Value = DBNull.Value;

                //LastName
                if (!String.IsNullOrEmpty(lastName))
                    cmd.Parameters.Add("@lastName", MySqlDbType.VarChar).Value = lastName;
                else
                    cmd.Parameters.Add("@lastName", MySqlDbType.VarChar).Value = DBNull.Value;
                
                //Img
                if (imageBytes != null)
                    cmd.Parameters.Add("@imageBytes", MySqlDbType.LongBlob).Value = imageBytes;
                else
                    cmd.Parameters.Add("@imageBytes", MySqlDbType.VarChar).Value = DBNull.Value;

                //execute
                cmd.ExecuteNonQuery();
            }
        }
    }

    public byte[] GetStudentImage(int id)
    {
        string sqlText = "SELECT img from Student where Id = @id;";

        using (MySqlConnection con = new MySqlConnection(_connectionStr))
        {
            //open
            con.Open();

            using (MySqlCommand cmd = new MySqlCommand(sqlText, con))
            {
                cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = id;
                
                //execute
                using (MySqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.HasRows)
                    {
                        while(dr.Read())
                        {
                            //get image from database and return as byte[]
                            if (dr["Img"] != null && dr["Img"] != DBNull.Value)
                                return (byte[])dr["Img"];
                        }
                    }
                }
            }
        }

        return null;
    }
}

Usage: Insert Record to Database

private HelperMySql helper = new HelperMySql();
                 ...
helper.TblStudentInsert("John", "Smith", @"D:\Images\Students\JohnSmith.png");

Usage: Display Image in PictureBox (name: pictureBox1)

int studentId = 1;
byte[] imageBytes = helper.GetStudentImage(studentId);

using (MemoryStream ms = new MemoryStream(imageBytes))
{
    pictureBox1.Image = Image.FromStream(ms);
    pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage; //fit to size
    pictureBox1.Refresh();
}
   

Note: You may consider storing the filenames in the database, and store the actual files in the file system instead.

Resources:

Answered By – user9938

Answer Checked By – David Goodson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.