Working with Blobs and VB.NET

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:

Create BLOB Field in MS Access
Figure 1 – Create BLOB Field in MS Access

Create BLOB Field in SQL Server
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 🙁 )?

Access form
Figure 3 – Access form

SQL Server 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:

  1. Created a Byte Array object. This object will hold all the Bytes that make up the BLOB, in this case an image.
  2. I created a new MemoryStream object to read the file’s contents inside a Using structure.
  3. I set the PictureBox’s Image property to show the read image file.
  4. 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:

  1. I set the Parameter type to Image.
  2. 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!

Hannes DuPreez
Hannes DuPreez
Ockert J. du Preez is a passionate coder and always willing to learn. He has written hundreds of developer articles over the years detailing his programming quests and adventures. He has written the following books: Visual Studio 2019 In-Depth (BpB Publications) JavaScript for Gurus (BpB Publications) He was the Technical Editor for Professional C++, 5th Edition (Wiley) He was a Microsoft Most Valuable Professional for .NET (2008–2017).

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read