You are here: HomeWallSayIt Loud2014 10 17Technology Saving Images inside a MySQL Database (Bajan)

Say It Loud

Technology - Saving Images inside a MySQL Database

2014-10-17 04:00:14

Technology - Saving Images inside a MySQL Database

What I love about programming is the fact that people pay me money for doing something I love doing. In fact the money comes a close second to the actual love of the work and the constant pleasure of learning something new.

This is a field where creativity and ingenuity take precedence over rules... for the most part you make your own rules because there are so many ways to choose how to do any one thing. It's similar to art, where the artist starts with a blank canvas and has full freedom to choose whatever shapes and colours he or she prefers when painting a picture. And pictures is exactly what this post is about.

I've been busy burning the midnight oil and learning something new. The objective was to have photos of people saved inside a MySQL database. The old version of the software used to display photos which were stored in a folder, with the file names being saved to match the employee codes stored in the database. It served its purpose of displaying the photos, but the images themselves were not stored in the database. Having shifted to my new toys of VB.NET, MySQL and FastReport.Net, I wanted to go the whole hog and save the images in the MySQL database. It took some trial and error but it was far easier than I ever imagined.

The first thing you need to do is have a picture box on the screen to display the photo, just like the old version used to do. Then you need two buttons: one to load a photo and another to remove the photo. the load button opens a file browser to select *.bmp, *.gif, *.jpg and *.png images. Selecting a photo displays it in the picture box. The remove button is used to clear the picture box when the current photo is no longer needed.

With the selection part done, the next step is getting the photo into the database. MySQL stores images as BLOB fields, with about four different types of BLOBs of varying file size capacity. I opened the database in MYSQL Workbench and modified the employees table to add a MEDIUMBLOB column called Photo. In future this will be done when the database is being built. Workbench also allowed me to browse my computer and add images to the records. I added only one for the first employee in the list.

With the photo saved for that employee, the next step as to display the saved image in the picture box when that employee's record was being retrieved in the program. It was very easy to do, using a technique of saving the database image to a memory steam and then transferring it to the picture box. Some additional code is required to check whether the employee has a photo or not, otherwise and error message appears from trying to display employees with no photos. The biggest challenge was setting the size of the picture box for the best fit. The picture box also a SizeMode option which is best set to Zoom so that images of various sizes an be properly displayed without corners being cut off.

The last part and most difficult part was saving the picture from the displayed picture into the database. That one was a bit technical and required some code which used a memory stream to transfer the image to the database. Just as with the display section, code had to be written to handle saving a blank photo. Once this last part was fine tuned, I was loading, saving, changing and removing images with ease. I also generated a report from the Employees table and it automatically included the photo in the list of records. This happened because I had added the MEDIUMBLOB field directly into the Employees table. Because there might be times when I don't want the image loaded, I'm considering creating a related table just to store images alone.

This is a very useful feature which adds some great new functionality to our software.

Morning beckons.

Nuff Respect, Bajan.

[This is an authentic posting from Bajan (Registered User)]
Your Comment:

Your Name: