我想從 MySQL 中獲取影像，Blob然后在 PictureBox 中顯示影像。我的傳入影像不正確，我不明白如何檢索位元組陣列，因為我當前的陣列不正確。
我的代碼：
將 img 添加到資料庫中：
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();
}
}
回傳圖片：
private Image byteArrayToImage(byte[] byteArrayIn)
{
MemoryStream ms = new MemoryStream(byteArrayIn);
Image returnImage = Image.FromStream(ms);
return returnImage;
}
主要代碼：
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");
}
}
不顯示錯誤。登錄表單顯示，但 PictureBox 不顯示照片。
uj5u.com熱心網友回復：
根據您的 OP，您將影像存盤在 MySql 資料庫中的資料庫 BLOB 列中。下面展示了如何將影像插入到 MySql 資料庫表中。然后檢索該影像并將其顯示在 PictureBox 中。
下載/安裝 NuGet 包：MySql.Data
資料庫表：學生
我選擇將資料庫的連接字串存盤在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>
創建一個類：（名稱：HelperMySql.cs）
- 在 VS 選單中，單擊專案
- 選擇添加類...
添加以下 using 陳述句：
- 使用 System.Configuration；
- 使用 System.IO；
- 使用 MySql.Data.MySqlClient；
HelperMySql.cs
注意：我選擇使用LongBlob而不是Blob- 如果需要，可以更改。
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;
}
}
用法：將記錄插入資料庫
private HelperMySql helper = new HelperMySql();
...
helper.TblStudentInsert("John", "Smith", @"D:\Images\Students\JohnSmith.png");
用法：在 PictureBox 中顯示影像（名稱：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();
}
注意：您可以考慮將檔案名存盤在資料庫中，而將實際檔案存盤在檔案系統中。
資源：
- MySQL 8.0 參考手冊
- 資料型別存盤要求
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/450328.html