Go Mobile

Create Compact, Robust Mobile Apps with SQL Server CE 2.0 and the .NET Compact Framework

Mark Brown and David Meunier

This article assumes you're familiar with SQL Server and Visual Studio

Level of Difficulty123

SUMMARY

Developers have myriad options when it comes to creating solutions for mobile devices. One of the greatest challenges facing mobile developers is finding a compact yet robust local storage solution. SQL Server CE 2.0 promises to deliver on both fronts. This new release represents a tremendous leap in terms of features and performance over its predecessor. This article will review some of the platform and tools choices developers have today. The authors will compare and contrast the significant new features in SQL Server CE 2.0 with the previous release. Following that, they will build a sample app for illustration.

Contents

Developing Mobile Solutions
Mobile Development Tools
SQL Server CE 2.0 Language Features
Server Features
Writing an App
Application Overview
Deployment Considerations
SQL Server CE 2.0—Call to Action

The explosive growth of mobile devices in recent years has enabled a new breed of mobile applications for consumers and enterprises alike. In light of this, Microsoft has been cranking out tools and frameworks so software developers will be able to fully exploit the capabilities of these smart devices. Combining these toolsets with a wide array of device options sets the stage for creating the most advanced mobile applications.

Microsoft® SQL 2000 Windows® CE Edition (also known as SQL Server™ CE 2.0) extends even more features of Microsoft SQL Server to Windows CE-based devices. SQL Server CE 2.0 provides all of the critical functionality you need to build robust database applications on the Windows CE or Windows CE .NET operating system (such as data storage, optimizing query processor, multiple connectivity options, and so on). This new edition also provides lateral compatibility with SQL Server 2000. Incredibly, it delivers all of this functionality, yet it is able to maintain an extremely small memory footprint.

This release provides significant new features and performance improvements over its predecessor. If you've worked with SQL Server CE in the past, you'll notice that performance has been bolstered in this new version and the programming model extended to support the .NET Compact Framework.

In this article, we'll take a look at several key features that are new to this release of SQL Server CE and examine some code that highlight these features. Keep in mind that this article does not attempt to address all facets of the SQL Server CE product. It should, however, whet your appetite for creating mobile applications with this new edition of SQL Server CE, whether you're a veteran mobile developer or just getting started.

Developing Mobile Solutions

If you're new to creating mobile applications, you'll find the list of Windows-powered devices, development tools, and frameworks daunting. SQL Server CE 2.0 is only one weapon in the mobile developer's arsenal. It would be impossible to recommend the perfect combination of tools and devices for creating your particular mobile solution. There are, however, some variables that you'll want to consider.

Today developers can target a range of Win-dows CE-powered devices. Both the Pocket PC (PPC) and Handheld PC (HPC) have been around for a while. Many of these can be equipped with wireless LAN modules such as Bluetooth and 802.11b, making them extremely functional devices that can stay well connected at all times. Devices based on the 3.x operating system tend to be consumer oriented, but businesses are realizing that these devices can also be used to empower their workforce as well as their customers.

Most recently, manufacturers have begun to embed wireless access technologies into the device itself. Gone are the days of the bulky PC Card sleeves piggybacked on the device in order to accommodate a wireless LAN card. The cellular phone is also converging with the Pocket PC, which makes for an interesting combination of technologies.

Embedded devices that are equipped with Windows CE have penetrated the workplace as well. And to widen their acceptance, many of these devices have been physically strengthened in order to survive a drop from several feet without breaking.

It's interesting to note that many of these embedded OEM devices have even newer versions of the Windows CE operating system than those available on consumer-oriented devices. The newest OEM devices are running version 4.x of Windows CE. These devices include the .NET Compact Framework by default. This makes this platform attractive for enterprise developers who want to be able to take advantage of the next generation tools and frameworks offered by Microsoft.

Windows CE is also being coupled with a variety of interesting hardware technologies including Compact Flash (CF) readers, bar code scanners, magstripe readers, Global Positioning Systems (GPS), fingerprint authentication, and cameras. Figure 1 explains some of the typical mobile solutions that companies are developing today using Windows CE-based devices and tools. For obvious reasons, these mobile applications require a compact yet robust local storage solution.

Figure 1 Mobile Application Examples

The biggest challenge for mobile application developers has been the relatively small amount of memory available on mobile devices (typically 16MB-32MB). As the memory capacity of mobile devices increases, so too will the complexity of mobile applications. Of course, a local storage mechanism must have a small memory footprint. Memory considerations aside, your application will most likely need to perform complex queries against the local storage as well.

Furthermore, these applications need to work equally well in both online and offline scenarios. This necessitates having both local and remote data access APIs, as well as some form of synchronization to the back-end database. As luck would have it, SQL Server CE 2.0 provides a framework for building applications that meets all these requirements.

In offline mode, SQL Server CE provides storage on the local device itself. Database changes can be reliably tracked on the device and synchronized to the back-end database server when connectivity is reestablished. The same is true of server-side changes. SQL Server CE supports a feature called Merge Replication, which when used in conjunction with SQL Server 2000, provides two-way synchronization of all database changes.

Mobile Development Tools

Mobile application developers have the option of using either current or next-generation tools with SQL Server CE 2.0.

For those who prefer using eMbedded Visual Tools 3.0 to develop mobile applications, these legacy tools will continue to be supported with SQL Server CE 2.0. Applications can continue to be written in both eMbedded Visual Basic® and eMbedded Visual C++®. If you choose to use eMbedded Visual Tools, be aware that SQL Server CE 2.0 needs version 3.x or later of the Windows CE operating system on the device.

Rapid mobile application development will be possible using next-generation tools and frameworks from Microsoft. With the shift toward developing applications based on the Windows .NET platform, mobile applications can take advantage of many innovations that are available on the desktop platform today. Applications that are written in eMbedded Visual Basic or Visual C++ will be upgradeable to Visual Basic .NET, C#, and ADO.NET.

Microsoft has unveiled its Smart Device Extensions (SDE) which enable mobile developers to use a unified toolset based on Visual Studio® .NET. Visual Studio .NET combined with SDEs will provide a seamlessly integrated development platform for creating Windows CE-based applications.

The next release of Visual Studio .NET (code-named "Everett") will provide native support for the .NET Compact Framework, Pocket PC 200x, and Windows CE .NET 4x devices.

SQL Server CE 2.0 Language Features

SQL Server CE 2.0 includes its own native OLE DB provider. Unlike the SQL Server 2000 OLE DB provider, which exposes many properties to specify a server and database, SQL Server CE uses only a single property—DBPROP_INIT_DATASOURCE—which takes the full path to the database file to be opened. For example, to open the database file \Windows\Northwind.sdf, pass the string \Windows\Northwind.sdf in the DBPROP_INIT_DATASOURCE property when calling IDBProperties::SetProperties.

SQL Server CE also supports a file-level access control mechanism called a database password. This password must be passed each time the database is opened. The property DBPROP_SSCE_DBPASSWORD in the provider-specific property set DBPROPSET_SSCE_DBINIT should be used to specify this value. When creating a new database, this property can be utilized to specify a password that must be used.

Intrinsic functions that you've come to know and love in SQL Server 2000 are now supported. Now you can use mathematical, string, and system functions in your queries to perform operations and return scalar values. Previously you needed to use eMbedded Visual Basic (or eMbedded Visual C++) functions to accomplish this. The use of intrinsic functions results in a tremendous performance gain over SQL Server CE 1.0.

For example, Figure 2 demonstrates how to convert a column to a char(50) column, in order to make the results in the ListBox easier to format. Using a UNION on a SELECT statement in SQL Server CE combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union. The following snippet illustrates how to use the new UNION clause in a SELECT statement:

Dim strSQL As String = "SELECT * FROM TableA UNION SELECT * FROM TableB"

Figure 2 Support for Intrinsic Functions

Dim strSQL As String = "SELECT CONVERT(char(50), title), _ td_sales FROM titles;" Dim ceCn As New SqlCeConnection("data source\ssceSample.SDF") ' create datareader to populate list box Dim ceDr As SqlCeDataReader Try ceCn.Open() Catch a As SqlCeException MsgBox(a.ToString()) End Try Try Dim ceCmd As New System.Data.SqlServerCe.SqlCeCommand(strSQL, ceCn) 'Create an instance of the ListBox. Dim listBox1 As New ListBox() With listBox1 ' Set the size and location of the ListBox. .Size = New System.CeDrawing.Size(208, 160) .Location = New System.CeDrawing.Point(8, 64) ' Add the ListBox to the form Me.Controls.Add(listBox1) ceDr = ceCmd.ExecuteReader() While ceDr.Read() .Items.Add(ceDr.GetString(0)) End While End With Catch a As SqlCeException MsgBox(a.ToString()) Catch a As Exception MsgBox(a.ToString()) End Try ceDr.Close() Try ceCn.Close() Catch a As SqlCeException MsgBox(a.ToString()) End Try

SQL Server CE 2.0 has added support for parameterized queries through ADO.NET. This feature is also available to developers using eMbedded Visual Tools 4.x using the OLEDBCE provider. Those of you who are familiar with parameterized queries in SQL Server 2000 will note that there are some differences. Since there is no built-in mechanism for storing queries in SQL Server CE, the parameters are actually placeholders ("?") for values that are supplied at run time. The other obvious difference between SQL Server 2000 and SQL Server CE is that named parameters are not supported. Parameterized Query support, as shown in the following code, is new to SQL Server CE 2.0.

Dim strSQL As String = "INSERT INTO TableA (col1, col2) VALUES (?, ?)";

Index pull is another new feature. Applications call the Pull method to extract data from a SQL Server database and store it in a database table in SQL Server CE. The initial version of SQL Server CE only brought down the primary key from a table. SQL Server CE 2.0 brings down the primary key and all additional indices.

Using pull in applications can track the changes made on a SQL Server CE table by setting the appropriate tracking options. SQL Server CE tracks all inserts, updates, and deletes (see Figure 3).

Figure 3 Using the Pull Method

' create RDA object Dim ceRda As New RemoteDataAccess() Dim strSQL = "SELECT * FROM Customers;" Dim strRemoteConnect = "provider=sqloledb;data source=" &_ strDataSource & ";Initial Catalog=Northwind;user id=sa;password=sa" Try ceRda.InternetUrl = strInternetURL ceRda.LocalConnectionString = strLocalConnect ' pull down FOO table ceRda.Pull("FOO", strSQL, _ strRemoteConnect, _ Data.SqlServerCe.RdaTrackOption.TrackingOn) Catch a As SqlCeException MsgBox(a.ToString()) End Try

Another improvement over the previous version of SQL Server CE is the addition of more robust error handling. As you may have noticed in some of the previous code fragments, applications that are written in Visual Basic .NET and C# using the .NET Compact Framework can utilize the built-in try/catch support. If you've written any desktop applications using Visual Studio .NET, you're probably already familiar with this style of exception handling. Developers who have been confined to using the On Error GoTo style of error handling in eMbedded Visual Basic will really appreciate this new language feature. To complement the try/catch construct, SQL Server CE has added a new SqlCeException class that will let you catch native SQL Server CE errors and handle them gracefully.

Unlike SQL Server CE 1.0, this release provides developers with long error descriptions, not just error numbers. The error description mechanism is implemented as an optional DLL that you can place in the path of your SQL Server CE application on the device. While you are debugging your application, you can deploy the DLL with the application. Once you have completed debugging your application, you can remove the DLL from the device.

Server Features

SQL Server CE 2.0 includes a new Connectivity Setup Wizard, which simplifies the creation of virtual directories and data access permissions (see Figure 4). Those of you who have used prior versions of SQL Server CE know that specific virtual directories and access rights are required to communicate between Windows CE-based devices and SQL Server. Using the connectivity wizard, you can create and manage virtual directories on a computer running Microsoft Internet Information Services (IIS). In addition, you can configure and manage NTFS permissions on the computers running IIS and SQL Server.

Figure 4 Connectivity Wizard

Like the previous release, this version uses an ISAPI DLL running under IIS to facilitate communications between the device running Windows CE and SQL Server 2000. This implies that if your device has HTTP access to the SQL Server CE virtual directory (that is, you can browse it using Pocket Internet Explorer), you'll be able to connect to your back-end database using Remote Data Access (RDA) or Merge Replication.

As with the previous version of SQL Server CE, you can now help protect the security of the local database either by using a password or by using a password along with 128-bit encryption. Using a password on the local database will prevent programmatic access to the local database, but it will not prevent any data from being read as clear text from the database file.

Using a password and encryption together will prevent programmatic access and will store user data in an encrypted form. If you are using Pocket PC 2002 extensions, you have the SSL native RSA-enhanced DLL with 128-bit encryption installed by default. If you are not using Pocket PC 2002, you will need to download the high-encryption pack..

Currently Microsoft recommends that you enable SSL on your IIS box and use Basic Authentication. Microsoft plans to add Kerberos support in future versions of Windows CE .NET (4.x and above). This will allow for the delegation of credentials between a Windows 2000 or .NET Server and CE.NET devices. Once Windows CE .NET 4.x devices begin shipping, SQL Server CE 2.0 will provide a support document that will explain how to take advantage of Kerberos support.

Another improvement is that SQL Server CE 2.0 now matches SQL Server 2000 in terms of the number of indices that are supported. The initial release maxed out at 32 indices, whereas this latest release now supports 249 indices.

The SQL Server Client Data Provider is another component that is included with SQL Server CE 2.0. This is implemented as a managed wrapper and allows applications to talk directly to the back-end SQL Server database. This should probably only be used in well-connected scenarios where your mobile device has a persistent network connection, and where you don't have a requirement for local data storage.

Another option the SQL Server Client Data Provider presents is the ability to query through the provider to SQL Server to retrieve data to be placed into a local SQL Server CE database.

SQL Server CE 2.0 also includes a significant enhancement to ISQLW (see Figure 5). This is the SQL Server CE counterpart to SQL Server Enterprise Manager. You can install the executable manually onto the device by doing a file copy using ActiveSync®. ISQLW is automatically installed on the Start menu of your device when you deploy your Visual Studio .NET SDE or eMbedded Visual Tools application for the first time.

Figure 5** ISQLW **

With the previous version of ISQLW, there was no easy way to view your database schema. This made it difficult to write queries unless you had the entire schema memorized. Now you can view your database objects in a tree-view, much like in the desktop version of SQL Query Analyzer.

SQL Server CE 2.0 provides two primary ways of connecting to back-end SQL Server databases: RDA and Merge Replication. RDA access supports connectivity to SQL Server 7.0 (and later) databases. Merge Replication, on the other hand, requires SQL Server 2000 on the back end. See Figure 6 for an architecture diagram of these two connectivity options.

Figure 6** Connectivity Options **

RDA in SQL Server CE 2.0 provides a simple way for a Windows CE-based application to pull data from a remote SQL Server database table and store it in a local device database table. Your application can then read and update the local database table. SQL Server CE can optionally track all changes that are made to the local table. Using this information, the application can later push the changed records from the local table back to the SQL Server table.

Windows CE-based applications can also use RDA to submit SQL statements to be executed on a remote SQL Server database. For example, an application could submit SQL statements that insert, update, or delete records to a remote SQL Server table.

With SQL Server CE, RDA is Internet-based. Simply put, SQL Server CE communicates with back-end SQL Server databases through IIS. By connecting via IIS, RDA takes advantage of IIS authentication and authorization services. And since the communication protocol is HTTP, the machine running SQL Server can be located behind a firewall and can be accessed through a publishing rule using Microsoft ISA Server (or Microsoft Proxy Server).

To reduce the amount of transmitted data, RDA uses compression. This makes RDA well suited to wireless transports. Optionally, encryption can be used to help safeguard sensitive user data. RDA also has a mechanism to handle communication failures. If a failure should occur, retransmission will resume from the last successfully transmitted message buffer.

RDA control provides programmatic access to a SQL Server 2000 or SQL Server 7.0 database. RDA access is provided by a Visual Studio .NET or eMbedded Visual Tools application.

SQL Server CE Merge Replication is based on SQL Server 2000 and uses the same Publisher/Subscriber model. Using Merge Replication enables data to be updated independently on the device and the server. This data can then be synchronized when the device is later connected to the server. Before you can create a subscription, you must configure SQL Server 2000 replication, create the SQL Server publication, including setting up a shared snapshot folder to store published data, and then enable the publication for anonymous subscriptions.

The SQL Server CE Replication object has methods for adding and dropping subscriptions and for initializing, invoking, and terminating Merge Replication on a subscription. SQL Server Replication is more sophisticated and more complex than RDA, although not difficult to program once it has been correctly configured.

An exciting new feature of SQL Server CE 2.0 is the addition of the SQL Server CE Managed Provider (or wrapper) that provides an interoperability layer for Visual Basic .NET and C# mobile applications using the .NET Compact Framework. If you already use ADO.NET in your desktop applications, you will be comfortable using the SQL Server CE Managed Provider with the .NET Compact Framework. This programming model gives you the ability to interact with the SQL Server CE database in a very intuitive way.

This wrapper actually leverages the native SQL Server CE DLLs. The beauty of this is that an eMbedded Visual Basic (or eMbedded Visual C++) application and a Visual Studio .NET application can run side-by-side on the same device. However, this does not imply that concurrent access to the same SQL Server CE database is supported. It just means that applications written using either platform can co-exist on the same device. SQL Server CE is still single-user database engine.

Two new namespaces were added to support managed code using the .NET Compact Framework. These are: System.Data.SqlClient and System.Data.SqlServerCe. Also a class called System.Data.SqlServerCE.SqlCeException provides try/catch support for Visual Basic .NET and C#.

To describe the power of the new managed code wrapper using Merge Replication in your application, a single call to the Synchronize method will bring the entire database schema and underlying data down to a device without having to write a single line of data description language (DDL).

Writing an App

Now let's demonstrate some of the new features of SQL Server CE 2.0 using a code project. We're going to create a simple C# SmartDevice application using Visual Studio .NET and the .NET Compact Framework. We'll deploy the application to the Pocket PC 2002 emulator to simplify development and testing.

For the purposes of this example, we'll use a database that is created when you install the NorthwindCE sample application that comes with the .NET Compact Framework SDK. If you're a veteran of SQL Server, you're probably already familiar with the Northwind database that ships with SQL Server 2000. The NorthwindCE sample application includes a setup script called SetupRepl.bat, which creates a copy of the database called Nwind_SQLCE. The script also installs a Publisher with a local Distributor on the computer where you are running SQL Server 2000. Furthermore, it creates a merge publication named SQLCEReplDemoNet from the Nwind_SQLCE database. We will instruct our example application to subscribe to this same publication.

Figure 7** New Publication **

Once your SQL Server instance has been configured as a Publisher/Distributor for replication and has a snapshot folder defined with the appropriate permissions, you can run the SetupRepl.bat that comes with the NorthwindCE sample. If all goes well, you should see that the publication has been successfully created, as shown in Figure 7.

Application Overview

The application we've designed will illustrate several of the new features of SQL Server CE 2.0 that were discussed earlier in the article, such as how to perform replication using the SqlCeReplication.Synchronize method. We'll demonstrate full (two-way) synchronization between the SQL Server 2000 Nwind_SQLCE database and a local SQL Server CE subscription database, as well as other features such as the new SqlCeException object and the use of parameterized queries.

Figure 8** Windows Form **

Figure 8 shows the Windows Form that we've designed for our SmartDevice application. The application allows us to query the Employees table of the local subscription database by entering a portion of the Last Name, and it will return the results in a ListView control. We've also added a Synchronize menu item that allows us to force any changes on the SQL Server 2000 database to be replicated down to our local SQL Server CE subscription database. SmartDevice may not be a thrilling application, but the way we get there is very interesting!

Before we get started, we'll assume that you've run the SetupRepl.bat script from the NorthwindCE sample. Make sure the SQL Server CE Server Agent has been configured with an IIS virtual directory name of "sqlce" and that it uses HTTP Anonymous access (certainly not a best practice for a production app). You can verify these settings using the SQL Server CE Connectivity Management MMC snap-in that comes with SQL Server CE 2.0.

The first part of the code we're going to look at demonstrates how to use the SQL Server CE SqlCeEngine object to create an empty database and the Replication object to synchronize with our existing SQL Server publication for the Nwind_SQLCE database. The DBInit function is called from our Form_Load event. It first creates an empty subscription database (if it doesn't already exist) and then calls DBSync to synchronize the publication database to the subscription database (see Figure 9).

Figure 9 Creating and Synchronizing a Database

private void DBInit() { try { if (!System.IO.File.Exists(sSubscriptionDB)) { SqlCeEngine oEng = new SqlCeEngine("data source=" + sSubscriptionDB); oEng.CreateDatabase(); DBSync(); } } catch (SqlCeException ex) { ShowErrors(ex); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } }

In our DBSync method, we've hardcoded all of the properties of the Replication object for illustrative purposes only. You would obviously want to maintain these in some sort of application-defined store, such as a SQL table or an XML file. We're using SQL Server authentication in our example. The Synchronize method creates the subscription database and brings down the entire schema for all database objects in the publication, including related indices, as shown in Figure 10.

Figure 10 Synchronize Method

private void DBSync() { // instantiate replication object SqlCeReplication oRepl = new SqlCeReplication(); try { // set publisher properties oRepl.Publisher = "MBLAP01"; oRepl.PublisherDatabase = "Nwind_SQLCE"; oRepl.Publication = "SQLCEReplDemoNet"; // set publisher security properties oRepl.PublisherLogin = "sa"; oRepl.PublisherPassword = "sa"; // set subscriber properties oRepl.SubscriberConnectionString = "Provider=Microsoft.SQLServer.OLEDB.CE.2.0; Data Source=" + sSubscriptionDB; oRepl.Subscriber = "MBPPC01"; // set internet properties oRepl.InternetUrl = "https://mblap01/sqlce/sscesa20.dll"; // perform full synchronization oRepl.ExchangeType = ExchangeType.BiDirectional; oRepl.Synchronize(); } catch (SqlCeException ex) { ShowErrors(ex); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { oRepl.Dispose(); } }

Once the subscription database has been created, and all objects in our publication have been replicated locally, we can use our application in a disconnected scenario. Changes made on either the SQL Server 2000 or SQL Server CE databases are resolved using merge replication the next time the DBSync method is called.

Please note the try...catch...finally construct technique of trapping exceptions in our SDE application. SQL Server CE exceptions are displayed in detail by the ShowErrors method. The code in Figure 11 shows how the SQL Server CE Exception stack is unraveled and displayed in a message box on the client.

Figure 11 Trapping Errors

public static void ShowErrors(SqlCeException ex) { SqlCeErrorCollection oErrors = ex.Errors; StringBuilder oStrBld = new StringBuilder(); Exception oInner = ex.InnerException; foreach (SqlCeError oErr in oErrors) { oStrBld.Append("\nError Code: " + oErr.HResult.ToString("X")); oStrBld.Append("\nMessage : " + oErr.Message); oStrBld.Append("\nMinor Err.: " + oErr.NativeError); oStrBld.Append("\nSource : " + oErr.Source); foreach (int iNumPar in oErr.NumericErrorParameters) { if (iNumPar != 0) oStrBld.Append("\nNum. Par. : " + iNumPar); } foreach (String sErrPar in oErr.ErrorParameters) { if (sErrPar != String.Empty) oStrBld.Append("\n Err. Par. : " + sErrPar); } MessageBox.Show(oStrBld.ToString(), "SqlCeException"); oStrBld.Remove(0, oStrBld.Length); } }

The last code sample that we'd like to show is the FindEmployees method. This is called from the Click event of the Find Now button. The method begins by instantiating the SqlCeConnection object and connecting to our local subscription database.

Next, we construct a SqlCeCommand object which consists of a parameterized query against our Employees table, as shown in Figure 12. Notice how we're using the RTRIM intrinsic function to remove trailing spaces from the FirstName and LastName columns so that we can format these nicely in our ListView control.

Figure 12 Trimming Spaces

private void FindEmployees() { // instantiate the SqlCeConnection object SqlCeConnection oCon = new SqlCeConnection("DataSource=" + sSubscriptionDB); try { oCon.Open(); SqlCeCommand oCmd = oCon.CreateCommand(); oCmd.CommandText = "SELECT EmployeeID, RTRIM(LastName) + ', ' + RTRIM(FirstName) AS \"Full Name\" FROM Employees WHERE LastName LIKE ? ORDER BY LastName"; // pass the search term as a parameter oCmd.Parameters.Add(new SqlCeParameter("p1", txtLastName.Text + "%")); SqlCeDataReader oReader = oCmd.ExecuteReader(); // populate the list view control lvwEmployees.Items.Clear(); while (oReader.Read()) { ListViewItem oItem = lvwEmployees.Items.Add(new ListViewItem(new String []{oReader.GetString(1), oReader.GetInt32(0).ToString()})); } } catch (SqlCeException ex) { ShowErrors(ex); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { // close and release the connection if (oCon.State == ConnectionState.Open) oCon.Close(); if (oCon != null) oCon.Dispose(); } }

Using the Parameters collection of the SqlCeCommand object, we can pass in the search pattern we want to use with the LIKE operator of our SELECT statement. Finally, we use the SqlCeDataReader to execute our query and populate the ListView control, then close and release the database connection that we opened to the subscription database.

Figure 13** Pocket PC Emulator **

Figure 13 shows what our completed application looks like running in the Pocket PC 2002 Emulator.

Deployment Considerations

Finally, we want to review a few things to consider when deploying your applications to the field. Although the SQL Server CE database engine itself is extremely reliable, any device it runs on can be susceptible to failure, primarily due to limited battery life. Consequently, any mobile solution you develop should always be designed with disaster recovery in mind.

Durable storage can be achieved by using persistent memory technologies such as CF Media. Plan to keep your application database and application executables stored in CF in order to allow your users to recover in the event that a cold boot is required. Creating a CAB file for your application greatly simplifies installation of your application by employees in the field.

SQL Server CE 2.0—Call to Action

SQL Server CE 2.0 will enable you to start building your own enterprise-class data management solutions for mobile devices. This release of SQL Server CE represents a significant leap in terms of features and performance over the prior release. We encourage everyone to get their hands on this exciting new release and start building the next "killer" mobile application.

For related articles see:
SQL Server CE: New Version Lets You Store and Update Data on Handheld Devices
Windows CE: Develop Handheld Apps for the .NET Compact Framework with Visual Studio .NET
Windows CE: eMbedded Visual Tools 3.0 Provide a Flexible and Robust Development Environment

For background information see:
.NET Compact Framework by Srinivasa Sivakumar, Craig Morris, Peter Stanski, and Andrew Polshaw (Wrox Press, February 2002)

Mark Brownis Chief Software Architect for IdentityMine Inc. (https://www.identitymine.com), an ISV based in Tacoma, WA. IdentityMine designs and builds next-generation Internet business solutions using Microsoft .NET Enterprise server technologies. You can reach Mark at mark.brown@identitymine.com.

David Meunieris a Principal Software Engineer for IdentityMine Inc., an ISV based in Tacoma, WA. IdentityMine designs and builds next-generation Internet business solutions using Microsoft .NET Enterprise server technologies. You can reach David at david.meunier@identitymine.com