chunksize.frm

A practice wirh database LOB objects in Csharp

                            ";User ID=" + connectionParams.username & _
                            ";Password=" + connectionParams.password
   
m_Oracon.Open 'establishing connection to the datasource
     
     
'open recordset with product_name, product_id column from product_information table
'using m_Oracon connection
'adopenKeyset cursor type used to allow all types of movements through the recordset
m_recProductInformation.Open "SELECT product_name, product_id " & _
                             " FROM product_information", m_Oracon, adOpenKeyset, _
                             adLockOptimistic, adCmdText
                             
'fetching records from recordset and populating the combobox
Do While Not m_recProductInformation.EOF
  
  'product_name as value displayed in the combobox
  cboSelectProduct.AddItem (m_recProductInformation!product_name)
    
  'product_id as actual item data in the combobox
  cboSelectProduct.ItemData(i) = (m_recProductInformation!product_id)
  m_recProductInformation.MoveNext
  i = i + 1
Loop
  
  
'resetting mouser pointer on the form "frmChunkSize" to normal state
frmChunkSize.MousePointer = vbNormal


If (Not Err.Number = 0) Then
ErrorText:   'error handler
    MsgBox "Source :" + Err.Source + " Description :" + Err.Description, , "Error"
End
End If
   
End Sub


'**************************************************************
' This sub procedure is used reset the option buttons, labels
' ,image displayed and the recordset cursor.
'**************************************************************

Private Sub cboSelectProduct_Click()

'resetting the captions for "Time Taken" and "No. of Round Trips" labels
lblTimeTaken.Caption = ""
lblRoundTrips.Caption = ""


'clearing the "imgProduct" imagebox
imgProduct.Picture = LoadPicture("")


'getting cursor position back to the first record
m_recProductInformation.MoveFirst

'navigate to the record in the recordset which has been selected from the combobox
m_recProductInformation.Move (cboSelectProduct.ListIndex)

End Sub
                                                      
                                                      
'**************************************************************
' This sub procedure is used to enable "Chunk Size" textbox
' and clearing the labels and imagebox
'**************************************************************

Private Sub optchunk_Click()
txtChunkSize.Enabled = True
lblRoundTrips.Caption = ""
lblTimeTaken.Caption = ""
imgProduct.Picture = LoadPicture("")
End Sub


'**************************************************************
' This sub procedure is used disable "Chunk Size" textbox
' and clearing the labels and imagebox
'**************************************************************

Private Sub optValue_Click()
txtChunkSize.Enabled = False
lblRoundTrips.Caption = ""
lblTimeTaken.Caption = ""
imgProduct.Picture = LoadPicture("")
End Sub
                                         
                                         
'***********************************************************************
' The objective of this sub procedure is to display the
' corresponding product_image for the product selected from the
' combobox.
'
' Following are the important steps followed in this sub procedure:

' Step 1. Validate whether product is selected from the list.
'
' Step 2. Validate that when optionbox for chunk size is selected then
'        "txtChunkSize" textbox should contain a positive value.
'
' Step 3  The recordset "recProductImage" is opened, for fetching
'         image of the selected product.
'
' Step 4. Since the product_image to be fetched from the database would
'         be in binary form, which cannot be directly loaded to the imagebox
'         so a buffer is formed to read the binary data temporarily.
'
' Step 5. Timer is started.
'
' Step 6. A check is made whether Value Property or ChunkSize property
'         option is selected.
'
' Step 7. In case Value Property is selected the byte array is assigned
'         the recordset field's value property. And the byte array is assigned
'         to a temporary file buffer.
'
' Step 8. In case ChunkSize property option is selected the actual
'         image size and no. of roundtrips is calculated.
'
' Step 9. Then image is retrieved in chunks of data and each chunk is
'         appended to the temporary file buffer.
'
' Step 10. Timer is stopped.
'
' Step 11. The imagebox is loaded with the temporary file.
'
' Step 12. After loading is done the temporary file is deleted.
'
' Step 13. The time taken for the image retrieval and no. of roundtrips
'          is also displayed.
'
' Step 14. The recordset "recProductImage" is closed.
'**************************************************************************

Private Sub cmdGetImage_Click()

Dim bytchunk() As Byte 'variable to store binary data
Dim destinationFileNum As Integer 'variable for filenumber

'recordset for fetching Product Image for the product selected form the list
Dim recProductImage As New ADODB.Recordset

Dim offset As Long
Dim totalsize As Long
Dim roundTrips As Long

'variables used in calculation of time taken to fetch the image
Dim startTime As Currency, EndTime As Currency, time As Currency, Freq As Currency

Dim i As Integer 'counter variable
i = 0


On Error GoTo ErrorText 'redirect to error handler


'** Step 1 **'
'validating if product is selected from the list
If cboSelectProduct.Text = "" Then
  MsgBox "Select product from the list!"
  Exit Sub
End If

'** Step 2  **'
'validating if "optChunk" optionbox is selected then
'"txtChunksize" textbox should contain a value
If optchunk.Value = True Then
     
   'validate if chunksize value is null
   If txtChunkSize.Text = "" Then
      MsgBox "Enter value for chunksize "
      Exit Sub
   End If
   
   'validating that the chunk size entered should be a positive value
   If CInt(txtChunkSize.Text) < 1 Then
     MsgBox "ChunkSize value should be positive!"
     Exit Sub
   End If
    
End If


   
   
'** Step 3 **'
'open image column from product_information table using m_Oracon connection
recProductImage.Open "SELECT product_image FROM product_information  " & _
                     " WHERE product_id =" & cboSelectProduct.ItemData(cboSelectProduct.ListIndex) _
                     , m_Oracon, adOpenStatic _
                     , adLockOptimistic, adCmdText
        

'check if product image exists for the product selected
If Not IsNull(recProductImage!product_image) Then

  'setting mouse pointer on the form "frmChunkSize" to wait state
  frmChunkSize.MousePointer = vbHourglass


  '** Step 4 **'
  'assigning "desitinationFileNum" variable to next file number
  'available for use
  destinationFileNum = FreeFile

  'allocates a buffer for I/O to the temporary file "tempImage.bmp"
  'at the current application path
  Open App.Path & "\tempImage.bmp" For Binary As destinationFileNum
  
  
  '** Step 5 **'
  'Get the frequency of internal timer in Freq variable
  QueryPerformanceFrequency Freq

  'start the timer
  QueryPerformanceCounter startTime
  
  
  'clear "imgProduct" imagebox
  imgProduct.Picture = LoadPicture("")
   
   
  '** Step 6 **
  If optValue.Value = True And optchunk.Value = False Then
  
    '** Step 7 **
    'using ADO Value property
    bytchunk = recProductImage("product_image").Value
    
    'appending byte arrary data to the temporary file
    Put destinationFileNum, , bytchunk
    
    'displaying "No. of Round Trips" in a label to 1
    lblRoundTrips = 1
    
  ElseIf optchunk.Value = True Then
    '** Step 8 **
    'converting the value entered "txtChunkSize" textbox to long
    'and assigning it to chunksize variable
    m_chunksize = CLng(txtChunkSize.Text)
  
    'assigning the actual size of the image retrieved to a variable
    totalsize = recProductImage("product_image").ActualSize
  
    'calculating and assigning the "No. of Round Trips" to a variable
    roundTrips = totalsize / m_chunksize
    
    'in case fragment of data left, incrementing roundtrips by 1
    If (totalsize Mod m_chunksize) > 0 Then
      roundTrips = roundTrips + 1
    End If
    
    'In this loop the image is retrieved in terms of chunksize
    'and appended to the temporary file
    Do While offset < totalsize
   
      '** Step 9 **
      'retrieving product_image from the recordset, in chunks of bytes
      bytchunk = recProductImage("product_image").GetChunk(m_chunksize)
     
      offset = offset + m_chunksize
     
      'appending byte arrary data to the temporary file
      Put destinationFileNum, , bytchunk
    Loop
    
  'displaying "No. of Round Trips" in a label
  lblRoundTrips = roundTrips

  End If
 
  '** Step 10 **'
  'stop the timer after image retrieval is done
  QueryPerformanceCounter EndTime


  'close the opened file handle
  Close destinationFileNum


  '** Step 11 **'
  'load the image stored in a temporary file "tempImage.bmp" to the "imgProduct" imagebox
  imgProduct.Picture = LoadPicture(App.Path & "\tempImage.bmp")

 
  '** Step 12 **'
  'delete the temporary file "tempImage.bmp" created at the current application path,
  Kill (App.Path & "\tempImage.bmp")
 
 
  '** Step 13 **'
  'Calulate and display the time taken to get data in a label
  time = ((EndTime - startTime) / Freq) * 1000
  lblTimeTaken.Caption = CStr(time) + "  Milliseconds"
     

  'setting mouse pointer on "frmChunkSize" form to normal state
  frmChunkSize.MousePointer = vbNormal
    
  'close and free memory held by the recordset
  'after the image for the selected product has been retrieved
  recProductImage.Close
  Set recProductImage = Nothing

Else

  'in case no image for a particular object exists in "Product_Information"
  'table, the "imgProduct" image box is loaded no image
  imgProduct.Picture = LoadPicture("")
  MsgBox "Image not found!"
End If


If (Not Err.Number = 0) Then
ErrorText:   'error handler
    MsgBox "Source :" + Err.Source + " Description :" + Err.Description, , "Error"
End
End If

End Sub


'**************************************************************
' This sub procedure is used to close recordset and connection
' and also frees the memory held by it
'**************************************************************

Private Sub Form_Unload(Cancel As Integer)

m_recProductInformation.Close
Set m_recProductInformation = Nothing

m_Oracon.Close
Set m_Oracon = Nothing

End Sub