Windows Phone 7 (Mango) Tutorial - 22 - Local Database Support, Create DataContext

Do you know that, Windows Phone 7.1 (Mango) now supports local database? Yes, you can now use local database inside your Phone 7 device and use it to CRUD operations. You can now create a DataContext class from an existing database and use it as the entry point class to do DB operation.

In this chapter we will learn how to create a DataContext class for local SQL CE database. At the end of this tutorial our data context class will be ready for further DB operations.

Index - Windows Phone 7 (Mango) Tutorial

Download SQL CE Database File

Before creating the DataContext file, you need an existing SQL CE database file. We will use "Northwind" database to demonstrate it. You can download the "Northwind.sdf" file from here:

• Download Northwind Database File for SQL CE (1,560 KB)

This database file comes with Visual Studio installation. If you already installed Visual Studio 2010 in your PC, you can get it from the following location too: "%ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Samples"

Create DataContext class

Once you already have the database file, you need to create a DataContext file from it. There you may have a question that "What is this DataContext file?". This is a class file which consists of all the CRUD operation based on the database to perform DB call. Once you have this file, you can easily query your database to fetch or insert records into that.

To do this, open the Visual Studio Command Prompt. This is available in the following location under Start menu:

Start -> All Programs -> Microsoft Visual Studio 2010 -> Visual Studio Tools -> Visual Studio Command Prompt

Here is the snapshot of the location, for your reference:

First of all we will copy the database file to a local directory (e.g. C:\NorthwindDB\Northwind.sdf) and enter the following command inside the command prompt. SQLMetal.exe which comes with Visual Studio installation, converts the sdf file to code based file which generates all the CRUD operations.

sqlmetal C:\NorthwindDB\Northwind.sdf /code:C:\NorthwindDB\Northwind.cs /language:csharp /namespace:LocalDBDemo /context:NorthwindDataContext /pluralize

Here we will create a CSharp file as I am comfortable with C# only. /language parameter as shown above creates the C# file. Your command window will look as below:

Press enter to continue. This will create the datacontext file from the .sdf file and show the following message after successfully complete:

Now, in the specified directory you will find the Northwind.cs file which consists of all the CRUD operations for the specific database file. Open the file and check what are the operations that it supports.

Remove unsupported Code

Last point is to remove the unsupported code from the file. IDbConnection interface is not supported by phone SDK. When I was compiling the same, I was actually getting some error with IDbConnection. Later found a similar post online which describes the same. You can find that post made by WindowsPhoneGeek. No doubt, that's an awesome article posted by him. I learnt some other points there too which I will be posting tomorrow (just to bookmark the same in my blog).

Now find out the following constructors inside the file and remove them one by one. Find the constructors as marked with a Red box shown below:

Save the file. Now your context class supports Windows Phone 7 application and once included in phone application project, will build properly.

In the next chapter, we will create a sample project and use this class to do database specific operations from your phone application. Stay tuned for the next chapter. Happy coding.