Database application programming for the Pocket PC using SQL CE

• Download source - 27.8 KB

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

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.