Developing Pocket PC Apps with SQL Server CEby Wei-Meng Lee
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.
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.