You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q318639
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
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 thefollowing 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
Properties

Article ID: 318639 - Last Review: 01/03/2007 17:36:09 - Revision: 2.4

  • Microsoft SQL Server 2000 Windows CE Edition
  • Microsoft SQL Server 2000 Windows CE Edition 1.1
  • Microsoft SQL Server 2000 Windows CE Edition 1.1 Service Pack 1
  • kbgraphxlink kbhowto kbhowtomaster KB318639
Feedback