Click here to Skip to main content
15,867,453 members
Articles / Mobile Apps / Windows Phone 7
Article

Extracting a SQL CE DB from Isolated Storage in WP7 Mango

Rate me:
Please Sign up or sign in to vote.
4.78/5 (9 votes)
3 Sep 2011CPOL4 min read 33.6K   17   4
A look at how to extract a SQL CE DB from Isolated Storage in Windows Phone 7 Mango

Introduction

By now, most of you have heard that Windows Phone 7 – Mango release will support Local Databases (SQL CE) using Linq to SQL. But what you probably haven’t heard much about is how to extract the .SDF that is created in isolated storage to your local computer and view the contents. I find this extremely important for debugging application and making sure my database is setup exactly like I want it. So, that is what we are going to do today.

Getting Started with a Sample Application

Image 1

For this tutorial, we will need a sample application that uses a local database. If you are already using a local database (SQL CE) in your Mango application, then you can skip this section, otherwise go ahead and download the “Local Database Sample” from MSDN.

Below is an excerpt from the MSDN Page:

On Windows Phone OS 7.1, you can use LINQ to SQL to store relational data in a local database that resides in your application’s isolated storage container. This sample is a to-do list application that uses a multi-table local database. Items that appear in the list are added, updated, and deleted from a local database, where they will persist between application launches. For step-by-step information about how to develop this application, see How to: Create a Local Database Application with MVVM for Windows Phone.

Download samples: C# | VB

The Database Structure

We see code that defines our database name in App.xaml.cs. It is simply called ToDo.sdf:

C#
// Specify the local database connection string.
string DBConnectionString = "Data Source=isostore:/ToDo.sdf";

And code that creates a table and defines the columns:

C#
[Table]
public class ToDoItem
{
    [Column( IsPrimaryKey = true )]
    public int ToDoItemId { get; set; }
 
    [Column]
    public string ItemName { get; set; }
 
    [Column]
    public bool IsComplete { get; set; }
 
    [Column]
    public int _categoryId { get; set; }
 
    [Column]
    private Binary _version { get; set; }
}

Please note that I removed all code relating to MVVM to simplify this example. If you wish to see the code, then please review Model –> ToDoDataContext.cs. This sample also has another Table called ToDoCategory that was not included in this tutorial.

Now that we have reviewed what the database looks like, let's see what it takes to extract the .SDF file from isolated storage to our local pc.

Get the required tools if you haven’t already.

  1. Download the Windows Phone SDK 7.1 RC and install it if you already have not.
  2. Grab the Silverlight Toolkit for Mango.
  3. Download the “Local Database Sample” from MSDN if you want to use this example.

Go ahead and run the application and enter some data. My screen looks like this:

Image 2

This should be enough data for us to work with.

Leave the emulator running and navigate to the Properties –> WMAppManifest.xml file. Open up notepad and copy/paste the ProductId into it.

Image 3

Go ahead and navigate over to your “C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v7.1\Tools\IsolatedStorageExplorerTool” and run the following command:

ISETool.exe ts xd 8c0be8e6-11c1-44d8-be92-57f06cf52240 
	"C:\Users\MichaelCrump\Desktop\IsoStore"

You should:

  • Replace the GUID with your own.
  • Give it a proper file location to output the file.

Here is what my command window looks like:

Image 4

Note: I kept getting this message that is was already being used but it actually built the file anyways.

Navigate over to the directory that you specified earlier and you will see your .SDF file from IsolatedStorage. Pretty cool huh?

Image 5

Go ahead and switch over to Server Explorer inside of Visual Studio 2010 and let’s add that SDF to the project. Right click on Data Connection, then select “Add Connection”.

Image 6

Make sure you select the Data Source to be: SQL Server Compact 3.5 and then hit Browser and select the .SDF file just created.

Please note that I could not get SQL Server Compact 4.0 Data Source to work with this sample.

Image 7

Hit OK and you should see your database that you crated in Mango (that was living in Isolated Storage) inside of Visual Studio.

Image 8

Now if you right click on a table, then you can “Show Table Data” kind of like we are used to with normal SQL Server DBs.

Image 9

Exactly the data shown in the first screen shot!

Image 10

One thing to note here is that this is simply a way to VIEW what was in isolated storage. You may want to read up on modifying IsolatedStorage using the same tool if that is your intention.

Conclusion

Of course, the IsolatedStorageExplorerTool is for more than SQL CE DBs. Basically anything in Isolated Storage in your app it can extract. It also works with devices as well as allows you to add files to IsolatedStorage from your local PC. With every release of Windows Phone, we are seeing the tools become better and better. I am very excited to see what is coming in future releases. I hope this helps someone out there troubleshooting a bug with their Windows Phone 7 application. Until next time, thanks again for reading.

Image 11Subscribe to my feed

Image 12

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Telerik
United States United States
Michael Crump is a Silverlight MVP and MCPD that has been involved with computers in one way or another for as long as he can remember, but started professionally in 2002. After spending years working as a systems administrator/tech support analyst, Michael branched out and started developing internal utilities that automated repetitive tasks and freed up full-time employees. From there, he was offered a job working at McKesson corporation and has been working with some form of .NET and VB/C# since 2003.

He has worked at Fortune 500 companies where he gained experience in embedded systems design and software development to systems administration and database programming, and everything in between.

His primary focus right now is developing healthcare software solutions using Microsoft .NET technologies. He prefers building infrastructure components, reusable shared libraries and helping companies define, develop and automate process standards and guidelines.

You can read his blog at: MichaelCrump.net or follow him on Twitter at @mbcrump.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert21-Sep-12 1:25
professionalKanasz Robert21-Sep-12 1:25 
Interesting idea
QuestionCan not copy DB Pin
kudresov15-Feb-12 5:23
kudresov15-Feb-12 5:23 
QuestionQuick Question....and this is a great article Pin
hometownnerd4-Feb-12 13:01
hometownnerd4-Feb-12 13:01 
GeneralMy vote of 5 Pin
mkrain28-Dec-11 7:04
mkrain28-Dec-11 7:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.