Developing data driven applications on mobile devices can be a daunting task. Mobile devices are notoriously short on system resources (mostly memory). In most cases, only a small subset of data is needed. As a result, unlike other platforms, deciding on a database for your mobile application is a very important decision.

As with most development decisions, the choice of database is a balancing of benefits. You need to decide which features and limitations are the most important; there is no right answer in this article. But by looking at the differences in approach from each of the contenders, you can decide which is most important to you.

Pocket PCs are not PCs. Mobile devices are fundamentally different, in that most of them work with a single data store for both what we would call "memory" and "disk" storage in the PC world. This changes the way we think about using memory. No longer can we differentiate between what we keep in memory and what we keep on disk. As a result, the database on the Pocket PC must be frugal with the amount of data that's kept on the device.

Also, since applications shut down less frequently than do their PC brethren, you can assume that any database engine that is in use will take up memory longer than you might expect.

Not all of the options discussed in this article are products. Sometimes the right approach is not to use a product. This really depends on what you need from your database.

With WiFi becoming ubiquitous in many environments, using all-remote databases is a perfectly acceptable solution when you can depend on consistent connectivity. While the memory footprint of the data itself will largely be the same with remote databases as it would with other solutions, using remote database offloads computationally-intensive operations, such as query optimization, to the bigger machines in your enterprise.

In the Compact Framework, you would simply use the SQL Managed Provider (SqlConnection, SqlDataAdapter, etc.) to access databases through a TCP/IP connection, much as you would with a standard .NET application. The SQL Server implementation in the Compact Framework is more limited; it does support features like connection pooling, distributed transactions, encrypted connectionsm, and connections through the cradle. (Only TCP/IP connections are supported). If you can live with requiring network connections and are not hampered by its limitations, SQL Server might be the right solution for you.

Accessing data through Web services is another popular solution to using remote data. With Web services, you are not limited by what the data stores are on the other side of the network connection. While the data is being transmitted in a less than efficient way, you can store it on the machine in a more compressed way (e.g. DataSets) in your client. Web services do require connections to the remote machine, like SQL Server above. But if you want to control where your data is contained, using Web services is a good choice.

Far and away, the most popular database in the Compact Framework is SQL Server CE. SQL Server CE is a resident database that can do complex operations on the handheld without the need for a network connection. It also eases the synchronization with SQL Server databases, since it is built to act as a subscriber to replication data.

SQL Server CE's database engine includes the ability to access data through SQL Server with intermittent connectivity. That is, it reads from local storage when disconnected, and goes to SQL Server when connected. Because the data access interface in SQL Server CE is so much like its full .NET cousin, it eases the first time CF developer into a familiar comfort zone.

SQL Server CE also supports features that were only previously seen in its big brother, such as referential integrity, 249 indexes per table, multi-column indexes, and parameterized queries. It does all this for a mere one megabyte memory footprint on the handheld.

The only unfamiliar thing about SQL Server CE is that it does not support stored procedures. If you are used to putting your logic in stored procedures for encapsulation, you might feel a bit hampered by this limitation.

Sybase's iAnywhere

In the handheld database market, Sybase has a compelling offering called iAnywhere (also called Ultralight). Its key strength for Compact Framework applications is that it is a scalable platform.

One compelling feature is the ability to deploy only the features of the database that you are using. According to the company Web site, this means that you could deliver a database on the handheld with the database engine as small as 75K. In practice, iAnywhere is somewhere more competitive with SQL Server CE's 1MB size.

Another reason to use iAnywhere is that is can be used across platforms. If you are developing an application across Pocket PCs, Palm, or VxWorks, iAnywhere is the obvious solution, since it can be deployed on all these platforms.

For some time now, Microsoft's handheld OSs (CE, PocketPC, etc.) have supported something called Pocket Access (also called Property Database). Pocket Access is a well worn way to store database data. Pocket Access has some support by the community of Compact Framework developers, but there is no built-in support for it in .NET.

There is a popular CF wrapper written by one of the community members that has been getting some traction. This is a good solution if you are porting applications to the Compact Framework and already use Pocket Access to store your data. Otherwise, I would suggest using something else, since the lack of Microsoft support is a risk.

If all you need is the ability to store and retrieve local data, using XML or flat files is another solution. In this case, you would just store the data locally as a single file. The biggest problem with this solution is that the XML stack in the Compact Framework is limited, as it does not support XPath, XSLT, schema verification, or the XmlDataDocument class. But if your needs are humble and your funds low, this is a perfectly acceptable solution.

There is no one "right" answer to which database solution to use in the Compact Framework. A thoughtful consideration must be taken about which database to use because of the sheer size of differences in the actual devices that can be targeted in the Compact Framework, and the differences in application requirements. In this article, I have shown you a list of potential contenders. With a little analysis, you should be able to determine the right solution for your application.