Using the Smart Device Extension (SDE) for Visual Studio .NET
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.
Figure 2: The Check Titles tabpage and the View Orders tabpage
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:
Figure 3: Adding a Reference to the SQL Server CE Managed Provider
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 --
'-----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.