Wednesday, April 10, 2013

Generate a Windows Phone 8 Local Database DataContext from an existing database

Despite my tooling for this having been available for more than 18 months, I have never posted a dedicated blog post for this subject. This post intends to remedy this.

The Local Database (a SQL Server Compact database accessed via LINQ to SQL) is a data access API available on Windows Phone 7.5 and 8.

The Microsoft documentation (listed here on my blog, together with many other useful Windows Phone Local Database links) always describes a Code First workflow, which makes it cumbersome to reuse existing effort in creating a SQL Server Compact database schema for Windows Mobile or desktop, and  also makes it hard to distribute a database prepopulated with data together with your app. My tooling, which is available with the SQL Server Toolbox Visual Studio add-in, and also available in a simpler form with the new standalone edition of the Toolbox for SQL Server Compact 3.5 (currently in alpha), enables both scenarios. The standalone edition is useful for VS Express users and when you do not wish to install Visual Studio on a PC (it is a single .exe file, so very simple to distribute)

In the following walkthrough, using Visual Studio 2012, I will demonstrate how to use the SQL Server Compact Toolbox to take an existing SQL Server database and use it as an included Windows Phone database in an new (or existing) Windows Phone 8 App. The process to do this requires these steps:

- Create the SQL Server Compact database from the server database and add it to the Windows Phone project
- Generate the LINQ to SQL DataContext and releated classes.
- Use the database from code

I assume you have Visual Studio 2012 Pro or higher with the Windows Phone 8 SDK installed.

Create the SQL Server Compact database

I have created a new Windows Phone Databound App for this sample, and selected Windows Phone OS 8.0 as the target OS.

image

I then use the Toolbox to create a new SQL Server Compact 3.5 database in the folder where the Phone project resides, (you can determine the folder from by using the “Open Folder in File Explorer” context menu item).

image

I then click Create, navigate to the project folder, and type PostCodes.sdf, press OK.

image

Click OK, and a new, empty database will be added to the database list in the Toolbox:

image

Now we need to connect to the SQL Server database, and script it, then run the script against the new, empty database.

image

Create and save the database script using the Toolbox menu item above, and then open the SQL Editor against the PostCodes.sdf database file:

image

Use the Open button in the editor to load the script, and the press the Execute button to run the script.

image

Now the database contains a PostCode table (the script is available here), which has all Danish postcodes.

The final step is adding the database file to the Phone project. In Solution Explorer, select “Show all files”, and include PostCodes.sdf. In this sample scenario, we would like the database to become writable on the Phone, so include it a “Embedded Resource” – it could also be included as Content, if it was just a read-only database, read more here.

image

 

Generate the LINQ to SQL DataContext

In order to generate the DataContext based on the database, right click it in the Toolbox, and select “Add Windows Phone DataContext to current project”.
If this menu item is disabled, verify that the database file is in 3.5 format, and that the SQL Server Compact 3.5 SP2 runtime is properly installed, you can check this via the About dialog. “Yes” is required in both places, if that is not the case, repair/re-install.

image

image

image

Let’s walk through the various options on this dialog:

Context name: The name of the generated DataContext class

Namespace: Allows you to specify another namespace for the generated code

Language: You can generate C# or VB code.

Pluralize: If checked, will rename tables (Person => People) etc.

Create a file per table: Normally, just a single file is created

Advanced options:

Add schema version table: If you would like to include the database file a a writeable file, and allow use of the DatabaseSchemaUpdater class in a future app version select this option .

Add rowversion column to all tables: Checking this will ensure that all tables have a rowversion column (formerly timestamp), which enhances performance when doing UPDATE and DELETE (see my blog posts here and here)

Include ConnectionStringBuilder: Will add a LocalDatabaseConnectionStringBuilder class to the project, to help with building connection strings in a strongly typed fashion.

For this sample project, just click OK, and a PostCodesContext.cs file will be added to the project, and we are done.

image

Use the database from code

Finally, to demonstrate that we are able to include data with the app, alter the DataTemplate in MainPage.xaml as follows:

<DataTemplate>
  <StackPanel Margin="0,0,0,17">
      <TextBlock Text="{Binding Zip}" TextWrapping="Wrap" Style="{StaticResource PhoneTextExtraLargeStyle}"/>
      <TextBlock Text="{Binding City}" TextWrapping="Wrap" Margin="12,-6,12,0" Style="{StaticResource PhoneTextSubtleStyle}"/>
  </StackPanel>
</DataTemplate>

Replace the OnNavigatedTo event handler in MainPage.xaml.cs with the following code:

        protected override void OnNavigatedTo(NavigationEventArgs e)
{
using (PostCodesContext ctx = new PostCodesContext(PostCodesContext.ConnectionString))
{
ctx.CreateIfNotExists();
ctx.LogDebug = true;
MainLongListSelector.ItemsSource = ctx.PostCode.ToList();
}
}






This code initialize a new PostCodesContext instance (embraced in “using”, as it is Disposable). The CreateIfNotExists method extracts the PostCodes.sdf embedded resource from the project, and copies it to isolated storage (feel free to look at the code). Setting LogDebug to true will show all SQL statements as text in the Debug window while debugging:



SELECT [t0].[Id], [t0].[Zip], [t0].[City], [t0].[Street], [t0].[Company], [t0].[IsProvince], [t0].[rowguid] AS [Rowguid], [t0].[ts] AS [Ts]
FROM [PostCode] AS [t0]



And finally, calling ToList() will execute the SELECT and return a list of PostCode objects, that is the bound to the ItemsSource property of the LongListSelector.



Result:



pc



Summary



Let us finish with a summary of advantages of this approach:
- Use desktop database tools for data population and schema design
- Saves time doing 1:1 mapping between database tables and DataContext classes
- DataContext class and entity classes are partial and can be extended
- The generated DataContext contains Index definitions (which SqlMetal does not support, as this is a Windows Phone extension)
- The generated DataContext contains the CreateIfNotExists method, that optionally extracts an included database (prepopulated with data) to Isolated Storage
- The generated DataContext includes the LogDebug property, that allows you to see all SQL statements generated on the device in the debug window
- Optionally split the generated Data Context classes into multiple files
- Optionally add a Version table if you include the table with your app, and want to enable use of the schema updater functionality.
- Optionally add rowversion columns to improve UPDATE and DELETE performance
- Optionally include a ConnectionStringBuilder class to build a valid connection string in a strongly typed way,  using advanced connection string options (see some of my Phone blog posts for candidates)



Hope you find it useful.


UPDATE: Comments have now been closed, please contact me for any issues via the Codeplex issue tracker here: http://sqlcetoolbox.codeplex.com/issues/list

44 comments:

roschler said...

Excellent walkthrough. Minor typo:

"I will demonstrate how use the SQL"

-> "I will demonstrate how to use the SQL"

-- roschler

ErikEJ said...

Thanks - and corrected

Kemal Serkan YILDIRIM said...

I made all step from begining to
Generate the LINQ to SQL DataContext step.
But I cant make DataContext for the Database. I can seee the option on the right click menu but it isn't enabled.

Why it can be ?

ErikEJ said...

Kemal: I have updated the blog post, explaining why the menu item might be disabled.

Hristijan said...

Hello. First of all this was very useful to me thank you very much. I am working on a school project and i have been searching the internet all the time still nothing to find useful for me to get started with inserting, updating or deleting data in the database. I was wondering if you can give me some links for that where i can see code examples. Thank you

ErikEJ said...

I have many links here: http://erikej.blogspot.dk/2011/07/windows-phone-sql-server-compact.html for example the Windows Phone Geek tutorials

Hristijan said...

Hello again :) I am sorry for bothering you. The link you gave me was very useful thank you. I have just one more question. I followed your steps with the PostCode database and it worked very well. The problem is that, now when i use SubmitChanges() it gives me error and i was wondering if their is read-only property to database or something. When i delete SubmitChanges() it works well but can't save the entry into the database

ErikEJ said...

No problem, buy suggest you post a question on the MSDN forum or Stackoverflow with a code sample and an error messsage - these comments are not the best forum for support

Asif Rahman said...
This comment has been removed by the author.
ErikEJ said...

Asif: No, I cannot based on the Little your are telling me - what is not working, what is the error message etc. Suggest you ask a question on MSDN forum or Stackoverflow

Nishumvar said...

Hey Erik, cool tool, but I have some issues:

The toolbox plugin first connects to the server and produces 26 separate 18MB files of insert statements across the tables, which is fine.
The problem is that there seems to be an exponential read time involved in importing these files.

I tried breaking the files into 1/100 the size, followed by 1/10, 1/4 and then 1/2. I found that it took roughly 3 seconds for the first break up, 30 seconds for the 1/10, 4 minutes for the 1/4th, and it crashed on the 1/2.

Am I doing something wildly wrong? I'm using VS2012 for the plugin, as well as Sql Server 2012 for the original database, but I've also tried using the standalone 3.5 exe and produced the same results.

ErikEJ said...

Nishumvar: I think you are maybe including too much data on the Phone... But to get better performance, do not use the editor, but rather use my sqlcecmd command line Tool or my SqlCeBulkCopy library to add the data to the file, both options do not load the entire file.

Nishumvar said...

Thanks for the suggestion on using the command line tool, it worked perfectly.

As for the amount of data I'm trying to work with, a really professional competitor application on the iPhone has a database roughly 2 to 3 times the size of mine, and is way quicker with its queries than I've managed to achieve so far. I've been using Peter Huene's sqlite-net-wp8 implementation, but I'm hoping an sdf formatted database will be faster. I believe it's closer (is?) to the native format that WP8 employs.

ErikEJ said...

Nishumvar: Make sure to use the appropriate indexes to help the critical queries,

Arun said...

Hi Erik,

I used the toolbox to generate a context from an existing sqlce database that was working on a netcf app on winmo 6. It generates the context fine. But it fails on deploy with an "Unspecified error". If I hit continue it gives the error "A specified communication resource(port) is already in use by another application". Is there anyway to debug whats going to wrong.

ErikEJ said...

Is it sqlce related? Con you deply a new empty app?

Arun said...

Yes I can deploy empty apps and also samples from MSDN.

ErikEJ said...

Suggest you report an issue on Codeplex, and attach a repro project...

Unknown said...

Hi, can you upload/send me your solution folder for this project.
my email: seng177@gmail.com

I try to follow you post but stuck at:

"Replace the OnNavigatedTo event handler in MainPage.xaml.cs "

my "MainPage.xaml.cs" don't have OnNavigatedTo event handler.

ErikEJ said...

Winson - start with the Databound App Phone 8 template

michael said...

I'm quite confused ... (I've never developed a WP8 App - but I want to start soon)

Is SQL CE supported? I thought Windows Phone is a non-X86 architecture and it wont work?

ErikEJ said...

Micheal: SQL Srver Compact supports more than 8 CPU architectures

michael said...

Hmm. But why doesnt it work on Windows RT? RT und Windows Phone are quite similar? (ARM architecture)

Also read about SQL CE Merge Replication in Rob Tiffany's book. I wonder how MS wants to support all this use cases described there if Merge Replication doesnt work anymore.

I'm confused ;)

ErikEJ said...

Michael: It is called Development :-) - WinRT is solely async, and that would require a full rewrite of the SQLCE engine, and ADO.NET is not available with Windows Phone - you can use WCF/OData Services and json for Sync instead (requires own programming)

michael said...

Thanks a lot. Will give WinRT a try anyway ... Lets see ;-)

But at the moment it seems to be very dissapointing ...

Unknown said...

Thanks a Lot :)

can you please tell me How can I search Data like Zip/city from this
Post Code table ??

Thanks in Advance :)

ErikEJ said...

Harisul: just use linq - ctx.PostCodes.Where(p => p.City == "mycity")

Unknown said...

when I add LINQ to SQL DataContext to current Project, it show a message :The selected project does not support .net Desktop - wrong targetFrameworkMoniker: Windows Phone, version=v8.0
:(

ErikEJ said...

Ken: I think you are using the wrong menu item.

Eric said...

I have a solution to the error: "The selected project does not support .net Desktop - wrong targetFrameworkMoniker: Windows Phone, version=v8.0"

It appears to be a problem because the selected project was a WP8 Class Library project. So, I added a regular Windows Class Library project to the solution, selected it and re-tried the operation. It worked this time. I copied the generated code into my WP8 class project and I was able to compile successfully. Although, first I had to remove a couple overloaded constructors that contained references to IDbConnection (not supported in WP8). I had to jump through some hoops but Linq-To-Sql is definitely possible in a WP app. So much nicer than EF, IMHO.

ErikEJ said...

Eric: Please use the "Add Windows Phone DataContext" menu item - it adds many useful features to the generated code.

Vito said...

Hello ErikEJ,
i have a WP8 and Visual studio 2012. SO: Windows 8. I have on PC only SQL CE 4.0
Use the version 3.5 is not a technique obsolete?
It is not possible use 4.0?
Thanks

ErikEJ said...

Vito: No, only version 3.5 files rsupported by Windows Phone, just install the desktop runtime to ue the tooling.

Vito said...

hello,
in my emulator application works and create database. If i restart my Windows phone application database has been deleted and re-creates. Why?

Eric said...

Using Linq-to-Sql to access a local db, when I run a simple query, I get the following error:

"The specified table does not exist. [Products]"

Here is the code that fails:

List ret = new List();
NewProductsContext pc = new NewProductsContext(ConnectionString);

ret = (from prod in pc.Products
select prod).ToList();
return ret;

The error is thrown when I call the query expression.

If I put a break point before the query expression and inspect the 'pc' variable, it has all the tables that are supposed to be there.

ErikEJ said...

Vito - everythng is erased when you shut Down the emulator

ErikEJ said...

Eric - did you run CreateDatabase or CreateIfNotExists ?

Vito said...

you're right it's true. createifexists call. I tried your example

Eric said...

Erik,

The db was created outside the app. It's included in the solution as an embedded resource. On startup, it checks isolated storage to see if it exists, if not, it's copied there from the installation folder. After the db is copied, the first action is to open it and read the contents. This is where the error is thrown.

ErikEJ said...

Eric, you did not answer my question, suggest you provide a detailed repro via the codeplex issue tracker at sqlcetoolbox.codeplex.com

Eric said...

"Eric, you did not answer my question..."

Sorry about that, I did not make a call to either CreateDatabase or CreateIfNotExists.

All database creation was done separate from the application.

ErikEJ said...

Eric - awaiting your repro and more details at the issue tracker at sqlcetoolbox.codeplex.com

Unknown said...

I try to reuse my database, tool generates context successful. But when I debug app in my device, the database is empty. I cannot get any record, but I can InsertOnSubmit() and get added record.

Why database is clear empty? How can I solve it? Thank you

ErikEJ said...

Tan: Suggest you contact me via the issue tracker on CodePlex with a repro project