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.
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.
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.
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.
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
<%@ 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
getTitles() Web method takes in a search string input parameter and
returns a dataset containing the titles that match the search string.
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:
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.
The next step is to load the bookstores list into the ComboBox control
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:
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
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.
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
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:
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
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)!
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:
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).
To connect to a SQL Server CE database, click on the button shown with a green arrow and a cylinder.
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:
To retrieve the records in the tables, you can use SQL statements under the SQL tab and examine the results under the Grid tab:
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).
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).
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.
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!
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 firstname.lastname@example.org.
Sample code for this article can be downloaded here. (603K zip)
Copyright © 2009 O'Reilly Media, Inc.