How to save images in MySQL database using C# and VB.NET in Windows Forms application


Introduction

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

Description

Create a table in MySQL database using MySQL browser as following to store images

MySQLTable

For storing images you can use any of the BLOB (Binary Large Object) data types based on your storage requirements. We are using MEDIUMBLOB data type. Following are the storage capacity of different 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 like below

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

C# code:

 using MySql.Data.MySqlClient;
 using System.IO;

VB.NET code:

 Imports MySql.Data.MySqlClient
 Imports System.IO

Write following code in the Click event of PictureBox to select image to save in the database

C# code:

 private void pbStudentImage_Click(object sender, EventArgs e)
 {
      try
      {
           OpenFileDialog openFileDialog1 = new OpenFileDialog();
           openFileDialog1.Filter = "Image files | *.jpg";
           if (openFileDialog1.ShowDialog() == DialogResult.OK)
           {
                txtStudentImage.Text = openFileDialog1.FileName;
                pbStudentImage.Image = Image.FromFile(openFileDialog1.FileName);
           }
      }
      catch (Exception ex)
      {
           MessageBox.Show(ex.Message);
      }
 }

VB.NET code:

Private Sub pbStudentImage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles pbStudentImage.Click, txtStudentImage.Enter, txtStudentImage.Click
     Try
          Dim fileOpener As OpenFileDialog = New OpenFileDialog()
          fileOpener.Filter = "Image files | *.jpg"

          If fileOpener.ShowDialog() = Windows.Forms.DialogResult.OK Then
               pbStudentImage.Image = Image.FromFile(fileOpener.FileName)
               txtStudentImage.Text = fileOpener.FileName
          End If
     Catch ex As Exception
          MsgBox(ex.ToString())
     End Try
End Sub

Assign same event to the Click and Enter event of ‘txtStudentImage’ TextBox. Write following in the load event of form (Only in C#)

C# code:

 this.txtStudentImage.Click += new System.EventHandler(this.pbStudentImage_Click);
 this.txtStudentImage.Enter += new System.EventHandler(this.pbStudentImage_Click);

Write following code in the Click event of btnSave Button

C# code:

private void btnSaveImage_Click(object sender, EventArgs e)
{
     MySqlConnection con = new MySqlConnection(ConString);
     MySqlCommand cmd;
     FileStream fs;
     BinaryReader br;

     try
     {
          if (txtFirstName.Text.Length > 0 && txtStudentImage.Text.Length > 0)
          {
               string FileName = txtStudentImage.Text;
               byte[] ImageData;
               fs = new FileStream(FileName, FileMode.Open, FileAccess.Read);
               br = new BinaryReader(fs);
               ImageData = br.ReadBytes((int)fs.Length);
               br.Close();
               fs.Close();

               string CmdString = "INSERT INTO Students(FirstName, LastName, Image, Address) VALUES(@FirstName, @LastName, @Image, @Address)";
               cmd = new MySqlCommand(CmdString, con);

               cmd.Parameters.Add("@FirstName", MySqlDbType.VarChar, 45);
               cmd.Parameters.Add("@LastName", MySqlDbType.VarChar, 45);
               cmd.Parameters.Add("@Image", MySqlDbType.Blob);
               cmd.Parameters.Add("@Address", MySqlDbType.VarChar, 100);

               cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
               cmd.Parameters["@LastName"].Value = txtLastName.Text;
               cmd.Parameters["@Image"].Value = ImageData;
               cmd.Parameters["@Address"].Value = txtAddress.Text;

               con.Open();
               int RowsAffected = cmd.ExecuteNonQuery();
               if (RowsAffected > 0)
               {
                    MessageBox.Show("Image saved sucessfully!");
               }
               con.Close();
          }
          else
          {
               MessageBox.Show("Incomplete data!", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
     }
     catch(MySqlException mex)
     {
          MessageBox.Show(mex.ToString());
     }
     catch (Exception ex)
     {
          MessageBox.Show(ex.Message);
     }
     finally
     {
          if (con.State == ConnectionState.Open)
          {
               con.Close();
          }
     }
}

VB.NET code:

Private Sub btnSaveImage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveImage.Click
     Dim con As MySqlConnection = New MySqlConnection(ConString)
     Dim cmd As MySqlCommand
     Dim fs As FileStream
     Dim br As BinaryReader

     Try
          If txtFirstName.Text.Length > 0 And txtStudentImage.Text.Length > 0 Then
               Dim FileName As String = txtStudentImage.Text
               Dim ImageData() As Byte

               fs = New FileStream(FileName, FileMode.Open, FileAccess.Read)
               br = New BinaryReader(fs)
               ImageData = br.ReadBytes(CType(fs.Length, Integer))
               br.Close()
               fs.Close()

               Dim CmdString As String = "INSERT INTO Students(FirstName, LastName, Image, Address) VALUES(@FirstName, @LastName, @Image, @Address)"
               cmd = New MySqlCommand(CmdString, con)

               cmd.Parameters.Add("@FirstName", MySqlDbType.VarChar, 45)
               cmd.Parameters.Add("@LastName", MySqlDbType.VarChar, 45)
               cmd.Parameters.Add("@Image", MySqlDbType.Blob)
               cmd.Parameters.Add("@Address", MySqlDbType.VarChar, 100)

               cmd.Parameters("@FirstName").Value = txtFirstName.Text
               cmd.Parameters("@LastName").Value = txtLastName.Text
               cmd.Parameters("@Image").Value = ImageData
               cmd.Parameters("@Address").Value = txtAddress.Text
               con.Open()
               Dim RowsAffected As Integer = cmd.ExecuteNonQuery()
               If (RowsAffected > 0) Then
                    MsgBox("Image saved successfully!")
               End If
               con.Close()
          Else
               MsgBox("Incomplete data!", MsgBoxStyle.Critical, "")
          End If

     Catch ex As Exception
          MsgBox(ex.ToString())
     Finally
          If con.State = ConnectionState.Open Then
               con.Close()
          End If
     End Try
End Sub
About these ads
This entry was posted in .Net, MySQL, VB.NET, WinForm and tagged , , , , , , , , , , , , , , , , , . Bookmark the permalink.

6 Responses to How to save images in MySQL database using C# and VB.NET in Windows Forms application

  1. Google says:

    Everything is very open with a really clear explanation of
    the issues. It was definitely informative. Your website is extremely helpful.
    Thank you for sharing!

    Like

  2. Gana says:

    hey where is connect database code… sorry i’m beginner

    Like

  3. henry says:

    how can i display an image that i saved in a folder(images) on mysql server and saved its link in the database.

    Like

  4. marrisa says:

    Good job! Thank you for sharing. I’m just looking for code about saving images using vb.net. Helps a lot.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s