Load an image to C# and then inserting into MySQL table

Question

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.

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

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

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 = '';

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

Thank you very much.

Answer 1

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.

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:

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

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

Solution 1: BLOB approach

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

Solution 2: Storing Path of File on Filesystem

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