SQL CE on Windows Phone 7.1 tips from an ISV (Linq, connection strings and creating/updating your DB's schema)

ComponentOne Community - Rich Dudley - Notes on Local SQL CE Databases and WP 7.1 “Mango”

"Introduction

In Windows Phone 7.1 Beta “Mango” tools, support for a local relational database in a client application was added. This database is based on SQL CE 4, and is stored in the application’s isolated storage (see image below). Databases need to be designed in a code-first pattern, using POCOs (plain old CLR objects) to define and create the database. It would be tempting to create a SQL CE 4 database in our project, and include it in the XAP, but then this database would be an embedded resource, not a local database, and XAP file size would be increased (not even our XapOptimizer minimizer/obfuscator could help you with the database). Since databases are not included with our application download, we need to include code to populate it with any seed data. The MSDN documentation for working with local databases starts at http://msdn.microsoft.com/en-us/library/hh202865(v=VS.92).aspx.

...

Linq-to-SQL is used to set the database context and query the local database in a Windows Phone 7 application. The Mango implementation of Linq-to-SQL is an incomplete version of that used in the full CLR. The implementation seems to be complete enough for most data-centric applications, but Linq-to-SQL masters might find some of the missing functionality a step backwards.

Because space is limited on a WP7 device, there are limits to our database’s size. The default maximum database size is 32 MB, and the maximum allowable size is 512 MB. If more space is needed, especially if images are involved, it might be time to think about storing data in Azure. Sharding might be possible, but probably wouldn’t be a best practice on a phone.

Linq-to-SQL on Windows Phone

...

Connection Strings and the Data Context

...

Creating the Database

...

Updating the Database

...

..."

I liked how this post brought together a number of SQL CE on WP7 points that we'll likely run into in the real world...