Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Database application programming for the Pocket PC using SQL CE

0.00/5 (No votes)
31 Aug 2004 1  
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

Introduction

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)
SQLEngine.CreateDatabase()

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 = "CREATE TABLE ADR_ADDRESS ( "
SQL &= "Address_ID INT IDENTITY(1,1) PRIMARY KEY "
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

Description

nchar(n)

(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.

nvarchar(n)

(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.

Ntext

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.

binary(n)

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

Varbinary(n)

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

Image

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

Uniqueidentifier

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.

ROWGUIDCOL

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
objConn.Open()

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

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/" + 
  System.Reflection.Assembly.GetExecutingAssembly.GetName.Name

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
objConn.Open()

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)
objCmd.Dispose()

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#

In SQLCE

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

In SQL CE

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.

Conclusion

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here