AddThis Social Bookmark Button

Print

Developing Pocket PC Apps with SQL Server CE
Pages: 1, 2, 3, 4, 5, 6

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:


Imports System.Data.SqlServerCe

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 -- the SqlCeConnection and SqlCeCommand classes.


'-----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 Pagearrow