In the world of software development, most of the time we are asked to display some descriptive images to end user at UI.  Generally, developers store the images inside the folders as image files and display them at the time of demand.  In this case we need to store the physical path only in the database and the image file keeps staying inside the folder.  This may reduce the database size, but at the same time, it leads to occupying a huge disk space for storing images and also makes the image files publicly accessible.

So let us have a look at the solution to it.  In the code snippet given below we will see how we can save the image files directly into the database omitting the intermediate disk/folder space. One can find many solutions in this scenario using .Net and SQL Server as the database where we can make use of Stored Procedures.  Here we have used Visual Basic .Net and MS Access (as database).  So we are left with no option for using Stored Procedures.  The required code snippets are given below.

·         Microsoft XP Professional

·         Visual Studio .NET 2003 or Visual Studio 2005

As we have discussed, the given code snippet will never keep the stored image in the folder/disk space.  We will also see both the read and write operations here.

Listing 1

Dim img As Image
Private Sub imgFile_FileOk(ByVal sender As System.Object, _
                           ByVal e As System.ComponentModel.CancelEventArgs) _
                           Handles imgFile.FileOk
  Try
  img = Image.FromFile(imgFile.FileName)
 
  btnSave.Enabled = True
  picBox.Image = img
 
  Catch ex As Exception
  If InStr(UCase(ex.Message), UCase("Out of memory")) Then
    MsgBox("Please select images only", _
           MsgBoxStyle.OKOnly, "Error Occured")
  End If
  End Try
End Sub

Analysis

The code snippet given above only displays the image selected from the Dialog box in the picture box at UI.  The code is written inside the Try-Catch Block and, therefore, it also ensures the selection of image files only.

Now let us have a look on the gist.  The code given below is actually inserting the image into the database in binary format.

Listing 2

Private Sub btnSave_Click(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) Handles btnSave.Click
  Dim oConn As OleDb.OleDbConnection
  Dim oComm As OleDb.OleDbCommand
  Dim Err As String
  Dim sInsertQuery As String
  Dim FSO As New System.IO.FileStream(imgFile.FileName, _
                                      IO.FileMode.Open, IO.FileAccess.Read)
  Try
  sInsertQuery = "insert into images([image]) VALUES(?)"
 
  oConn = New OleDb.OleDbConnection( _
          New Connection().getConnectionString)
  oConn.Open()
  oComm = New OleDb.OleDbCommand(sInsertQuery, oConn)
  Dim imgArrayByte(CType(FSO.Length() - 1, Integer)) As Byte
  FSO.Read(imgArrayByte, 0, imgArrayByte.Length)
  FSO.Close()
 
  Dim QueryParameter As New OleDb.OleDbParameter("@Picture", _
                                                 OleDb.OleDbType.LongVarBinary, _
                                                 imgArrayByte.Length, ParameterDirection.Input, _
                                                 False, 0, 0, Nothing, DataRowVersion.Current, _
                                                 imgArrayByte)
  oComm.Parameters.Add(QueryParameter)
  oComm.ExecuteNonQuery()
  MsgBox("Image is saved successfully to the Database", _
         MsgBoxStyle.OKOnly, _
         "Successfully Saved")
  getListOfImages()
  btnSave.Enabled = False
  Catch Ex As Exception
  Err = Ex.Message
  MsgBox("Error : " & Err, _
         MsgBoxStyle.OKOnly, "Error Occured")
  Finally
  oConn = Nothing
  oComm = Nothing
  End Try
End Sub

Analysis

The code given above is actually inserting the images to the Database.  The above snippet needs a bit of clarification on some points, which are given below.

One can see the variable sInsertQuery is initialized as “insert into images([image]) VALUES(?).”  The reason is that we can not write the complete query here.  We need to add a parameter, which will hold the image in binary format, and this can only be done after converting the image to binary.  (Obviously, appending a parameter to the query cannot do this.)

For this reason we have used a placeholder [?] in the query which is replaced by the "QueryParameter" afterwards.

Creating a new OleDB Parameter requires an array of bytes.  For that reason we have used a File System Object and created the array of bytes out of it.

After successfully creating the parameter, it has replaced the placeholder [?] in the query and the query gets executed.

Until now we were looking into the codes, which is actually responsible for writing the images to the database.  Now, let us look into the section which is reading the images from the database.

Listing 3

Private Sub cmbImgIds_SelectedIndexChanged(ByVal sender As System.Object, _
                                           ByVal e As System.EventArgs) Handles cmbImgIds.SelectedIndexChanged
  btnDelete.Enabled = False
  Dim sQuery As New System.Text.StringBuilder()
  Dim oDataTable As New DataTable()
  Dim dtRow As DataRow
  Dim imagedata() As Byte
  Dim imageBytedata As MemoryStream
  sQuery.Append("SELECT image FROM images WHERE imageId = " & _
                cmbImgIds.SelectedItem)
  oDataTable = New Connection().doDBconnection(sQuery.ToString, "Image")
 
  For Each dtRow In oDataTable.Rows
    btnDelete.Enabled = True
    imagedata = dtRow.Item("image")
    imageBytedata = New MemoryStream(imagedata)
  Next
  picBox.Image = Image.FromStream(imageBytedata)
End Sub

Analysis

The above lines of codes are actually reading the image from the database in binary format and then displaying it in the picture box at UI.  A few points from the above lines of code are discussed below.

One needs to import the System.IO in order to access MemoryStream.

The Connection() is a user defined class that contains the public function doDBconnection().  The public function takes the Query and a proposed datatable name as input.  After executing the query it returns the results filled in a datatable with the proposed name.

[Executable file]

We have seen how the images can be stored in the database in binary formats without keeping them in disk spaces as we used to do traditionally.  But of course, pros and cons are always there.  One cannot say that this method of storing images in a database is completely acceptable, but, yes, it is a different way of storing which can lead to the reduced use of disk space and keep the images safely restricting the public use of it.