Saving images in MySQL Database from Visual Basic .NET 2010



I am just not sure enough about if this is a good or a bad idea to save images in DB. The most cases I have heard about said that it’s a bad idea and in very fewer articles I have read that “No, it’s same expensive.” But my suggestion would be that, if you have to pull only a single image from DB at a time, then you can use this method. But not in the else cases. In case of web development, there is not need to do this, because PHP is already having very easy procedure to save the image or any file in server’s directory and pulling them back and so as other server side languages.


Target: 

Target of this tutorial is to Store or Save images in MySQL Server Database from Visual Basic .NET 2010.

Assumption: 

I am assuming that you are familiar with the following
  1. Creating Database, Table in MySQL
  2. Adding data to MySQL database.
  3. Creating projects, saving projects, writing codes in VB.net.

Prerequisites: 

To achieve the target, you will have to have the following things,
  1. The MySQL Server
  2. The MySQL .NET Connector
  3. An IDE for DB Management. Such as SQLyog, PHPmyadmin etc. If you don’t have any, then you can download a free tool.
  4. Finally, Visual Basic .NET 2010 [ 2008 will also work ]

Once you have the above things, install all of them.

If you are not familiar with using MySQL Server Database with Visual Basic .NET then I recommend the blog post at the following link

Read Also: Using MySQL Database with Visual Basic .NET 2010

The Database: 

We have named our database as “my_test_projects” and the table that will have the image as “image_in_db”. The structure of the table “image_in_db” is as follows.

Column Name
Type
Description
id
int
This is not necessary, I have just used it, and I think you should to.
Image_data
mediumblob
This field actually holds the image. I am taking “mediumblob” as data type as it can hold nearly 4 MB of data, that is an image with nearly 4 MB of file size.

Okay, now open VB.net and create a new project and name it whatever you like and save it. Then, as described in this post “Using MySQL Database with Visual Basic .NET 2010” Add Reference of MySQL Connector to your project.

ContentMiddleAd

The Form: 

Design the form as follow,



Here, we have 4 controls. Description of the controls are given below.

Serial
Control
Name
Value
Usage
1
Picture Box
pic_box_save

It holds the image that will be saved in the DB. You can set an image by default as I did.
2
Button
btn_save
Save
The saving procedure is underneath this button.
3
Picture Box
pic_box_get

It holds the image after pulling that from the DB.
4
Button
btn_get
Get
The procedure to pull the image from the DB lies with this button.



Okay, so now we have the database and form ready, lets start coding in the next part.

PartDivider

First Phase - Creating connection and declaring variables:

First of all we have to import the MySQL Connector and need to declare some variables that we will use through the project.

Imports MySql.Data
Public Class Form1
     Dim sql As String
     Dim sql_connection As MySqlClient.MySqlConnection
     Dim sql_command As MySqlClient.MySqlCommand
     Dim sql_reader As MySqlClient.MySqlDataReader

     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          sql_connection = New MySqlClient.MySqlConnection("Data Source=localhost;user id=root;database=my_test_projects;")
          Try
               sql_connection.Open()
          Catch ex As Exception
               MsgBox("Error Creating DB Connection")
          End Try
     End Sub
End Class

ContentMiddleAd


Explanation of the above code is already given in this post “Using MySQL Database with Visual Basic .NET 2010”, please read that post if you need.

Great, so the connection is made, lets move on to the next part about saving the image in DB.

PartDivider

Second Phase - Saving the image in DB:

Double-Click the “btn_save” button to open the code editor and write the following code.

Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save.Click
 Dim FileSize As UInt32

 Dim mstream As New System.IO.MemoryStream()
 pic_box_save.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
 Dim arrImage() As Byte = mstream.GetBuffer()
 FileSize = mstream.Length
 mstream.Close()
 MsgBox(FileSize)
 Try
  sql = "INSERT INTO image_in_db(id, image_data) VALUES(@image_id, @image_data)"
  sql_command = New MySqlClient.MySqlCommand(sql, sql_connection)
  sql_command.Parameters.AddWithValue("@image_id", Nothing)
  sql_command.Parameters.AddWithValue("@image_data", arrImage)
  sql_command.ExecuteNonQuery()
 Catch ex As Exception
  MsgBox(ex.Message)
  Exit Sub
 End Try
 MsgBox("Image has been saved.")
End Sub

We will convert the image file to Byte type data. But before that, we will need to place the image file in memory-stream. That’s why in line 1 we have taken mstream as an instance of memory-stream. Then in the second line, we have saved the image of pic_box_save in mstream. Right after that, we have taken the stream in a Byte type array named as arrImage and then closed the stream on line 4.

ContentMiddleAd


That’s it and we are ready to save the image. In the Try…Catch block we have writen our SQL statement and then we made the SQL Command. After that, in line 8 and 9, we passes our data to the command. In line 8, we passed Nothing as the value of @image_id and then in line 9 we passed arrImage as the value of @image_data. Then in line 10 we have executed our SQL Command which actually saves the image in the DB. If this process fails for any reason then the code in line 12 and 13 will be executed, where we show the reason for the failure as a message and then exit the sub, respectively. But if this process is succeded then the code of line 15 will be executed which will show a message regarding the success of the saving process.

So far so good, the image has been saved to DB, but of no use unless we are able to pull it back for processing, right? Lets see how to do that in the next part.

PartDivider

Third Phase - Pulling the image back from the DB:

Double-Click the “btn_get” button to open the code editor and write the following code.

Private Sub btn_get_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_get.Click
 Dim arrImage() As Byte
 Try
  sql = "SELECT * FROM image_in_db WHERE id = '1'"
  sql_command = New MySqlClient.MySqlCommand(sql, sql_connection)
  sql_reader = sql_command.ExecuteReader()
  sql_reader.Read()
  arrImage = sql_reader.Item("image_data")
  Dim mstream As New System.IO.MemoryStream(arrImage)
  pic_box_get.Image = Image.FromStream(mstream)
  sql_reader.Close()
 Catch ex As Exception
  MsgBox(ex.Message)
  Exit Sub
 End Try
End Sub

ContentMiddleAd


To get the image back we just revert the image-saving procedure. In case of saving the image what we did is, Image in Picture Box > Memory Stream > Byte > Database. Here what we have done is the reverse process.

Line 4 to 7 : Pulling the image from the DB

Line 8: Getting the image in a Byte array named as arrImage.

Line 9: Getting the Byte array in memory-stream named as mstream.

Line 10: Putting the data from the memory-stream mstream in a picture box named as pic_box_get.

And that’s it. We are done. In the next part, we get the full source code along with the sample project to download to see things in action.

PartDivider


The Full Source Code:


Imports MySql.Data
Public Class Form1
    Dim sql As String
    Dim sql_connection As MySqlClient.MySqlConnection
    Dim sql_command As MySqlClient.MySqlCommand
    Dim sql_reader As MySqlClient.MySqlDataReader

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sql_connection = New MySqlClient.MySqlConnection("Data Source=localhost;user id=root;database=my_test_projects;")
        Try
            sql_connection.Open()
        Catch ex As Exception
            MsgBox("Error Creating DB Connection")
        End Try
    End Sub
    Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save.Click
        Dim FileSize As UInt32

        Dim mstream As New System.IO.MemoryStream()
        pic_box_save.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
        Dim arrImage() As Byte = mstream.GetBuffer()
        FileSize = mstream.Length
        mstream.Close()
        MsgBox(FileSize)
        Try
            sql = "INSERT INTO image_in_db(id, image_data) VALUES(@image_id, @image_data)"
            sql_command = New MySqlClient.MySqlCommand(sql, sql_connection)
            sql_command.Parameters.AddWithValue("@image_id", Nothing)
            sql_command.Parameters.AddWithValue("@image_data", arrImage)
            sql_command.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try

        MsgBox("Image has been saved.")

    End Sub

    Private Sub btn_get_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_get.Click
        Dim arrImage() As Byte

        Try
            sql = "SELECT * FROM image_in_db WHERE id = '1'"
            sql_command = New MySqlClient.MySqlCommand(sql, sql_connection)
            sql_reader = sql_command.ExecuteReader()
            sql_reader.Read()
            arrImage = sql_reader.Item("image_data")
            Dim mstream As New System.IO.MemoryStream(arrImage)
            pic_box_get.Image = Image.FromStream(mstream)
            sql_reader.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try
    End Sub
End Class


ContentMiddleAd


Download Full Project using the following download link:


Saving Images in MySQL DB


Next Read:

Recommended Recommends

Comments

Contact Us