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:

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:

 

WP7.1 LocalDBDemo - Visual Studio 2010 Command Prompt

 

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:

 

WP7.1 LocalDBDemo - Create DataBase Context

 

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

 

WP7.1 LocalDBDemo - DataBase Context Created

 

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:

 

WP7.1 LocalDBDemo - Make NorthwindDataContext class Compatible

 

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.


11 comments

  1. Good one. Waiting for your next chapter for further reading.
    Thank you Kunal for sharing such a nice tutorial series.

    While searching on Google, I came to your blog. It's awesome. Started following.

    Keep up the good work.

    ReplyDelete
  2. thanks for this post.
    after entering the command in the command prompt it keeps telling me i access to the file is not allowed.

    ReplyDelete
  3. Can you tell me what is the error that you are facing? Try opening the Visual Studio Command Prompt in "Administrative Mode" if you are facing any access related issues.

    ReplyDelete
  4. Kunal, I just found you, and your examples are great! I would be interested in seeing an article on creating a local database from scratch, rather than using Northwind.

    ReplyDelete
  5. Error : The database file may be corrupted. Run the repair utility to check the
    database file. [ Database name = \\?\C:\pindata\pincode2.sdf ]

    ^^ iget this error and the database is not corrupt

    ReplyDelete
  6. Error : The database file may be corrupted. Run the repair utility to check the
    database file. [ Database name = \\?\C:\pindata\pincode2.sdf ]

    ^^ iget this error while the database is not corrupt

    ReplyDelete
  7. What do you do, if you are trying to create a brand new DB? Meaning I'm not using Northwind, I want to create something from scratch. What would I need to do?

    ReplyDelete
  8. This is actually the only demo I've been able to find using an existing database. All the other samples I've looked at want to either create a database from scratch or seed it with a ton of hand coded data. However my DB is evidently SQLCE 4 and when I run the sqlmetal command it seems to want to work with 3.5. Is there a way to do this sample with a SQLCE 4 database?

    ReplyDelete


 
© 2008-2014 Kunal-Chowdhury.com | Designed by Kunal Chowdhury
Back to top