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
- Creating Database, Table in MySQL
- Adding data to MySQL database.
- Creating projects, saving projects, writing codes in VB.net.
Prerequisites:
To achieve the target, you will have to have the following things,
-
The MySQL Server
-
The MySQL .NET Connector
-
An IDE for DB Management. Such as SQLyog, PHPmyadmin etc. If you don’t have any, then you can download a free tool.
- 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.
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.
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
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.
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.
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.
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
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.
The Full Source Code:
Download Full Project using the following download link:
Saving Images in MySQL DB