将图像加载到 C#，然后插入到 MySQL 表中 [英] Load an image to C# and then inserting into MySQL table

问题描述

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

Here's what I need: I'm making a software for inventory control, but I have to upload an image of the product among the data. I'm working with C# and MySQL.

我不知道如何将加载到pictureBox中的图像放到MySQL表中.有任何想法吗?我完全迷失在这里.

I do not know how to put the image loaded into the pictureBox to a MySQL table. Any ideas? I'm completely lost here.

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

I loaded the image into a pictureBox using this code:

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 表中?我想表中的图像属性应该是:

How do I insert the loaded image into a MySQL table? I suppose that the image attribute in the table should be:

`img` LONGBLOB NOT NULL

然后，我如何将图像从 MySQL 回调到一个图片框?我想(再次)查询是这样的:

And then, how do I call back the image to a pictureBox from MySQL? I suppose (again) that the query it is something like:

select img from table_name where id = '';

最后，当我有查询时，如何将图像从 MySQL 加载到图片框?

Finally, when I have the query, how do I load the image to a pictureBox from MySQL?

非常感谢.

推荐答案

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

I will offer two solutions. The first solution is to store the raw image in bytes in the database directly. The second solution is what I personally recommend - which is to instead use the path of the image file in the database instead.

这里摘录了一篇文章，其中提出了一些关于是否为到 BLOB.

Here an excerpt from an article which brings up some excellent points on whether or not to BLOB.

嗯，不应该存储二进制数据有几个原因在您的数据库中:

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

• 在 SQL 数据库中存储数据的全部意义在于对数据进行某种排序和结构化，并且能够搜索这些数据.但是你如何在一个二进制数据中搜索图片?

• 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:

• 复制.将图像存储在数据库中可以将您的所有数据集中存储，这样更便携，更容易复制.

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

Here is how you would go about choosing your image file:

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);
      }
   }
}

解决方案 2:在文件系统上存储文件路径

// 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());
    }
}

这篇关于将图像加载到 C#，然后插入到 MySQL 表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！