One of the biggest complaints (from myself and others) about V1 of WP7 was the total and complete lack of a built in database (although v1 of WP7 did have SQL CE on the device but developers did not have access to use it). When I say there was no built in database don’t misread that to say there is no data storage support cause there is but it is in the form of blob storage inside of the Isolated storage area. This type of storage worked and some great libraries were built on top of this storage to give your app SQL like abilities, but non of them gave you a true SQL experience.
With the upcoming release (this post is based of the Beta build of Mango) of WP7 Mango developers now have full access to SQL CE inside your application. This means that you can do many cool things. One of the really cool things that can be done w/ this SQL access is the ability to import/export your .sdf (SQL CE database file) file in order to view the data inside of SQL Management studio or Visual Studio.
In this post I will walk you though what is needed in order to use the Isolated Storage Explorer tool and how to use this tool to export your .sdf file to your hard drive. I will than walk you through how to connect to that .sdf file via Visual Studio in order to run queries against the data.
Step 1: Running the Isolated Storage tool
This tool is NOT a GUI based tool but rather a command line tool, but it is not hard to use or to learn.
- Open up a command prompt and navigate to c:\Program Files (x86)\Microsoft SDKs\Windows Phone\v7.1\Tools\IsolatedStorageExplorerTool
- In order to extract your data file you will want to run the following command:
ISETool.exe ts xd GUIDGOESHERE PATHTOEXPORTGOESHERE
Let me break down this command in a little more details
- ISETool.exe is the commandline too to work with Isolated Storage
- ts is the command to instruct the take a snapshot of the Isolated Storage data
- xd is the command to instruct the app to look at a running emulator (use de if you want to hit a real device)
- GUIDGOESHERE is the Application GUID for your WP7 application which can be found in your WMAppManifest.xml file
- PATHTOEXPORTGOESHERE is the full path to a folder on your hard drive to store the exported .sdf file
Step 2: Attach the .sdf file to a running instance of Visual Studio
You will want to open up Visual Studio and open the Server Explorer window/pane. Once you have this window open you will need to connect to your .sdf file as such:
- Click the Connect to Database button in the Server Explorer
- When you click the connect button you will need to add a connection. When the dialog below opens the default should be SQL Client. Make sure you click the change button and select SQL CE 3.5. Selecting SQL CE will change the dialog and you should populate it as you see below.
Step 3: Open the database and take a look at the schema
One you have attached your .sdf file you should now see the database attached to the Server Explorer. You should be able to see all the table schema information as below
Step 4: Query the data
Now that you have attached the database and you can see the schema the next logical thing to do is query the data. You can do this one of two ways. You can either right click on the table and choose ‘Show Table Data’ or you can create a new query and write custom sql. Below is the output of choosing to show table data.
As you can see using when using SQL CE on WP7 Mango it is very easy to export your .sdf file and view the contents of the file inside of Visual Studio. Having this ability is very powerful because it can give the developers the ability to run analysis on their data or inspect their data at any time to ensure they are heading down the right path.
Hope this helps.
Till next time,
07-19-2011 6:13 AM