Windows Phone Local Database tip: Initializing the database

There are several options for initializing the database for Window Phone, depending on your approach. By “initializing” I mean preparing the database for first use, just after the application has ben installed.

Whether you create the DataContext by hand or use RAD “Database First” with the SQL Server Compact Toolbox or the ExportSqlCe command line utility is not relevant.

You can read more about Database First here: http://erikej.blogspot.com/2012/01/generating-linq-to-sql-datacontext-with.html and more about Code First here: http://msdn.microsoft.com/en-us/library/hh202876(v=VS.92).aspx

I will assume you have used one of my tools to create the DataContext classes.

Option 1: No database file included

In this case, there is no initial data in the local database, it will be populated either by getting data from the web, or by manual entry by the user. The database must be writable, and must therefore reside in Isolated Storage. The connection string look like this: "Data Source=isostore:/Chinook.sdf"

To create the database based on the definition in the DataContext classes, you can use the following code, for example for each database call, or during app start:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    if (!db.DatabaseExists())
        db.CreateDatabase();
}
OR:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    db.CreateIfNotExists();
}

Option 2: Database file included, read and write

With this option, you include a database (.sdf file) in your project as en embedded resource:

You database must reside in Isolated Storage to be writable, and it must be copied to Isolated Storage as required. The connection string looks like this: "Data Source=isostore:/Chinook.sdf"

To copy the database to Isolated Storage, you can use the following code, for example for each database call, or during app start:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    db.CreateIfNotExists();
}

Option 3: Database file included, read only

With this option, you use the database for reference data only, and it can reside in the program files folder as a read-only resource. You include the database (.sdf file) as Content in your project:

You do not need to copy the database anywhere, it is installed with the other content in your application. The connection string looks like this: "Data Source=appdata:/Chinook.sdf;File Mode=Read Only;"

To connect to the database, use the following code:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionStringReadOnly))
{
    db.LogDebug = true;
    // More data access here...
}

I hope this article gave you an overview over the various options for database initialization with Local Database on Windows Phone.