Database Development for Windows Powered Pocket PC Phone Edition

MSDN Library
Mobile and Embedded Development
Windows Mobile
Technical Articles
Working with Data

A Technical Comparison of Replication and Remote Data Access Features in SQL Server 2005 Mobile Edition 3.0
ADOCE for ADO Programmers
Architecting Disconnected Mobile Applications Using a Service Oriented Architecture

DataSet Server CE: Database Connectivity for Windows Mobile-Based Devices
Desktop and Device Tools Usage for SQL Mobile
How to Develop an Efficient Architecture based on SQL Server CE Replication Requirements
It's Official: SQL Server CE 1.1 Loves ActiveSync
Multi-User Considerations in Data Synchronization for SQL Server 2005 Mobile Edition 3.0
Not All Record Sets Are Created Equal...
RDA and Replication Considerations When Using SQL Server 2000
Step by Step: Developing a SQL Mobile Application with Visual Studio 2005 and SQL Server 2005
Step by Step: Program Microsoft SQL Server CE 2.0 Merge Replication Using .NET Compact Framework
Storing Application Data and Settings in Windows Mobile-based Smartphone Applications using .NET Compact Framework
The Desktop Side of Mobile Computing
Upgrading from SQL Server CE 2.0 to SQL Server 2005 Mobile Edition
Using ADOCE from a C++ Application
Using Data in Native Code

A New Era of Mobile Computing Begins

The pieces of mobile solution system architecture are swiftly materializing and coming together. First and foremost, companies are becoming increasingly aware that mobile solutions can drive improved efficiency as well as new business opportunities. Consumers are beginning to see the use of mobile devices in everyday life. Mobile device hardware, in particular the new Pocket PCs, are leading the evolution and address both enterprise as well as consumer needs. The key enabling piece glues everything together—new software and development tools.

The past few months, I have worked with the new Visual Studio .NET, the Smart Device Extensions, and the new SQL Server™ CE 2.0. These software components, combined with the connected Pocket PCs such as the Pocket PC Phone Edition, improve development efficiency and already drive many new mobile applications.

New Features of SQL Server CE

From an overview perspective, SQL Server CE delivers a paradox. As a local database for mobile devices, SQL Server CE supports disconnected scenarios. In most cases, a Pocket PC application running on a Pocket PC without a network connection will require a local data store. SQL Server CE also supports connected scenarios and makes data transportation to and from remote servers efficient both from a development and from a bandwidth perspective.

These are some, but not all, of the highlights of the new SQL Server CE:

Integration with the new .NET Compact Framework classes for local SQL Server CE management, as well as for remote SQL Server connectivity.
Connectivity Setup Wizards make installation of SQL Server CE components simple.
A vast number of new intrinsic functions, including the much appreciated NEWID, CHAR, CHARINDEX, UNICODE, LEN, LTRIM, RTRIM, SPACE, SUBSTRING, IDENTITY, DATALENGTH, and more.
UNION (SELECT * FROM Orders UNION SELECT * FROM OldOrders).
The ability to pull remote server table indexes using Remote Data Access Pull.
A much improved SQL Server Query Analyzer.

The new SQL Server CE 2.0 Data Access Architecture relies on classes found in the namespaces:

System.Data.SqlServerCE (manages the local database as well as remote server connectivity using Merge Replication and Remote Data Access)
System.Data.SqlClient (manages remote databases and includes support for TSQL and stored procedures)

As the Data Access Architecture has been moved over to the .NET Compact Framework, the components included therein have been improved and are easier to work with. For example, the previous Merge Replication Initialize, Run and Terminate schema is now replaced with a single method, System.Data.SQLServerCE.Replication.Synchronize, which creates the schema and downloads the data at the first synchronization. Then, it pushes modified data, and brings modified data down at subsequent synchronizations.

The new Remote Data Access classes also deliver improvements including the ability to pull remote table indexes, and the ability to define a batch-mode for the Push method. I will now walk you through a sample golf score card application built using SQL Server CE 2.0, Remote Data Access and Visual C#, and then take a look at the code.

The Sample Application: Walking Across the Golf Course

The sample application, Golf Anyplace, can run on standard Pocket PCs; however, the built-in connectivity in the Pocket PC Phone Edition will be of great advantage on an actual golf course. Golf Anyplace is essentially a digital score card that can keep track of your results and the results of other players. The idea is that the golfers each use a Pocket PC Phone Edition to track the score. As everyone can push their own score to the remote server as well as pull down the others' scores, it is possible to constantly see the progress of the game.

This is how it looks (no, I am not a user interface designer):

The main form is used to enter your own score. It can also be used to view the score of other players.

Figure 1. Enter your score

The Synchronize command pushes the local data to the remote server, and then pulls down all the remote score data.

Figure 2. Synchronize scores

You use the View form to see both a detailed and an overview picture of the game

Figure 3. Keep track of all the golfers

The Sample Application: Walking Through the Code

Let us take a look at the code. At some places in the code, you will notice that I have chosen to solve the same issue in different ways. Some examples include how I go about class initializations, using DataReader vs DataSet, populating a ListView, using SQL Server CE wrapper or not, etc. I hope this is helpful to you, as one solution may work better in one scenario while another solution may work better in another. Note that you can download the sample code behind Golf Anyplace.

Starting Up

The start up object of Golf Anyplace is GolfAnyplace.RDAGolf. This is what the constructor logic does when starting up the application:

public RDAGolf()
{
InitializeComponent();

// Make sure there is a database!
SQLServerCEWrapper SSCEWrapper = new SQLServerCEWrapper();
bool NewDatabase = SSCEWrapper.CreateDatabase();

// If a new database was created, then perform a first pull!
if(NewDatabase==true)
{
// Call the Pull without keeping local data! (Nothing there!)
SSCEWrapper.Pull(false);
}

// Populate combobox
for(int iCounter=1; iCounter < 19; iCounter++)
this.cmbHole.Items.Add(iCounter.ToString());

// Make first hole default
this.cmbHole.SelectedIndex = 0;

}

You can see that I have implemented an SQL Server CE wrapper. I have done so to structure the database related code into one place. The wrapper helps me manage and work with the local database as well as the remote server synchronization.

The following shows the first lines of code in the wrapper.

using System;
using System.Data;
using System.Windows.Forms;
using System.Collections;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Data.SqlClient;

namespace GolfAnyplace
{

public class SQLServerCEWrapper
{

public string InternetServer = "http://servername/directory/sscesa20.dll";
public string InternetUser ="DOMAIN\\user";
public string InternetPassword = "password";
public string RemoteConnection = "Provider=sqloledb;Data Source=MySQLServer;Initial Catalog=GolfAnyplace;User Id=user;Password=password";
public string LocalDatabase = "\\My Documents\\ga.sdf";
public string LocalConnection = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=\\My Documents\\ga.sdf";
public string LocalTableName = "Result";
public string RemoteTableName = "Result";

The only reason that I made the RDA properties public is that I will need them when I illustrate how to use the DataReader in another part of the application. The RDA properties should really be private, as I would then be consistent with the rest of application and would have passed a DataSet from the wrapper instead.

This is what the wrapper's CreateDatabase and Pull methods look like:

public bool CreateDatabase()
{
// Make sure the database exists!
// Return true if a new database was created.
// Return false if new database was not created.
// Known bugs:
// Version   Date   Who Comment
// 00.00.000 020808 ASJ Created
// *****************************

if(System.IO.File.Exists(LocalDatabase) == false)
{
System.Data.SqlServerCe.Engine SQLCEEngine = new System.Data.SqlServerCe.Engine(LocalConnection);
SQLCEEngine.CreateDatabase();
return true;
}
else
{
return false;
}

The CreateDatabase method creates a new database, if one does not exist.

public void Pull(bool KeepLocalData)
{
// Pull table to local database.
// Known bugs:
// Version   Date   Who Comment
// 00.00.000 020808 ASJ Created
// *****************************
string SQL;
SqlCeConnection cn;
SqlCeCommand cmd;
RemoteDataAccess RDA = null;

// Create and initiate the new RDA object
RDA = new RemoteDataAccess(InternetServer, InternetUser, InternetPassword, LocalConnection);

// Keep the local data? If so, Push it first!
if(KeepLocalData)
{
RDA.Push(LocalTableName, RemoteConnection, RdaBatchOption.BatchingOff);
}

// Before the Pull, we must drop the local table
// Open connection to local database
cn = new SqlCeConnection(LocalConnection);
cn.Open();

// Drop the local table
SQL = "DROP TABLE " + LocalTableName;
cmd = new SqlCeCommand(SQL, cn);

// An error occurs if the table does not exist.
// If that happens, I just want to stroll along.
try
{
cmd.ExecuteNonQuery();
}
catch{ }

// Close the connection
cn.Dispose();

// Finally it is time to pull the remote table!
SQL = "SELECT PlayerName, Hole, Result FROM " + RemoteTableName;
RDA.Pull(LocalTableName, SQL, RemoteConnection, RdaTrackOption.TrackingOnWithIndexes, "RDAErrors");

// Clean up
RDA.Dispose();

}

Note that I pass a boolean variable into the Pull method. It is not possible to pull contents from a remote server table to a local SQL Server CE table that already exists. The pull therefore must be preceded by a DROP TABLE statement. The boolean variable is used to controls whether or not to keep the local data by first pushing to the server. You can also see the new batching parameter on the Push method. In this case I use BatchingOff, which means that I do not consider the rows being pushed to the server as one batch, that either must go through completely or not at all. The other valid setting is BatchingOn. As you can see, I use the SqlCeCommand to execute the DROP TABLE.

I would like to highlight the fact that I specify the field names in the Pull statement. Make sure you always specify field names, both in Pull statements as well as in regular SELECT statements. That way, you can make sure that you only touch data that is absolutely necessary to touch. Moreover, you can easily prevent problems related to finding out that there are certain fields that you cannot pull down, such as rowguidcol, int identity, and timestamp fields.

What Is on the Server?

Before we go deeper into the Pocket PC application's source code, I would like to reveal what is on the server side. The remote server is a SQL Server 2000 running a database called GolfAnyplace. The database has only one table, the Result table. This is the table's data definition:

CREATE TABLE [dbo].[Result] (
[PlayerName] [nvarchar] (50) NOT NULL ,
[Hole] [smallint] NOT NULL ,
[Result] [smallint] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Result] WITH NOCHECK ADD 
CONSTRAINT [PK_Result] PRIMARY KEY CLUSTERED 
(
[PlayerName],
[Hole],
[Result]
) ON [PRIMARY]

I have also configured the SQL Server CE 2.0 server agent with the comfortable assistance of some Wizards.

Populating a ListView

The GolfAnyplace project illustrates two ways to populate a listview: using a DataSet and using a DataReader. Since I have chosen to implement a wrapper around SQL Server CE, it makes most sense to use a DataSet. A DataSet can be disconnected and passed around (marshaled) between classes in that disconnected state. Since a DataReader requires an open connection to the database, its use should be as close to the opening and closing of the connection as possible. The first sample code shows how to populate a listview by looping through a DataSet.

This is the code that populates the listview:

private void UpdateResultListView()
{
// Updates the listview.
// Known bugs:
// Version   Date   Who Comment
// 00.00.000 020808 ASJ Created
// *****************************
DataSet ds = null;
SQLServerCEWrapper SSCEWrapper = new SQLServerCEWrapper();

// Get the DataSet
ds = SSCEWrapper.GetPlayerResult(txtPlayerName.Text);

// Clear listview
lvwResult.Items.Clear();

// Loop through the DataSet
foreach (DataRow dr in ds.Tables[0].Rows)
{
ListViewItem lviItem = new ListViewItem(dr["Hole"].ToString());
lviItem.SubItems.Add(dr["Result"].ToString());
lvwResult.Items.Add(lviItem);
}
}

This is the corresponding code found in the wrapper class:

public DataSet GetPlayerResult(string PlayerName)
{
// Get data from local database. Return as DataSet.
// Known bugs:
// Version   Date   Who Comment
// 00.00.000 020808 ASJ Created
// *****************************
string SQL;
SqlCeConnection cn;
SqlCeCommand cmd;
DataSet ds;
SqlCeDataAdapter da;

// Initialize a new connection
cn = new SqlCeConnection(LocalConnection);

// Open connection
cn.Open();

// Build SQL
SQL = "SELECT PlayerName, Hole, Result FROM " + LocalTableName + " WHERE PlayerName = '" + PlayerName + "' ORDER BY Hole";

// Initialize a new command
cmd = new SqlCeCommand(SQL,cn);

// Initialize a new DataSet
ds = new DataSet();
da = new SqlCeDataAdapter(SQL,cn);

// Fill the DataSet with data using the adapter
da.Fill(ds, LocalTableName);

// Clean up
cn.Dispose();
cmd.Dispose(true);

// Return the DataSet
return ds;

}

The following sample code shows how to do the same thing, but using DataReader instead.

private void UpdateDetailedResultListView()
{
// Update Detailed Results using a DataReader.
// Known bugs:
// Version   Date   Who Comment
// 00.00.000 020808 ASJ Created
// *****************************
SQLServerCEWrapper SSCEWrapper = new SQLServerCEWrapper();
string SQL;

// SQL for the detailed listview!
SQL = "SELECT PlayerName, Hole, Result FROM Result ORDER BY PlayerName, Hole";

SqlCeConnection cn = new SqlCeConnection();
cn.ConnectionString = SSCEWrapper.LocalConnection;

// Open the connection
cn.Open();

// Initiate the command and execute to the reader.
SqlCeCommand cmd = new SqlCeCommand(SQL,cn);
SqlCeDataReader dtr = cmd.ExecuteReader();

// Clear listview
this.lvwResultDetailed.Items.Clear();

// Start reading!
while (dtr.Read())
{

ListViewItem lviItem = new ListViewItem(dtr["PlayerName"].ToString());
lviItem.SubItems.Add(dtr["Hole"].ToString());
lviItem.SubItems.Add(dtr["Result"].ToString());
lvwResultDetailed.Items.Add(lviItem);

}

// Close the DataReader
dtr.Close();

// Clean up
cmd.Dispose(true);
cn.Dispose();

}

Finally, note that the sample code available for download could use some error handling. I also left the Options form (see figure 4) in the project although it needs some additional code to support it fully.

Figure 4. SQL Server CE Options form

Tips and Tricks

These are some tips and tricks that I would like to share with you:

Hire a User Interface designer.
Don't leave your SQL Server CE code without proper error handling using the Try, Catch and Finally constructs.
You can use the Pocket Emulator if you don't have a Pocket PC intended for development or an Ethernet adapter to the Pocket PC you have.
Trust the SQL Server CE Connectivity Wizards, or at least start out with them.
Use the server side SQL Profiler to monitor what is going on when you push and pull, or use Merge Replication. If for nothing else, you can see when or if anything happens.
Learn where to find the answers to your coding problems on the Internet. See some of my favorite sites below.

Conclusion

Visual Studio .NET, Visual C# or Visual Basic .NET and SQL Server CE 2.0 match each other very well. I hope you are able to pull some good ideas from the things I pushed your way with this article.

Contents