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.
CodePudding user response：
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
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: