Saving images in MySQL Database from Visual Basic .NET 2010

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
     Download Directly From Here
     Click Here to Choose Your Download

2.    The MySQL .NET Connector
     Download Directly From Here
     Click Here to Choose Your Download

3.    An IDE for DB Management. Such as SQLyog, PHPmyadmin etc. If you don’t have any, then you can download a free tool.
     Click Here to Download MySQL Query Browser

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

          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.

Ok, 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.

Ok so now we are set to start coding.

First Phase: Creating connection and declaring variables.

  

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.

Second Phase: Saving the image in DB.

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

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.

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.

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 3 to 6 : Pulling the image from the DB

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

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

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

And that’s it. We are done.

Note: 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.

The Full Source Code: