How to store image to MySQL Database using VB.Net

There are many people, VB and MySQL Master that does not agree with this way to store image into MySQL Server. Apart from that, I just want to make a note for myself how to store image into MySQL Server from VB.Net. The image is saved as Blob field type. This could be Blob, Mediumblob or Longblob. The other way, we can save the file path of the image on a computer and then this file path will be saved to the MySQL table. I will discuss and write about this on the next post. For now, I will show how to store image to MySQL Table from VB.Net.

Before we go through, we need to make a table with a Blob field type. For example, here is my tbl_test design:

As you can see, my tbl_test contains two fields. ID and Image_File with LONGBLOB datatype. To store images to MySQL, you must have at least one field with BLOB datatype. The table content when filled with images will looks like this:

I am using MySQL Workbench to display the table properties above. You may use any program to create or alter your MySQL tables.

Now, its start to switch to Microsoft Visual Studio

Design the interface. On the example below, I have 4 main controls. Picturebox and three buttons.

The idea is to browse the image first, and display it on the Picturebox1 and then save the image to MySQL using Save button.

Browse button Code:

    Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
        Dim openfiledialog1 As New OpenFileDialog
        Try
            With openfiledialog1
                .InitialDirectory = "C:\Users\Lenovo\Pictures"
                .Filter = "Image File (*.jpg)|*.jpg|(*.png)|*png"
            End With
            If openfiledialog1.ShowDialog = DialogResult.OK Then
                Dim namafile As String = openfiledialog1.FileName
                PictureBox1.SizeMode = PictureBoxSizeMode.Zoom
                PictureBox1.Image = Image.FromFile(namafile)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

The Browse button will get the image file path and then passed to the Picturebox1 to be displayed as image. When this program is running, it will looks like this:

Save button code:

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Try
            If IsNothing(PictureBox1.Image) Then
                MsgBox("Please load image first")
            Else
                Dim filesize As UInt32
                Dim mstream As New System.IO.MemoryStream()
                PictureBox1.Image.Save(mstream, Imaging.ImageFormat.Jpeg)
                Dim arrImage() As Byte = mstream.GetBuffer()
                filesize = mstream.Length
                mstream.Close()
                MsgBox(filesize)
                'Insert to MySQL
                Dim sql As String
                sql = "Insert into tbl_test(id, image_file) VALUES (Null,@Image_File)"
                Dim sqlcommand As New MySqlCommand(sql, conn)
                sqlcommand.Parameters.AddWithValue("@Image_File", arrImage)
                sqlcommand.ExecuteNonQuery()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Please note that on the code above, you won’t find any MySQL connection string etc., because I have created MySQL Connection module. So this module can be accessed from any form on my project without having to type the MySQL connection code repetitively. Here is my module. I suggest you to create one:

Imports MySql.Data.MySqlClient
Module MySQLConnect
    Public conn As New MySql.Data.MySqlClient.MySqlConnection
    Public Sub Connect()
        Dim DatabaseName As String = "my_asset"
        Dim Server As String = "10.34.0.10"
        Dim UserName As String = "dhani"
        Dim Password As String = "12345678"
        If Not conn Is Nothing Then conn.Close()
        conn.ConnectionString = String.Format("server={0}; user id={1}; password={2}; database={3}; pooling=false; Convert Zero Datetime=True", Server, UserName, Password, DatabaseName)
        Try
            conn.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Module

Now all should work perfect. The only problem I have is the file size limitation that can be saved to MySQL. Maximum file that is supported by BLOB datatype can be read on this official explanation on MySQL website.