Store/Retrieve binary data (BLOBs) from database [BLOBs

Store/Retrieve binary data (BLOBs) from database [BLOBs - Audio, Image, Video, etc.] (Database, VB.NET)

I always wanted a simple way to store binary data (images, audio, video, EXEs, XML files, etc.) in database and retrieve it when needed? Here, I have tried to present the simplest and perhaps the shortest way of doing so in VB.NET.

The Trick

In single line: 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 OLE Object data type.
2. The data we want to put/get into the database must be in form of a byte array.

To see how to do it exactly, please see the example below. In this example, we will store/retrieve images into database. Once, you get the feeling of how to do so, you can easily store/retrieve any kind of data in database.

Example

In this example, we have used an Access database with a 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 internally build SQL commands (like INSERT, UPDATE and DELETE) for the DataAdapter object for us. Therefore, we will have no need to write 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 this case, SELECT img FROM Table1).

For your convenience, I have tried to explain the flow diagrammatically below:

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. See the code below:

        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.

I recommend that you download the source code and see the whole thing running if you have any confusion. I am sure that will help you.

Part 6 - Saving array to file

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

I would like to mention one thing here. 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))

I hope this code is simple enough and will definitely help you. If you need to discuss anything related to this, please do not hesitate to contact me at rahul@coder000.com, I will be happy to discuss it further.

Happy coding!!