Developing Pocket PC Apps with SQL Server CE
Pages: 1, 2, 3, 4, 5, 6
With the Web service created, I now proceed to use the SDE to create the
Pocket PC application. For an introduction to using the SDE, please refer
to Brian Jepson's "Previewing the .NET Compact Framework," and my article
"First Look at the Smart Device Extension (Beta 1) for Visual Studio
.NET."
The Pocket PC application consists of a tab control with two tabpages. The
first tabpage allows the salesperson to check for book titles and place
orders at bookstores. The second tabpage lists the orders, sorted by
bookstore. Figure 2 shows the various controls used by the form. The
controls used are Label, TextBox, Button, ComboBox, ListBox and Tab
controls.
When the form is first loaded, I need to check if the Pocket PC contains
the database for storing bookstores information. If it isn't, I will use
the SQL Server CE Engine object to create the database. For connecting to a
SQL Server CE database, we need to use the SQL Server CE Managed Provider.
So the first thing we need to do is to add a Reference to the
System.Data.SqlServerCe.dll assembly and import the relevant namespace:
System.Data.SqlServerCe.dll
Imports System.Data.SqlServerCe
Once I have created the database, I need to create the tables. Manipulating
tables involves programming the familiar ADO.NET classes, but in this
case we are using the classes in the SQL Server CE Managed Provider --
the SqlCeConnection and SqlCeCommand classes.
SqlCeConnection
SqlCeCommand
'-----conn and ds are defined globally-----
Dim conn As New SqlCeConnection( _
"Provider=Microsoft.SQLServer.OLEDB.CE.1.0;" & _
"Data Source=\My Documents\BookStores.sdf")
Dim ds As DataSet
'------------------------------------------
Sub createStoreDB()
' if database does not exist, create one
If Not File.Exists( _
"\My Documents\BookStores.sdf") Then
Dim sqlEngine As New Engine( _
"Data Source=" & _
"\My Documents\BookStores.sdf")
sqlEngine.CreateDatabase()
Dim cmd As New SqlCeCommand( _
"CREATE TABLE Stores(storeID int " & _
"Primary Key NOT NULL, " & _
"storeName nvarchar(20))", conn)
conn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = _
"CREATE TABLE Orders(storeID int, " & _
"title_id nvarchar(20), qty int)"
cmd.ExecuteNonQuery()
cmd.CommandText = _
"INSERT INTO Stores (storeID, " & _
"storeName) VALUES (1, " & _
"'Great BookStore')"
cmd.ExecuteNonQuery()
cmd.CommandText = _
"INSERT INTO Stores (storeID, " & _
"storeName) VALUES (2, " & _
"'Computer BookStore')"
cmd.ExecuteNonQuery()
conn.Close()
End If
End Sub
In the above code, I have created two tables -- Orders and Stores. The Order
table is to capture the orders made by bookstores and the Store table is
for containing the list of bookstores. I have also populated the Stores
table with two records.
Pages: 1, 2, 3, 4, 5, 6
Next Page
© 2017, O’Reilly Media, Inc.
(707) 827-7019
(800) 889-8969
All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.