This project is read-only.

[ Main documentation resides on the Microsoft Interop Team Windows Phone app migration site]


Introduction
With the release of Windows Phone Mango and its new support for relational databases through Microsoft SQL Server Compact Edition (SQL CE) we decided to create a tool to aid developers in porting their mobile applications to Windows Phone from other platforms. Differences in data types between various database platforms can make the manual migration of your data a daunting and frustrating task. SQLite2CE is a tool developed to make this process simple by converting a SQLite database into SQL CE while simultaneously creating all the classes needed to easily incorporate the new database into your Windows Phone application.

Prerequisites
In order for the SQLite2CECmd tool to function correctly you’ll need to have the following packages installed on your system prior to use:
• Microsoft .NET Framework 4.0, which comes with the Windows Phone SDK (http://create.msdn.com/en-us/home/getting_started )
• Microsoft SQL Server Compact 3.5 SP2 http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5783
(Do not install SQL Server Compact 4.0)

Description and Settings
Download the SQLite2CE package and extract the archive to a location on your system. Within the SQLite2CEBin directory you’ll find SQLite2CECmd.exe, this is the command-line tool for converting your SQLite database to SQL CE.
Open a command prompt and navigate to the directory you just extracted. Run SQLite2CECmd.exe and you’ll be presented with usage documentation as shown here:

Usage:
SQLite2CECmd --v --f --sv --st --sd -i=<input db file> -o=<output db file> -r=<report file> -s=<settings file>

where
--v: Produce verbose output report
--f: Overwrite output database if it already exists
--sv: skip views i.e. do not convert any views that may exist in the database
--st: skip triggers i.e. do not convert any triggers that may exist in the database
--sd: skip data i.e. do not convert any data that may exist in the database
-i: full path to input database file (SQLite database)
-o: full path to output database file (SQL CE database)
-r: full path to output report file
-s: full path to input settings
Only the -i and -o parameters are mandatory. Rest of the parameters are optional.
e.g. SQLite2CECmd --f -i=c:\test\test.sqlite -o=c:\test\test.sdf

As described above, operation of the tool is simple in that all it requires is a path to the original SQLite database and a path in which to create the new SQL CE database. However, you have the option of being more involved in how your database migrates various data types and the level of detail returned during the migration process.
Within the SQLite2CEBin directory there exists a file named “settings.xml” that can be altered to indicate how certain SQLite data types are mapped in SQL CE. Opening this settings file will reveal default values which are a limit on the maximum number of NChar characters (500), a mapping from integer (SQLite) to bigint (SQL CE), and a default data type mapping of image (SQL CE) if no other mapping is deemed appropriate by the tool. These three settings are user configurable should your database require mapping other than the recommended default. To use alternate settings, make the changes within the settings.xml file and then, when running the tool, specify the path to your altered settings file using the –s flag.

KNOWN ISSUES:

1. From Issue 11634 It is known that conversion of database with a composite key with nullable column may fail.

       SQLite DLL identifies such a column in a composite key as not null. This is related to different interpretations for UNIQUE constraint for nullable columns in SQLite and Microsoft SQL server. 

Last edited Feb 25, 2012 at 2:56 AM by abub, version 3

Comments

No comments yet.