Evaluating Database Technologies

[Nathan]

Posting both to SQL CE and Compactframework since it is relevant to both.

I am responsible for evaluating our future database strategy for a consumer
Pocket PC application that currently using CF 1.0 and SQL CE 2.0.

I am not terribly enamored with SQL CE 2.0. While it has worked okay and
always works *on my devices*, the following are some pain points:
Not being able to write the database format on the desktop.
Corrupted databases that happen fairly often. Not always able to find a good
reason why they get corrupted; again it always works on my devices, not
always for the customer.
Unhelpful error codes like 28558 failure to create database and no more
information or 27750 Cannot load sscemw20.dll or ssceca20.dll is missing or
not registered when it.
Having to do fancy things that just might fix the errors above.
Discovering from the archive of these newsgroups that some of these SQL CE
errors are still a mystery to the talented people on these newsgroups and
even to microsoft engineers.
Finding that the troubleshooting methods involve trying many storage cards
and/or a few hard resets, which is just not appropriate to ask an end
consumer to do. (remember, it always works on my devices).

One option is to move to SQL Mobile and .NET 2.0. This means deprecating
Pocket PC 2002 customers, of which we have some. I know there are
new/different SQL Server synchronizing features; I don't plan to use
synchronization at all in the future. I'd prefer being able to write a CE
database file on the desktop to synchronization any day. The new
SQLCEResultSet sounds interesting, if it is a replacement for using
TableDirect as I have done in one place.

If SQL Mobile is more robust, that might be a reason to use it. More
important than features for me right now. But I want to hear from others who
have some actual experience with it. It may not have had the test of time
yet.

If not, the other alternative are third party databases for Pocket PC usable
through .NET. Feel free to post about some and self promote if you develop
one. Besides the robustness mentioned, other important features for a
database are image (binary) data type and indexing. Conversion from SQL CE
if I am moving away from it is also nice.

Nathan

 

[Christian Schwarz]

If not, the other alternative are third party databases for Pocket PC

usable through .NET. Feel free to post about some and self promote if you
develop one. Besides the robustness mentioned, other important features
for a database are image (binary) data type and indexing. Conversion from
SQL CE if I am moving away from it is also nice.

Maybe the SQLite database (http://www.sqlite.org) is what you are looking
for.

1. It's written in ANSI-C, open source and completly free of charge (public
domain).
2. It supports most of the SQL92 standard.
3. The database files are portable (i.e. between dektop and mobile device).
4. It's easy to deploy as it usually consists of one dll containing the
database engine (sqlite3.dll) and maybe a .NET assembly containing the ADO
..NET stuff.
5. There's a free ADO .NET Data Provider
(http://sourceforge.net/projects/adodotnetsqlite) available.
6. It supports BLOBs (binary large objects) and of course indexing.

Greetings, Christian

 

[Ginny Caughey [MVP]]

Nathan,

SQL Mobile is indeed more robust than SQL CE 2.0, and there are advantages
to moving to CF 2.0 in any case. SqlCeResultSet does give you direct table -
type access, but the result set is also data bindable. The data syncing
options for SQL Mobile are improved over SQL CE 2, but you really don't have
different ones - just ones that work better. If you desktop machine is a
TabletPC, you could even use SQL Mobile on the desktop to read and write the
database, so that is new, but SQL Mobile isn't licensed for desktop use in
general - just for development and in the special case of TabletPC operating
system.

I'd suggest just trying a conversion to SQL Mobile and see if you are
satisfied. And if not, there are 3rd party options.

 

[Markus Humm]

Hello,

mimer from Sweden might be an option as well.

Greetings

Markus