c# - 将图像加载到 C#，然后插入到 MySQL 表中

Question

这是我需要的:我正在制作一个用于库存控制的软件，但我必须在数据中上传一张产品图片。我正在使用 C# 和 MySQL。

我不知道如何把图片加载到pictureBox 到一个MySQL 表中。有任何想法吗？我完全迷失在这里。

我使用以下代码将图像加载到 pictureBox 中:

using (OpenFileDialog dlg = new OpenFileDialog())
            {
                dlg.Title = "Open Image";
                dlg.Filter = "All Files (*.*)|*.*";

                if (dlg.ShowDialog() == DialogResult.OK)
                {                                     
                    pictureBox1.Image = new Bitmap(dlg.FileName);
                }
            }

如何将加载的图像插入到 MySQL 表中？我想表中的图像属性应该是:

`img` LONGBLOB NOT NULL

然后，如何从 MySQL 将图像回调到 pictureBox？我想(再次)查询是这样的:

select img from table_name where id = '';

最后，当我有查询时，如何从 MySQL 将图像加载到 pictureBox？

非常感谢。

Answer 1

我将提供两种解决方案。第一种解决方案是将原始图像以字节为单位直接存储在数据库中。第二种解决方案是我个人推荐的——改为使用数据库中图像文件的路径。

此处摘录自 article这对是否使用 BLOB 提出了一些很好的观点。

Well, there are several reasons why you should not store binary data in your database:

• The whole point of storing data in a SQL database, is to put some kind of ordering and structure on your data, as well as being able to search on these data. But how do you search in the binary data of a picture?

• For large data sets, storing binary data will quickly run up the size of your database files, making it harder to control the size of the database.

• In order to store binary data in the database, you must continually escape and unescape the data to ensure that nothing breaks.

• Storing images on the file system has a marginally faster retrieval rate. Now here are some reasons why you should:

There is one good reason why you might want to store the binary data in the database:

• Replication. Storing images in a database allows for all of your data to be central stored which is more portable, and easy to replicate.

以下是选择图像文件的方式:

using (var openFileDialog = new OpenFileDialog())
{
   openFileDialog.Title = "Choose Image File";
   openFileDialog.InitialDirectory =
                Environment.GetFolderPath(Environment.SpecialFolder.MyPictures);
   openFileDialog.Filter = "Image Files (*.bmp, *.jpg)|*.bmp;*.jpg";
   openFileDialog.Multiselect = false;
   if (openFileDialog.ShowDialog() == DialogResult.OK)
   {
       pictureBox1.Image = new Bitmap(openFileDialog.FileName);
   }
   // store file path in some field or textbox...
   textBox1.Text = openFileDialog.FileName;
}

解决方案 1:BLOB 方法

// Write to database like this - image is LONGBLOB type
string sql = "INSERT INTO imagetable (image) VALUES (@file)";
// remember 'using' statements to efficiently release unmanaged resources
using (var conn = new MySqlConnection(cs))
{
    conn.Open();
    using (var cmd = new MySqlCommand(sql, conn))
    {
        // parameterize query to safeguard against sql injection attacks, etc. 
        cmd.Parameters.AddWithValue("@file", File.ReadAllBytes(textBox1.Text));
        cmd.ExecuteNonQuery();
    }
}

// read image from database like this
string sql = "SELECT image FROM imagetable WHERE ID = @ID";
using (var conn = new MySqlConnection(cs))
{
   conn.Open();
   using (var cmd = new MySqlCommand(sql, conn))
   {
      cmd.Parameters.AddWithValue("@ID", myInt);
      byte[] bytes = (byte[])cmd.ExecuteScalar();   
      using (var byteStream = new MemoryStream(bytes))
      {
         pictureBox1.Image = new Bitmap(byteStream);
      }
   }
}

方案二:在文件系统上存储文件路径

// Some file movement to the desired project folder
string fileName = Path.GetFileName(this.textBox1.Text);
string projectFilePath = Path.Combine(projectDir, fileName);
File.Copy(this.textBox1.Text, projectFilePath);

// Write to database like this - imagepath is VARCHAR type
string sql = "INSERT INTO imagepathtable (imagepath) VALUES (@filepath)";
using (var conn = new MySqlConnection(cs))
{
    conn.Open();
    using (var cmd = new MySqlCommand(sql, conn))
    {
        cmd.Parameters.AddWithValue("@filepath", projectFilePath);
        cmd.ExecuteNonQuery();
    }
}

// read from database like this
string sql = "SELECT imagepath FROM imagepathtable WHERE ID = @ID";
using (var conn = new MySqlConnection(cs))
{
    conn.Open();
    using (var cmd = new MySqlCommand(sql, conn))
    {
        cmd.Parameters.AddWithValue("@ID", myInt);
        pictureBox1.Image = new Bitmap(cmd.ExecuteScalar().ToString());
    }
}