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