Store-Retrieve binary data (BLOBs – Audio, Video, Image, Exe, etc) in database in VB Net

There are many scenarios in which we require to store some kind of binary data in the database. This data may be anything from an image, to audio, video, executables, xml files, multi lingual data files and so on. This discussion presents the simplest and perhaps the shortest way to do this in VB.NET.

The Trick

We use an array of bytes to get/put data into database’s OLE field.

There are two things to notice in the above statement:

1. The field in which we want to put/get binary data must be of Binary/OLE Object data type.
2. The data we want to put/get into the database must be in form of a byte array.

screenshot

Here is an example to store & retrieve images in a database. Once, you get the feeling of how to do so, you can easily store & retrieve any kind of data in the database.

The example uses a MS Access database with one table – Table1 which has an OLE Object field – img.

Part 1 – Global variable declaration

Dim strConn As String = ”Provider=microsoft.jet.oledb.4.0; data source=“ & Application.StartupPath & ”\..\db.mdb”
Dim da As New OleDb.OleDbDataAdapter(”select img from table1“, strConn)
Dim ds As New DataSet()
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim row As DataRow

DataAdapter (da): This is used to execute SQL commands at data source. In this example, we have used this to execute SELECT command on our database. We will also use this object to fill our dataset object (see Part 2)

DataSet (ds): This object is used to hold our table and perform operations on it.

CommandBuilder (cb): This object is used to build SQL commands (like INSERT, UPDATE and DELETE) for the DataAdapter object for us saving us from the need to SQL commands for these operations!

DataRow (row): This object is used to hold a row.

Part 2 – Filling the DataSet

da.Fill(ds)

The Fill method of DataAdapter is used to fill a given DataSet with the result of the specified SQL statement (in our example, SELECT img FROM Table1). See the figure below to get a clear idea about the communication with database.

store-binary-data-in-db

Part 3 – Reading the file into an array

Whatever you are trying to put in database must be in some file (in most of the cases). Our next step is to read the file into a byte array.

Dim arr() As Byte
‘Resize array so that it can accomodate the file
ReDim arr(FileLen(txtFileName.Text) - 1)
FileOpen(1, FileName, OpenMode.Binary, OpenAccess.Read, OpenShare.Shared)
FileGet(1, arr)
FileClose(1)

Line 1: Create array (check the data type – Byte)
Line 2: Comment
Line 3: Dynamically resize array to the same size as length of file
Line 4: Open the file for reading
Line 5: Read the file into array (just one line…)
Line 6: If you open a file, you MUST close it

Part 4 – Saving data (array) to database

row = ds.Tables(0).NewRow
row.Item(0) = arr
ds.Tables(0).Rows.Add(row)
da.Update(ds)

Line 1: Create a new row and assign it to row object variable
Line 2: Store array into the first column of the new row (you can specify the column name also)
Line 3: Add this row to the table
Line 4: Remember that that datasets are disconnected. Therefore, changes made in the dataset are not reflected in database automatically. To do it, we have called Update method of DataAdapter which updates the database using the CommandBuilder object which creates the appropriate SQL command for us (in this case, an INSERT statement).

Part 5 – Retrieving data into array

Dim arr() As Byte
arr = ds.Tables(0).Rows(recNo).Item(0)

Line 1: Create a byte array
Line 2: Get the data from the first column of row no. recNo (Integer variable – we can use an integer value also, like: .Rows(0), for the first row) of first table in the dataset.

You can go ahead, download and run the source code using the download link above. It will help to see it all running and to play a little with the code.

Part 6 – Saving array to file

FileOpen(1, "filename", OpenMode.Binary)
FilePut(1, arr)
FileClose(1)

If we are dealing with images and the data retrieved from database into array is an image, we can show that image into a PictureBox (picLoad) using the following line:

picLoad.Image = picLoad.Image.FromStream(New IO.MemoryStream(arr))