Windows Phone 7.5 - Local SQL Database：How to read the DB schema in windows phone 7

http://www.codeproject.com/KB/windows-phone-7/ExtractingSQLCEDB.aspx

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

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:

Collapse | Copy Code

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

And code that creates a table and defines the columns:

Collapse | Copy Code

[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:

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.

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

Collapse | Copy Code

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:

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?

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”.

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.

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

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.

Exactly the data shown in the first screen shot!

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.