Introduction
Welcome to today’s article! Today I will demonstrate how to save and retrieve BLOBS from a database with Visual Basic.NET.
BLOBS
Do not get confused. This is not a character from the movie called The Blob. The term Blob means: Binary Large OBject. The keyword that should tell you everything is Binary. A Blob refers to any image or file that is stored inside a database. Large Object would refer to its size. Now you will say some files are small, and I will agree with you, but, files and images are still much bigger than a piece of data that is stored in a database table. This is why it is called a Binary Large Object. For more information regarding BLOBs, have a look here.
Storing and Retrieving Blobs
The whole process of storing and retrieving blobs in a database works a little bit differently than just storing and retrieving ordinary data in a database. You have to remember that you are ultimately working with a file, whether you retrieve it or store it. You have to make use of methods that are capable of reading and writing file content first before storing it to the database or read from the database. If you do not read the file’s content, the object will not be displayed or stored.
Creating the BLOB Field in the Database(s)
In order for a database field to store any sort of BLOB, you need to ensure you create the database field with the correct data type. If you haven’t followed any of my database articles, you will not have access to the Students database. Have a look here at how to create the initial database(s) in either MS Access 2010 or SQL Server.
Add the following field to the StudentInfodatabase table:
Figure 1 – Create BLOB Field in MS Access
Figure 2 – Create BLOB Field in SQL Server
Let us do a project!
Our Project
The little project you will build today makes use of Visual Studio 2012. Open it and create a new VB.NET Windows Forms application. Once done, make sure you have two forms and design them to resemble both Figure 1 and Figure 2. I will demonstrate how to store and read blobs from an Access 2010 database table as well as from an SQL Server database table. Some of the features I will demonstrate today have already been covered in my previous article on Parameterized Queries. If you haven’t read it yet ( why not 🙁 )?
Figure 3 – Access form
Figure 4 – SQL Server form
You will also make use of an OpenFileDialogto Browse for an Image file to store in the Database table, so make sure to add it to your design as well.
Coding
As usual, let me get the necessary Namespaces out of the way. Add the following Importsstatements on each form respectively:
Access
Imports System.Data.OleDb 'Import Access Db Handling Capabilities
SQL Server
Imports System.Data.SqlClient 'Import SQL Server Db Handling Capabilities
Add the following modularvariables to each respective form:
Access
'Access Database Connection String Dim strAccConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\HannesTheGreat\Documents\Students.accdb;Persist Security Info=False;" 'Object To Use As SQL Query Dim strAccQuery As String 'Access Database Connection Dim oleAccCon As OleDbConnection 'Store Image Path Dim strImagePath As string 'Image To Store PictureBox Image Dim imgTemp As Image
SQL Server
'SQL Server Database Connection String Dim strSQLConn As String = "Data Source=HANNES;Initial Catalog=Students;Integrated Security=True" 'Object To Use As SQL Query Dim strQuery As String 'SQL Server Database Connection Dim sqlSQLCon As SqlConnection 'Store Image Path Dim strImagePath As string 'Image To Store PictureBox Image Dim imgTemp As Image
These variables will be used during the entire program. I created the Connection string variables to each associated database (Access / SQL Server). I then created a query string object to host the queries I will submit to the database. The last two variables will be used to keep track of where the chosen imagehas been selected from, as well as an Image object to later draw the chosen, or retrieved image.
Add the Search button’scode:
Access
'Search Button Code Private Sub btnSearch_Click( sender As Object, e As EventArgs) Handles btnSearch.Click 'Search SQL Query, Including a Parameter Called Name 'This Query Simply Retrieves All Information That Matches Our Criteria strAccQuery = "Select * From StudentInfo where StudentName = @Name" 'Instantiate Connection Object oleAccCon = New OleDbConnection(strAccConn) 'Using Structure Simplifies Garbage Collection And Ensures That The Object Will Be Disposed Of Correctly Afterwards Using oleAccCon 'Create Command Object, To Make Use Of SQL Query Dim oleAccCommand As New OleDbCommand(strAccQuery, oleAccCon) 'Create Parameter Instead Of Hardcoding Values 'Name = Whatever txtSearch Contains oleAccCommand.Parameters.AddWithValue("Name", txtSearch.Text) 'Open Connection To The Database oleAccCon.Open() 'Reader Object To Traverse Through Found Records Dim oleAccReader As OleDbDataReader = oleAccCommand.ExecuteReader() 'If The Reader Finds Rows If oleAccReader.HasRows Then 'Retrieve The Content, For Each Match While oleAccReader.Read() 'GetString(1) Represents Column 2 Of StudentsInfo table txtName.Text = oleAccReader.GetString(1) 'GetString(2) Gets Information Stored In Third Column txtSurname.Text = oleAccReader.GetString(2) 'Use GetValue or GetInt32 Here, Because StudentNumber Is A Number Field txtStudentNumber.Text = oleAccReader.GetValue(0) Dim bImage As Byte() = CType(oleAccReader("StudentPicture"), Byte()) Using ms As New IO.MemoryStream(bImage) picImage.Image = Image.FromStream(ms) picImage.SizeMode = PictureBoxSizeMode.StretchImage End Using End While Else 'No Match Was Found MessageBox.Show("No Rows Found.") End If 'Close Reader Object oleAccReader.Close() End Using End Sub
SQL Server
Private Sub btnSearch_Click( sender As Object, e As EventArgs) Handles btnSearch.Click 'Search SQL Query, Including a Parameter Called Name 'This Query Simply Retrieves All Information That Matches Our Criteria strQuery = "Select * From StudentInfo where StudentName = @Name" 'Instantiate Connection Object sqlSQLCon = New SqlConnection(strSQLConn) 'Using Structure Simplifies Garbage Collection And Ensures That The Object Will Be Disposed Of Correctly Afterwards Using sqlSQLCon 'Create Command Object, To Make Use Of SQL Query Dim sqlSQLCommand As New SqlCommand(strQuery, sqlSQLCon) 'Create Parameter Instead Of Hardcoding Values 'Name = Whatever txtSearch Contains sqlSQLCommand.Parameters.AddWithValue("Name", txtSearch.Text) 'Open Connection To The Database sqlSQLCon.Open() 'Reader Object To Traverse Through Found Records Dim sqlSQLReader As SqlDataReader = sqlSQLCommand.ExecuteReader() 'If The Reader Finds Rows If sqlSQLReader.HasRows Then 'Retrieve The Content, For Each Match While sqlSQLReader.Read() 'GetString(0) Represents Column 1 Of StudentsInfo table txtname.Text = sqlSQLReader.GetString(1) 'GetString(1) Gets Information Stored In Second Column txtSurname.Text = sqlSQLReader.GetString(2) 'Use GetValue or GetInt32 Here, Because StudentNumber Is A Number Field txtStudentNumber.Text = sqlSQLReader.GetValue(0) Dim bImage As Byte() = CType(sqlSQLReader("StudentPicture"), Byte()) Using ms As New IO.MemoryStream(bImage) picImage.Image = Image.FromStream(ms) picImage.SizeMode = PictureBoxSizeMode.StretchImage End Using End While Else 'No Match Was Found MessageBox.Show("No Rows Found.") End If 'Close Reader Object sqlSQLReader.Close() End Using End Sub
As mentioned earlier, most of the above code is borrowed from an earlier article I wrote, entitled : Parameterized Queries, so please, if you haven’t read it, do so now before continuing. As the name of the button implies, it searches for records in the database. In order to achieve the searching, I set up my strQueryvariable to select all the data from the specified table. I created a Command object to host and execute the query.
I also made use of parameters inside this query and gave them all values. If you have read my previous article, you will notice no difference apart from the fact that I brought in the Image handling capabilities. To read the image present in the StudentInfoTable I did the following:
- Created a Byte Array object. This object will hold all the Bytes that make up the BLOB, in this case an image.
- I created a new MemoryStream object to read the file’s contents inside a Using structure.
- I set the PictureBox’s Image property to show the read image file.
- Ensured that the Picturebox shows a thumbnail of the image.
Add the following code to allow the user to select an image he/she would like to store in the database table:
Access
Private Sub btnBrowse_Click( sender As Object, e As EventArgs) Handles btnBrowse.Click ofdOpen.ShowDialog() strImagePath = ofdOpen.FileName imgTemp = Image.FromFile(ofdOpen.FileName) picImage.SizeMode = PictureBoxSizeMode.StretchImage picImage.Image = imgTemp End Sub
SQL Server
Private Sub btnBrowse_Click( sender As Object, e As EventArgs) Handles btnBrowse.Click ofdOpen.ShowDialog() strImagePath = ofdOpen.FileName imgTemp = Image.FromFile(ofdOpen.FileName) picImage.SizeMode = PictureBoxSizeMode.StretchImage picImage.Image = imgTemp End Sub
This button’s code is actually pretty simple. I show the OpenFileDialog to allow the user to select a file. I obtain the Filename of the chosen image, then open it and display it inside the PictureBox.
Now, let us add the last piece of code to adda BLOB to a database table. Add the following code:
Access
Private Sub btnAdd_Click( sender As Object, e As EventArgs) Handles btnAdd.Click If strImagePath <> "" Then Dim strImageFinal As String strImageFinal = strImagePath While (strImageFinal.Contains("\")) strImageFinal = strImageFinal.Remove(0, strImageFinal.IndexOf("\") + 1) End While Dim msImage As New IO.MemoryStream If strImagePath.Contains("jpeg") Or strImagePath.Contains("jpg") Then imgTemp.Save(msImage, System.Drawing.Imaging.ImageFormat.Jpeg) End If If strImagePath.Contains("png") Then imgTemp.Save(msImage, System.Drawing.Imaging.ImageFormat.Png) End If If strImagePath.Contains("gif") Then imgTemp.Save(msImage, System.Drawing.Imaging.ImageFormat.Gif) End If If strImagePath.Contains("bmp") Then imgTemp.Save(msImage, System.Drawing.Imaging.ImageFormat.Bmp) End If Dim bImage() As Byte = msImage.ToArray() 'INSERT SQL Query 'This Query Inserts Our Input Data Into The Table strAccQuery = "Insert Into StudentInfo (StudentName, StudentSurname, StudentNumber, StudentPicture) Values (@Name, @Surname, @StudentNo, @StudentImg)" 'Instantiate Connection Object oleAccCon = New OleDbConnection(strAccConn) 'Using Structure Simplifies Garbage Collection And Ensures That The Object Will Be Disposed Of Correctly Afterwards Using oleAccCon 'Create Command Object, To Make Use Of SQL Query Dim oleAccCommand As New OleDbCommand(strAccQuery, oleAccCon) 'Create Parameters Instead Of Hardcoding Values 'Name = Whatever txtName Contains 'Surname = Whatever txtSurname Contains 'StudentNo = txtStudentNumber Text oleAccCommand.Parameters.AddWithValue("Name", txtName.Text) oleAccCommand.Parameters.AddWithValue("Surname", txtSurname.Text) oleAccCommand.Parameters.AddWithValue("StudentNo", txtStudentNumber.Text) oleAccCommand.Parameters.Add("@StudentImg", OleDbType.VarBinary, bImage.Length).Value = bImage 'Open Connection To The Database oleAccCon.Open() 'Execute Command As NonQuery As It Doesn't Return Info oleAccCommand.ExecuteNonQuery() 'Inform User That Row Has Been Added MessageBox.Show("Added") End Using End If End Sub
SQL Server
Private Sub btnAdd_Click( sender As Object, e As EventArgs) Handles btnAdd.Click If strImagePath <> "" Then Dim strImageFinal As String strImageFinal = strImagePath While (strImageFinal.Contains("\")) strImageFinal = strImageFinal.Remove(0, strImageFinal.IndexOf("\") + 1) End While Dim msImage As New IO.MemoryStream If strImagePath.Contains("jpeg") Or strImagePath.Contains("jpg") Then imgTemp.Save(msImage, System.Drawing.Imaging.ImageFormat.Jpeg) End If If strImagePath.Contains("png") Then imgTemp.Save(msImage, System.Drawing.Imaging.ImageFormat.Png) End If If strImagePath.Contains("gif") Then imgTemp.Save(msImage, System.Drawing.Imaging.ImageFormat.Gif) End If If strImagePath.Contains("bmp") Then imgTemp.Save(msImage, System.Drawing.Imaging.ImageFormat.Bmp) End If Dim bImage() As Byte = msImage.ToArray() 'INSERT SQL Query 'This Query Inserts Our Input Data Into The Table strQuery = "Insert Into StudentInfo (StudentName, StudentSurname, StudentNumber, StudentPicture) Values (@Name, @Surname, @StudentNo, @StudentImg)" 'Instantiate Connection Object sqlSQLCon = New SqlConnection(strSQLConn) 'Using Structure Simplifies Garbage Collection And Ensures That The Object Will Be Disposed Of Correctly Afterwards Using sqlSQLCon 'Create Command Object, To Make Use Of SQL Query Dim sqlSQLCommand As New SqlCommand(strQuery, sqlSQLCon) 'Create Parameters Instead Of Hardcoding Values 'Name = Whatever txtName Contains 'Surname = Whatever txtSurname Contains 'StudentNo = txtStudentNumber Text sqlSQLCommand.Parameters.AddWithValue("Name", txtName.Text) sqlSQLCommand.Parameters.AddWithValue("Surname", txtSurname.Text) sqlSQLCommand.Parameters.AddWithValue("StudentNo", txtStudentNumber.Text) sqlSQLCommand.Parameters.Add("@StudentImg", SqlDbType.Image, bImage.Length).Value = bImage 'Open Connection To The Database sqlSQLCon.Open() 'Execute Command As NonQuery As It Doesn't Return Info sqlSQLCommand.ExecuteNonQuery() 'Inform User That Row Has Been Added MessageBox.Show("Added") End Using End If End Sub
Here, I first did a bit of semantics. I identified the chosen file, and then identified its file extension. The reason for identifying its file extension is so that I can save the image properly, in the correct format inside the database table. I also manipulated the filename not to include all the directories, just the file. Once I have done that and have the correct file extension, I save the image to the Imagestream named msImage.
Then, I created a Byte object to host the data that was written to the ImageStream. The following code is to set up the parameters and supply the correct values to them, before submitting the data top the database. Look closely what I did with the Parameter named StudentImg:
- I set the Parameter type to Image.
- I supplied the Byte array as the value.
When the query has run, the chosen image will be stored inside the database. Although I have made use of an Image object here, the very same logic can be applied to anyfile.
Conclusion
As you can see, it is very easy to store Binary Large Objects inside database tables. I hope you have enjoyed this article and that you have learned from it. Until next time, cheers!