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.


EDIT: Code blocks added =>

This post has been edited by PsychoCoder: 6 May, 2008 - 05:07 AM

Is there a question in there somewhere?