Click here to Skip to main content
13,194,103 members (49,160 online)
Click here to Skip to main content
Add your own
alternative version


66 bookmarked
Posted 31 Aug 2004

Database application programming for the Pocket PC using SQL CE

, 31 Aug 2004
Rate this:
Please Sign up or sign in to vote.
This demonstrates how to program a SQL CE application. It also shows a design pattern that can be easily modified and extended to create other database applications


This article follows up on Inheriting Data Access Layer functions and describes how a quick and structured method of building a Pocket PC 2003 database application.

Firstly create a Smart Device Application for a Pocket PC 2003. Following which you need to add references to System.Data.Common and System.Data.SQLServerCE

How to initialize the database

To create the database filled with some initial data firstly first check if the database file exists. If this file does not exist then the database may be created by the following steps:

1. Create the database itself

REM Create Database 

Dim SQLEngine As SqlServerCe.SqlCeEngine
SQLEngine = New SqlServerCe.SqlCeEngine("data source=" & DBPath)

To create a password-protected database using the SqlCeEngine.CreateDatabase method, you must specify the password property in the connection string. For example:

"data source=\secure.sdf;password=<myPassword>;encrypt database=TRUE"

2. Build an SQL command that will create the table that you want

REM Create an SQL command to create the database
SQL &= ",Name nvarchar(50) "
SQL &= ",Address ntext "
SQL &= ",Telephone ntext "
SQL &= ",Last_Edited datetime "
SQL &= ",IsPrivate_Flag nvarchar(10) "
SQL &= ")"

In the following table is an explanation of the data types used in SQL CE

Data Type



(national character(n))

Fixed-length Unicode data with a maximum length of 255 characters.

Default length = 1

Storage size, in bytes, is two times the number of characters entered.


(national character varying(n))

Variable-length Unicode data with a length of 1 to 255 characters.

Default length = 1

Storage size, in bytes, is two times the number of characters entered.


Variable-length Unicode data with a maximum length of (2^30 - 2) / 2 (536,870,911) characters. Storage size, in bytes, is two times the number of characters entered.


Fixed-length binary data with a maximum length of 510 bytes. Default length = 1


Variable-length binary data with a maximum length of 510 bytes. Default length = 1


Variable-length binary data with a maximum length of 2^30 – 1 (1,073,741,823) bytes.


A globally unique identifier (GUID). Storage size is 16 bytes.

IDENTITY [(s, i)]

s (seed) = starting value

i (increment) = increment value

This is a property of a data column, not a distinct data type.

Only data columns of the integer data types can be used for identity columns. A table can have only one identity column. A seed and increment can be specified and the column cannot be updated.


This is a property of a data column, not a distinct data type. It is a column in a table that is defined using the uniqueidentifier data type. A table can only have one ROWGUIDCOL column.

3. Execute the SQL command

Dim objConn As New SqlCeConnection

objConn.ConnectionString = ConnStr

Dim objCmd As SqlCeCommand = New SqlCeCommand(SQL, objConn)
objCmd.CommandType = CommandType.Text

The final step is to fill this table with data. To do this I have included Adr_Address.xml in my project. When deployed this file will find its way to the program directory. Which can be located with the following code:

StartupPath = "/Program Files/" + 

This file is read and inserted into the database using the ImportXML function in DBTable.vb.

CRUD Create, Read, Update and Delete with SQLCE

Create, Update and Delete functions can be made with SQL statements that do not return a value. These functions can therefore be made as described previously in steps 2 and 3.

To read from the database the following code may be used:

Dim objConn As New SqlCeConnection

objConn.ConnectionString = ConnStr

Dim SQL as string = "select * from adr_address"
Dim objCmd As New SqlCeDataAdapter(SQL, objConn)
Dim ds As DataSet = New DataSet

REM Fill Table held in ClassName in the Dataset ds
objCmd.Fill(ds, ClassName)

When building more interesting SQL statements the main differences between Access and SQL CE relevant to this example are as follows:

1. To use date time functions can use

In Access

TestDate = #01 MAR 2002 13:12:45#


TestDate (datetime,' 01 MAR 2002 13:12:45:000') 

2. SQL CE Does not have a Boolean type. This means you have to define one yourself. I prefer to use the strings ‘True’ and ‘False’, but they could just have easily defined as an integer or ‘T’ and ‘F’

In Access

TestBoolean = True


TestBoolean = ‘True’

3 Tier SQLCE Pocket PC example

In the example all this has been implemented using the inheritance method described in Inheriting Data Access Layer functions. This might not be everyone’s cup of tea, however I am very fond of this method because it has proven to be a very fast way of developing maintainable well structured applications. In the example I have also included XMLTable, which is analogous to DBTable. The idea is that I am using the same interface for reading XML files as for any other database, making it easy to change the database from XML to something else.

The ERD below gives an overview of how I have linked the classes in the example.

One last to remember is that the ‘x’ in the top left hand corner of the dialog box’s is not handled the same way in a Windows application. This is because it would otherwise be difficult to switch from one application to another. Therefore if you want to close a dialog you need to execute me.close.


This is a good starting point at making database applications. It demonstrates the basic problems of how to get a stand-alone database application to work. When I have some time I will make a follow up article how to synchronize data with data on other platforms.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
Switzerland Switzerland
I am a Software Engineer currently working in a Re Insurance company.

You may also be interested in...


Comments and Discussions

GeneralData transfer to remote mysql from sqlce Pin
nuhtufan9-Apr-10 4:21
membernuhtufan9-Apr-10 4:21 
GeneralOracle connection in windows CE Pin
farnaz9-May-08 21:19
memberfarnaz9-May-08 21:19 
Generalgetting Native error 25011 Pin
learner@set14-Jun-06 0:30
memberlearner@set14-Jun-06 0:30 
Generalsend Xml throgh web service Pin
Ahmedz_ce16-Apr-06 12:55
memberAhmedz_ce16-Apr-06 12:55 
QuestionCreating the output as an image Pin
studmat22-Feb-06 0:24
memberstudmat22-Feb-06 0:24 
Questionhello!! Pin
xavthebad16-Feb-06 5:34
memberxavthebad16-Feb-06 5:34 
Generalneed help!!pocket pc application Pin
xavthebad16-Feb-06 5:19
memberxavthebad16-Feb-06 5:19 
QuestionSynchronize using activesync Pin
Ariston Darmayuda8-Feb-06 15:26
memberAriston Darmayuda8-Feb-06 15:26 
AnswerRe: Synchronize using activesync Pin
rspiewak8-Mar-06 3:46
memberrspiewak8-Mar-06 3:46 
QuestionDatabase Issues Pin
cossy7415-Jan-06 16:33
membercossy7415-Jan-06 16:33 
AnswerRe: Database Issues Pin
Nigel-Findlater15-Jan-06 20:11
memberNigel-Findlater15-Jan-06 20:11 
GeneralTo move through the recordset Pin
siddams4-Jul-05 21:21
membersiddams4-Jul-05 21:21 
QuestionHow can I copy my database (*.sdf) to a memory card in a Pocket-ce ? Pin
claudiojose22-Apr-05 8:31
memberclaudiojose22-Apr-05 8:31 
GeneralAnticipating the follow up article Pin
kkgian4-Oct-04 5:39
memberkkgian4-Oct-04 5:39 
GeneralCode sample Pin
mathon5-Sep-04 23:47
membermathon5-Sep-04 23:47 
GeneralRe: Code sample Pin
Nigel-Findlater6-Sep-04 18:56
memberNigel-Findlater6-Sep-04 18:56 
GeneralRe: Code sample Pin
Nigel-Findlater13-Sep-04 20:15
memberNigel-Findlater13-Sep-04 20:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171018.2 | Last Updated 1 Sep 2004
Article Copyright 2004 by Nigel-Findlater
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid