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

By | November 28, 2012

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

 

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 (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

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

  1. henry

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

    Reply
  2. Google

    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!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *