Database migration from Pocket Access to SQL Server CE

Summary: This article discusses the issue of migrating a Pocket PC application that will be written with eMbedded Visual Basic to the. NET Compact Framework. Migrations include changing data from the Pocket Access database using ADO CE to accessing data in SQL Server CE using ado.net. This article provides sample code for both Visual Basic and C # two versions.

Please download the Download Database migration from Cedb to SQL Server ce.msi from the Microsoft Download Center.

Brief introduction

Whether you've decided to migrate applications and data from previous Pocket PC tools (Microsoft eMbedded Visual Basic and Pocket Access) to your current technology (Microsoft. NET Compact Framewor K and Microsoft SQL Server CE, or are you considering this decision, you need to study some of the reasons for this migration's difficulties.

The first reason is that your device will improve performance. Performance is undoubtedly important on the device processor, even the most current and best processor. When the database is set correctly in SQL Server CE (with the appropriate key, index, and so on), its performance will be completely over the Pocket access-sometimes over many times.

The second reason is that richer type systems in SQL Server CE will enable your device to store data more efficiently. Everything you can store in Pocket Access can be stored in SQL Server CE (more than that). Synchronization is also easier because the type system is a clean subset of the type system of SQL Server 2000.

The third reason is that the current data access middleware (ado.net) will provide you with more functionality so that you can read and manipulate the data itself in addition to the definition of manipulating data. The new and disconnected model (indicated by the DataSet) gives you more freedom in how data is used. A dataset can be sent to an XML Web service (the dataset type will be naturally supported when you create an XML Web service through asp.net). You can store the DataSet as XML (containing XML schema information, or in a separate file) to the file system (you can reread the XML from the same file to the DataSet).

The last reason is that, in addition to data manipulation, richer SQL syntax allows you to have more choices when reading data (using subqueries, outer joins, and so on). Previously, you needed to implement a lot of logic in the code after you retrieved the Recordset, and now you can do it directly in the SQL command that it belongs to. In the previous code, complete such as through LTRIM or case ... When ... THEN ... ELSE ... The end statement to format the columns in the SELECT statement is much more difficult and usually slower.

When migrations from previous Pocket PC applications involve data and code migrations, you can perform migrations at the following three different levels:

•	Database
•	Database middleware
•	Source

At the database level, you can migrate data and code from Pocket Access to SQL Server CE. The major differences are related to the supported data types. When you migrate data access code, the differences in database middleware are primarily related to supported objects and the syntax for using those objects. Typically, when you migrate source code, differences are associated with different languages and supported class libraries. The following sections describe the differences between different levels.