Monday, January 23, 2012

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:



image



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:



image



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.

11 comments:

Unknown said...

Hi, did the procedure for "reference database/read only" changed somehow?

I've seen a lot blog posts saying you need an auxiliary app to create and populate the datase, and them you copy this database to your machine and then you add to your primary application. Is that a fact? Cause is doenst make sense.

ErikEJ said...

That is still the MS recommended approach, I am just demonstrating some other (non-MS supported) approaches.

Kimleang said...

Dear Mr. ErikEJ
I'm new to Windows Phone developer I want to develop a simple dictionary application, I have read your tutorial and I try your Option 3: Database file included, read only, and I got one problem.

first I create a new default Windows Phone Project but I don't write any code yet, after that I add database (.sdf file) as Content then I build and deploy but I got an error: "a specified communication resource (port) is already in use by another application", but when I remove (.sdf) from project it work fine. I can't find out what the problem is.

the (.sdf) file is SQL Server Compact 4
and the project target to Windows Phone 8.0

Thank you for your reply Mr. ErikEJ and sorry If I have some bad English.

ErikEJ said...

Kimleang: The database must be 3.5 format, not 4.0 and how big is it?

Kimleang said...

Thank you for your reply Mr. ErikEJ

I have try 3.5 but it the same error I can't deploy it.
The database is still empty just only one table structure.

I'm not write any code yet I just create a default Windows Phone Databound App then add (.sdf) as Content and deploy it. It build success but deploy to emulator fail.

Here is my error build log:
1>------ Build started: Project: EKDictionary, Configuration: Debug Any CPU ------
1> EKDictionary -> c:\users\icool\documents\visual studio 2012\Projects\EKDictionary\EKDictionary\Bin\Debug\EKDictionary.dll
1> Begin application manifest generation
1> No changes detected. Application manifest file is up to date
1> Begin Xap packaging
1> Creating file EKDictionary_Debug_AnyCPU.xap
1> Adding EKDictionary.dll
1> Adding Assets\AlignmentGrid.png
1> Adding Assets\Tiles\FlipCycleTileLarge.png
1> Adding Assets\Tiles\FlipCycleTileSmall.png
1> Adding Assets\Tiles\IconicTileMediumLarge.png
1> Adding Assets\Tiles\IconicTileSmall.png
1> Adding DicData.sdf
1> Adding AppManifest.xaml
1> Adding Assets\ApplicationIcon.png
1> Adding Assets\Tiles\FlipCycleTileMedium.png
1> Adding WMAppManifest.xml
1> Xap packaging completed successfully
2>------ Deploy started: Project: EKDictionary, Configuration: Debug Any CPU ------
2>Deploying c:\users\icool\documents\visual studio 2012\Projects\EKDictionary\EKDictionary\Bin\Debug\EKDictionary_Debug_AnyCPU.xap...
2>Connecting to Emulator WVGA 512MB...
2>Installing the application...
2>Deployment of application to device failed.
2>Unspecified error
========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========
========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

ErikEJ said...

Kimelang: I have no idea, maybe test on another PC?

Kimleang said...

Thank Mr. ErikEJ for your comment
After few days and I have re-install my operating system I found out the problem.
The problem is with my database have password protection.

I can read content from database only if my database don't have password and build it as Content.
and this is my working connection string for database build as Content: @"Data Source = 'appdata:/DataRef/DictDataDB.sdf';File Mode = read only;";

I can't use database with password as Content in read-only mode.
if I use database with password as Embedded Resource. I got the error message "a specified communication resource (port) is already in use by another application"
if I use database without password as Embedded Resource. I got error The path is not valid. Check the directory for the database. [ Path = C:\Data\Users\DefApps\AppData\{D273290A-7FB8-4D5E-AB5E-A89B8AE9F39C}\Local\DataRef/DictDataDB.sdf ]
and this is my connection string for database build as Embedded Resource Database: @"Data Source = 'isostore:/DataRef/DictDataDB.sdf';";

what should I do Sir if I want password for my data?

ErikEJ said...

Kimelang: You CANNOT include a password protected file, as I have described here: http://erikej.blogspot.dk/2012/03/windows-phone-local-database-tip.html

Kimleang said...

Thank you very much sir for your great help now I know about.
Thank Mr. ErikEJ

BinhDanHocVu said...

Dear Mr. ErikEJ
I have a question about database of local in winphone 8.Everything i do as your tutorial is very good.and now i want to backup my data after I inserted new field or updated field,But i don't know good idea for its.So can you tell me Where is my file .sdf in my phone and how can i get it to update or backup database.Thanks so much.

ErikEJ said...

Bin: Suggest you ask in a forum, and start by sharing your connection string