ONDotNet.com    
 Published on ONDotNet.com (http://www.ondotnet.com/)
 See this if you're having trouble printing code examples


Developing Pocket PC Apps with SQL Server CE

by Wei-Meng Lee
07/08/2002

The recently-launched Beta 1 of the Microsoft .NET Compact Framework included SQL Server 2000 CE Edition 2. SQL Server CE extends the desktop version of SQL Server 2000 to devices running the Windows CE operating system. At the same time, it offers developers the familiarity of the programming model of its desktop cousin. In this article, I will explore the SQL Server CE edition and see how it can be used in your Pocket PC applications developed using the Smart Device Extension (SDE) for Visual Studio .NET.

A Quick Overview of the Architecture of .NET Compact Framework

The .NET Compact Framework (.NET CF) is a subset of the .NET Framework. The key benefit here is that programmers have the same flexibility in using the languages familiar to them as well as reusing the knowledge of the .NET Class library. However, do note that not all of the classes and methods in the .NET Framework are supported in the .NET Compact Framework Class library. Figure 1 shows the various components in a typical mobile platform.


Figure 1: Components in the .NET Compact Framework

The platform allows native Windows CE applications to co-exist with .NET-based applications. The Application Domain Host (itself a native application) starts an instance of the Common Language Runtime (CLR) for running managed code. Applications developed with the SDE run on top of the CLR, utilizing the .NET Compact Framework Class Library.

Building Our Sample Application

The sample application that I will be building in this example illustrates several aspects of .NET CF Pocket PC programming. I will illustrate how you can consume Web services in the .NET CF, use the various controls in .NET CF, and build a SQL Server CE database. The application in this example allows a salesperson to use a Pocket PC to search for books (from a Web service) and place orders at bookstores. The orders are captured in a local SQL Server CE database.

Web Services

Our Web service makes use of the Pubs database in SQL Server 2000. The Pubs database comes preinstalled with SQL Server 2000, and this is the main reason why I chose to use it for this article. It is easier for you to try out the code on your own machine.


For more information on creating .NET Web Services, check out the Web Services section of the .NET DevCenter.


The Web service simply contains one Web method named

getTitles():

<%@ WebService Language="VB" Class="Service1" %>

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Public Class Service1 : Inherits WebService 

  <WebMethod()> _
  Public Function getTitles( _
    ByVal title As String) As DataSet
 
    ' Make the database connection.
    Dim conn As New SqlConnection( _
      "server=localhost; uid=sa;" & _
      "password=; database=Pubs")

    ' Create the SQL and set the parameter.
    Dim sql As String = "SELECT * FROM " & _
      "titles WHERE title LIKE @title"
    Dim comm As New SqlCommand(sql, conn)
    comm.Parameters.Add("@title", _
                        "%" & title & "%")

    ' Create a data adapter and data set.
    Dim dataAdapter As New SqlDataAdapter(comm)
    Dim ds As New DataSet()

    ' Fill the data set with the query results.
    conn.Open()
    dataAdapter.Fill(ds, "titles")
    conn.Close()

    ' Return the dataset.
    Return ds

  End Function

End Class

The getTitles() Web method takes in a search string input parameter and returns a dataset containing the titles that match the search string.

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.


Figure 4: Relationship between the Stores and Orders tables

The next step is to load the bookstores list into the ComboBox control using the SqlCeDataReader class.


Sub LoadStores()
  conn.Open()
  Dim reader As SqlCeDataReader
  Dim cmd As New SqlCeCommand( _
    "SELECT * FROM Stores", conn)
  reader = cmd.ExecuteReader
  While reader.Read
      cboStoreID.Items.Add( _
        reader.Item("storeID"))
  End While
  conn.Close()
End Sub

So, when the Form is loaded, the ComboBox is filled with the bookstore list:


Figure 5: Populating the ComboBox

When a Store ID is selected, it will display the name of the store selected:


Private Sub cboStoreID_SelectedIndexChanged( _
  ByVal sender As System.Object, _
  ByVal e As System.EventArgs) _
  Handles cboStoreID.SelectedIndexChanged
    conn.Open()
    Dim sql As String = _
      "SELECT * FROM Stores WHERE storeID=" & _
       cboStoreID.Items(cboStoreID.SelectedIndex)
    Dim cmd As New SqlCeCommand(sql, conn)
    Dim reader As SqlCeDataReader = cmd.ExecuteReader
    reader.Read()
    lblStoreName.Text = reader.Item("storeName")
    conn.Close()
End Sub

Consuming the Web Service

To search for specific titles, our application will use the Web service. Consuming a Web service in .NET CF is no different from consuming it on the .NET Framework (select the project in the Solution Explorer, right click on it, choose Add Web Reference, and supply the path to the service's WSDL). The only issue to note here is to ensure that actual machine name of the Web service is used. Using localhost will not work in Beta 1 of the SDE.

In the BookStoreApp.zip file, the Web reference needs to be modified to work with your system: In the Solution Explorer, locate the TitlesWS Web reference, select it, and change the Web Reference property to the correct WSDL URL (it is currently http://YOURHOSTHERE/Services/service1.asmx).


For more information on creating .NET Web Services, check out the Web Services section of the .NET DevCenter.


The result of the Web service is bound to the Result ComboBox.


Private Sub cmdSearch_Click( _
  ByVal sender As System.Object, _
  ByVal e As System.EventArgs) _
  Handles cmdSearch.Click
    Dim ws As New TitlesWS.Service1()
    ' get the web service
    ds = ws.getTitles(txtSearch.Text)
    cboResult.DataSource = ds.Tables(0)
    cboResult.DisplayMember = "title"
End Sub

As usual, when a title is selected, more information about the selected title is displayed in the label controls:


Private Sub cboResult_SelectedIndexChanged( _
    ByVal sender As System.Object, _
    ByVal e As System.EventArgs) _
    Handles cboResult.SelectedIndexChanged
    ' display the information of the 
    ' selected book.
    Dim row As DataRow
    row = ds.Tables("titles").Rows( _
      cboResult.SelectedIndex)
    lblTitleID.Text = row.Item("title_id")
    lblPrice.Text = "$" & row.Item("price")
    txtNotes.Text = row.Item("notes")
End Sub

Figure 6: Displaying information about the title selected

To place an order for a bookstore, specify the quantity and click on the Add button. An order for the bookstore selected will be added:


Private Sub cmdAdd_Click( _
  ByVal sender As System.Object, _
  ByVal e As System.EventArgs) _
  Handles cmdAdd.Click
    '---add the title to the stores ORDER table
    conn.Open()
    Dim sql As String = "INSERT INTO Orders " &_
      "(storeID, title_id, Qty) VALUES (" & _
      cboStoreID.Items(cboStoreID.SelectedIndex) _
      & ",'" & _
      lblTitleID.Text & "'," & txtQty.Text & ")"
    Dim cmd As New SqlCeCommand(sql, conn)
    cmd.ExecuteNonQuery()
    MsgBox("Title added for " & lblStoreName.Text, _
      MsgBoxStyle.Information, "Orders")
    conn.Close()
End Sub

And a message box confirms the addition:


Figure 7: Confirming the addition

Checking the Orders

Clicking on the second tabpage displays the orders made by bookstores. When the Refresh button is clicked, the first ListBox control will display the list of bookstores available:


Private Sub cmdRefresh_Click( _
  ByVal sender As System.Object, _
  ByVal e As System.EventArgs) _
  Handles cmdRefresh.Click
    '---displays the list of stores available
    conn.Open()
    Dim sql As String = "SELECT * FROM Stores"
    Dim cmd As New SqlCeCommand(sql, conn)
    Dim reader As SqlCeDataReader = _
      cmd.ExecuteReader
    '---clears the listbox
    cboStoreIDs.Items.Clear()
    While reader.Read()
        cboStoreIDs.Items.Add( _
          reader.Item("storeID"))
    End While
    conn.Close()
End Sub

When a particular bookstore is selected, its corresponding order is then listed on the second ListBox control.


Private Sub cboStoreIDs_SelectedIndexChanged( _
  ByVal sender As System.Object, _
  ByVal e As System.EventArgs) _
  Handles cboStoreIDs.SelectedIndexChanged
    '---displays the orders of store selected
    conn.Open()
    Dim sql As String = _
      "SELECT * FROM Orders WHERE storeID=" & _
      cboStoreIDs.Items(cboStoreIDs.SelectedIndex)
    Dim cmd As New SqlCeCommand(sql, conn)
    Dim reader As SqlCeDataReader = _
      cmd.ExecuteReader()
    '---clears the listbox
    cboOrders.Items.Clear()
    While reader.Read()
        cboOrders.Items.Add( _
          reader.Item("title_id") & " - " & _
          reader.Item("qty") & "-copy(ies)")
    End While
    conn.Close()
End Sub

Figure 8: Viewing the Orders

Oh, one last thing. You may notice that the bottom of our form does not contain the virtual keyboard. This is not easily noticeable if you are using the emulator. But when testing using a real device, you may be stumped when you need to key in the search title. Don't worry; to ensure that the virtual keyboard is displayed, simply add an empty menu bar to your form. And of course, do not forget to shift the controls up a little, or else the tab will not be seen (as it is covered by the virtual keyboard)!

Using the SQL Server CE Query Analyzer

Phew! If you have been following closely, you should be able to get the application up and running, albeit with some little trips here and there. As the SDE is still in Beta 1, there are some rough spots that I have encountered when testing the application. The most common problem is when the Web service does not work -- you click on the Search button but there is no response from the Web service. I noticed one way to detect if the Web service is responding: when the Search button is clicked, you will notice that the Output window will show messages indicating that some threads have exited:


Figure 9: Detecting if a Web service is called

If no such messages are displayed, it is then likely that the Web service is not called. In this case, simply stop your application and start again.

Another area that you might want to probe into is whether or not your database tables are updated correctly. SQL Server CE 2.0 comes with a tool known as the SQL Server CE Query Analyzer. The first time you run an application with the SQL Server CE Managed Provider referenced, the SDE will copy the relevant files to the target device (or emulator). The SQL Server CE Query Analyzer can then be invoked by clicking Start->SQLCE Query (as shown in Figure 10).


Figure 10: Using the SQL Server CE Query Analyzer

To connect to a SQL Server CE database, click on the button shown with a green arrow and a cylinder.


Figure 11: Connecting to a SQL Server CE database

You can either connect to an existing database or create a new one. For this article, I will show how we can examine the database that we have created earlier.

Figure 12 shows the tables and fields of the database that we created earlier:
Figure 12: Examining the BookStores.sdf database

To retrieve the records in the tables, you can use SQL statements under the SQL tab and examine the results under the Grid tab:


Figure 13: Using SQL Statements and examining the results

A nice feature of the Query Analyzer is the "Button Presets" function. The Button Presets function allows you to store commonly used SQL statements so that they can be invoked by simply clicking on the preset buttons (labeled 1-10).


Figure 14: Using the Preset button for storing commonly used SQL statements

Resetting the Emulator

There are times when the Query Analyzer simply refuses to work, perhaps due to the beta nature of this product. In most cases, doing a soft reset on the emulator can solve the problem. A soft reset does not erase the database stored in it. However, a hard reset will wipe out the database, as well as the Query Analyzer. The only way to get it back is to run the application in Visual Studio .NET again (as the database needs to be recreated).

Opening and Closing

You may also notice that the application opens and closes database connections before and after each operation. I found that, in the current beta, the emulator would crash if the connection was left open when the application exits.

Conclusion

In this article, we have seen how to use the SDE to create a simple Pocket PC application to consume a Web service, and how information can be stored locally using the SQL Server CE Edition 2. One problem exists: how do you synchronize the data on the Pocket PC to your desktop? In the next article, I will discuss how data stored in SQL Server CE can be replicated and synchronized with a SQL Server database. Till then, have fun!

About the Author

Wei-Meng Lee is an author and developer specializing in .NET. He has co-authored many books on XML and mobile application development. Wei-Meng is also a contributing author to SQL Server Magazine, Visual Studio Magazine, and .NET Magazine. Contact Wei-Meng at wei_meng_lee@hotmail.com.

Sample code for this article can be downloaded here. (603K zip)


Return to the .NET DevCenter

Copyright © 2009 O'Reilly Media, Inc.