Mango Samples: SQL Toolbox

In recent posts I discussed two ways to add a local database to your Windows Phone applications. 

1. The first was code-first. This decorates classes and creates the database programmatically.
2. The second was SQL Metal. This command-line tool generates the classes for data access.

In this post, we will discuss Option 3. I want to show how the free SQL Server Compact Toolkit can add a database.

Step 1: Install the SQL Server Compact Toolbox

Using the Extension Manager (under the TOOLS menu in Visual Studio) search Online for the SQL Server Compact Toolbox. Download, install, and restart. Alternately find it here.

SQL Server Compact Toolbox is not a Microsoft product. It was created by Erik Ejlskov Jensen, a SQL Server Compact MVP in Denmark. The Toolbox is on CodePlex; it’s an add-in for Visual Studio 2010 and is completely free for you to enjoy

We’re focusing on it’s ability to: “Generate a Windows Phone DataContext (.cs) in the current project”. Since this is not (yet) built into VS and can only be accomplished in code behind or with SQL Metal, a UI-oriented add-in is a nice developer’s friend.

Step 2: Open the Toolbox Task Pane

To use SQL Compact Toolbox, open the SQL Server Compact task pane inside Visual Studio. Find the option in the TOOLS menu. It looks a lot like “Data Connections:”.

Step 2: Create the Database

As I showed in my previous post, create your compact database using Server Explorer, then edit schema and data. Once your database is “just right”, then the SQL Server Compact Toolbox is ready to step in and help create the Data Context.

Remember, Windows Phone supports only SQL Compact 3.5 not 4.0 (which is what you select for desktop applications).

Also, the Toolbox can create databases, too. It gives access to the create database wizard. The resulting database is identical. Still edit the schema from Server Explorer.

Step 3: Generate your Data Context

Find your database in the Toolbox’s list of databases (if there is more than one). Then select “Add Windows Phone DataContext to current Project” in the context menu.

Remember: Have your Windows Phone Visual Studio project open and ensure it is has the selected focus inside Visual Studio 2010.

For me, since I am simple, I chose all the defaults in the dialog. After clicking Create MyDataContext.cs was generated and added to my Windows Phone project.  Cool!

Here’s a step-by-step screencast:

One final note is an error in the video: I create my Users table and a UserId column but I do not designate that column as the primary key. In Linq to SQL, every table must have a primary key. Because I failed to do that, no Linq to SQL query would have worked. The solution is simple, be sure every table you create has a Primary Key.

Conclusion

There is *so* much more the SQL Server Compact Toolbox can do for you. I encourage you to check it out learn if you can benefit from its features (here).