How to Save Images in MySQL Database Using C#

This article explains how to save images into a MySQL database in Windows Forms applications using C#. BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB.

In this article I will explain how to save images into a MySQL database in Windows Forms applications using C#.

Introduction

In this article first we will create a table in a MySQL database and then we will use a Windows Forms application to save an image into the database.

Description

Create a table in a MySQL database using MySQL browser as in the following. To store the image:

save-images-in-MySQL-database1.jpg

For storing images you can use any of the BLOB (Binary Large Object) data types based on your storage requirements. We are using the MEDIUMBLOB data type. The following are the storage capacities of various BLOB types in MySQL:

Data Type

 

Storage Capacity

 

BLOB

 

255 bytes

 

TINYBLOB

 

65,535 bytes (64 KB approx.)

 

MEDIUMBLOB

 

16,777,215 bytes (16 MB approx.)

 

LONGBLOB

 

4,294,967,295 bytes (4 GB approx.)

 

Create a new Windows Forms application and arrange controls on the form as in the following:

save-images-in-MySQL-database2.jpg

Add a reference to the MySql.Data dll using the Add Reference dialog box and include the following two namespaces:

  1. using MySql.Data.MySqlClient;  
  2. using System.IO; 
Write the following code in the Click event of PictureBox to select an image to save in the database:
  1. private void pbStudentImage_Click(object sender, EventArgs e)  
  2. {  
  3.     try  
  4.     {  
  5.         OpenFileDialog openFileDialog1 = new OpenFileDialog();  
  6.         openFileDialog1.Filter = "Image files | *.jpg";  
  7.         if (openFileDialog1.ShowDialog() == DialogResult.OK)  
  8.         {  
  9.             txtStudentImage.Text = openFileDialog1.FileName;  
  10.             pbStudentImage.Image = Image.FromFile(openFileDialog1.FileName);  
  11.         }  
  12.     }  
  13.     catch (Exception ex)  
  14.     {  
  15.         MessageBox.Show(ex.Message);  
  16.     }  
  17. }
Assign the same event to the Click and Enter events of the "txtStudentImage" TextBox , as in:
  1. this.txtStudentImage.Click += new System.EventHandler(this.pbStudentImage_Click);  
  2. this.txtStudentImage.Enter += new System.EventHandler(this.pbStudentImage_Click); 
Write the following code in the Click event of the btnSave Button:
  1. private void btnSaveImage_Click(object sender, EventArgs e)  
  2. {  
  3.     MySqlConnection con = new MySqlConnection(ConString);  
  4.     MySqlCommand cmd;  
  5.     FileStream fs;  
  6.     BinaryReader br;  
  7.     try  
  8.     {  
  9.         if (txtFirstName.Text.Length > 0 && txtStudentImage.Text.Length > 0)  
  10.         {  
  11.             string FileName = txtStudentImage.Text;  
  12.             byte[] ImageData;  
  13.             fs = new FileStream(FileName, FileMode.Open, FileAccess.Read);  
  14.             br = new BinaryReader(fs);  
  15.             ImageData = br.ReadBytes((int)fs.Length);  
  16.             br.Close();  
  17.             fs.Close();  
  18.             string CmdString = "INSERT INTO Students(FirstName, LastName, Image, Address) VALUES(@FirstName, @LastName, @Image, @Address)";  
  19.             cmd = new MySqlCommand(CmdString, con);  
  20.             cmd.Parameters.Add("@FirstName", MySqlDbType.VarChar, 45);  
  21.             cmd.Parameters.Add("@LastName", MySqlDbType.VarChar, 45);  
  22.             cmd.Parameters.Add("@Image", MySqlDbType.Blob);  
  23.             cmd.Parameters.Add("@Address", MySqlDbType.VarChar, 100);  
  24.             cmd.Parameters["@FirstName"].Value = txtFirstName.Text;  
  25.             cmd.Parameters["@LastName"].Value = txtLastName.Text;  
  26.             cmd.Parameters["@Image"].Value = ImageData;  
  27.             cmd.Parameters["@Address"].Value = txtAddress.Text;  
  28.             con.Open();  
  29.             int RowsAffected = cmd.ExecuteNonQuery();  
  30.             if (RowsAffected > 0)  
  31.             {  
  32.                 MessageBox.Show("Image saved sucessfully!");  
  33.             }  
  34.             con.Close();  
  35.         }  
  36.         else  
  37.         {  
  38.             MessageBox.Show("Incomplete data!""", MessageBoxButtons.OK, MessageBoxIcon.Error);  
  39.         }  
  40.     }  
  41.     catch (Exception ex)  
  42.     {  
  43.         MessageBox.Show(ex.Message);  
  44.     }  
  45.     finally  
  46.     {  
  47.         if (con.State == ConnectionState.Open)  
  48.         {  
  49.             con.Close();  
  50.         }  
  51.     }  
  52. }