HOW TO: Store and Retrieve an Image File with SQL Server CE and eVB (318639)

SUMMARY

This step-by-step article describes how to store an image file to a Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) database and how to retrieve the stored image from the database and reconstruct the image file. This article uses Microsoft Embedded Visual Basic as the development platform.

The sections that follow describe how to create an Embedded Visual Basic application that uses two command buttons. One command button stores a .jpg file to a SQL Server CE database. The second command button is for reconstructing the .jpg file from the image data stored in the SQL Server CE database.
back to the top

How to Store a JPG file to a SQL Server CE Database

1. Copy the MS.jpg file from the Program Files\Common Files\Microsoft Shared\Grphflt folder on your desktop computer to the CE device. If you cannot find this file, use any other .jpg file and be sure to replace the references in the code segment of the example with the appropriate name.
2. The following code segment describes how to store a .jpg file to a SQL Server CE database.
   
   NOTE: The code segment assumes you have a database named Image.sdf created on the CE device.
   1. Create a new Microsoft eMbedded Visual Basic application.
   2. Add a command button to the default form, and then paste the following code in for the command:

      
         Private Sub Command1_Click()
            Dim cn
            Dim rs
            Dim pixfld
      
            Const adcmdtabledirect = 512
            Const adlockoptimistic = 3
            Const adopenkeyset = 1
      
            Dim sfiledata
            
            Dim picfile As File
            Set picfile = CreateObject("FileCtl.File")
            
          picfile.Open "\MS.JPG", fsModeBinary
          sfiledata = picfile.Input(picfile.LOF)
          
          Set cn = CreateObject("ADOCE.Connection.3.1")
          Set rs = CreateObject("ADOCE.Recordset.3.1")
          cn.ConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\image.sdf"
          cn.Open
          
          ' Comment the following line the first time you run this sample.
          cn.Execute "drop table JpegTest"
             
          cn.Execute "create table JpegTest (ID int, Pix Image)"
              
          rs.Open "jpegTest", cn, adopenkeyset, adlockoptimistic, adcmdtabledirect
          
          Set pixfld = rs("pix")
          rs.AddNew
          rs.Fields("id") = 1
          
          pixfld.AppendChunk sfiledata
          
          picfile.Close
      
          rs.Update
          
          rs.Close
          cn.Close
          Set rs = Nothing
          Set cn = Nothing
          
          MsgBox "success storing image to database"
      End Sub 
      
      
      						

back to the top

How to Retrieve a JPG File from a SQL Server CE Database

The following code segment describes how to reconstruct a .jpg file from the image data stored in SQL Server CE.

NOTE: The code assumes that the image stored in the CE database is of type .jpg. You must know the type of the image (.jpg, .gif, .bmp, and so forth) to successfully reconstruct the file by using the following code sample. The code also assumes that the name and path of the reconstructed .jpg file is \MSRetrieved.jpg.

Add another command button to the default form, and then paste the following code:

Private Sub Command2_Click()
      Dim cn
      Dim rs
      
      Const adcmdtabledirect = 512
      Const adlockoptimistic = 3
      Const adopenkeyset = 1
      
      Dim picfile As File
      Set picfile = CreateObject("FileCtl.File")
        
    picfile.Open "\MSRetrieved.JPG", fsModeBinary, fsAccessWrite
    
    Set cn = CreateObject("ADOCE.Connection.3.1")
    Set rs = CreateObject("ADOCE.Recordset.3.1")
    
    cn.ConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\image.sdf"
    cn.Open
    
    rs.Open "JpegTest", cn, adopenkeyset, adlockoptimistic, adcmdtabledirect
    
    ' You may have to adjust the size passed to GetChunk.
    
    picfile.LinePrint rs.Fields("Pix").GetChunk(10000)
    picfile.Close
    
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    MsgBox "success loading image to file"
End Sub
				

back to the top

How to Add References to the Project

To add references to the project, from the Project menu, click References. Click to select references to:

• Microsoft CE ADO Control 3.1
  
• Microsoft CE ADO ext. 3.1 for DDL
  
• Microsoft CE File System Control 3.0
  
• Microsoft CE SQL Server Control 1.0

back to the top