Coding Paradox

Blog On .Net, CSharp, Windows Phone Programming

Using Local Database in Windows Phone Apps

without comments

In my previous posts, we looked at how to store data in Windows Phone Apps using Local Storage and Isolated Storage. In this post we will take a look at how to create a Local Database for storing data of our Windows Phone app. Database are great storage option when your app want to store some relational data. The database gets created into the local app folder and is only accessible to the same application.

We will be using the LINQ to SQL assembly and mainly the classes present in the System.Data.Linq and System.Data.Linq.Mapping namespace to create our database schema and perform CRUD operations on it.

In this demo, we will create a sample application that will store Students Information in the database. For each student, we will also store the courses that the student has taken. Lets get our hands dirty.

Open Visual Studio –> New Project –> Windows Phone Blank App and name the application “LocalDatabaseWPApp”.

Creating The Data Model

Right Click on The Solution and Add a folder called “Model”. Inside the Model folder, add two classes Student.cs and Courses.cs.

Our data model will consist of two classes which are “Student” and “Courses”. These two classes will also map to the tables in our database schema. The Student class will store student information like Student name and roll number while the Courses class will store the course information for each student. There will be a one to many relationship between the Student and the Courses class. Our Student class will look like below

The [Table] mapping attribute specifies that this entity will map to a database table in the local database. Each of our property are marked with [Column] attribute which will part columns of the database table. We can also specify additional properties in the [Column] attribute like “DbType”, “CanbeNull” or “IsPrimaryKey”. These can provide some more control on the column types that will be created in the database.

The Student entity also specifies an Association attribute with our child table “Courses”. The Storage property of the [Association] attribute indicated the name of the private property the will hold the values of the column. The ThisKey and OtherKey property provides the Primary Key to Foreign key relationship.

The “Courses” class will have the following properties

It defines a table called “Courses” which will store the courses for each student. Just as the “Student” entity had a child association, the “Courses” entity defines a parent association with the Student property.

Both our classes implement the INotifyPropertyChanged interface for change tracking and they also implement the INotifyPropertyChanging interface which limits the memory consumption related to change tracking.

Creating the DataContext

Right Click on the Solution and add a class called “StudentDataContext.cs” which will have the following code

The class derives from the DataContext class and it provides the entry point for accessing data within the applications. The constructor calls the base class constructor passing the connection string and it also exposes and initializes the properties which contain the table data.

Creating The UI and ViewModel

Lets modify our UI to display the students list. Open MainPage.xaml and replace the ContentPanel grid with the following code

We are using the Long List Selector to display the list of students and we are using the DataBinding with our Viewmodel to display the info.

We also use the application bar to display the Add button which will allow the user to Add a New student

Right Click on the Solution and create a new folder called “ViewModels”. Add a class to the folder called MainViewModel.cs which will be our ViewModel for displaying the Student Information. It will contain the below code

In the constructor, we are using the “DataBaseExists()” to check if the database already exists. If it does not, we create the database and the changes are persisted using the “SubmitChanges()” function.

Open App.xaml.cs file and add the following code in the beginning of the class

Add the following code to the Application_Activated() event

Once the application gets activated, we check if the our ViewModel contains the data, if it does not, we call the LoadStudentsData() method to fetch the student data from the database.

Open MainPage.xaml.cs and add the following code in the constructor

We set the DataContext of the Page to that of ViewModel and the data binding takes care of the rest. We need to provide a way for the User to add a new student along with the courses. Right Click on the Project and add a new Windows Phone Page and name it “StudentDetails.xaml”. When the user navigates to the page by clicking on “Add” link, the user can add a new student. When the navigation is done by clicking on a student, the student id is passed to the page and that student information is loaded.

Due to the length of the code, i will mention only the important functions, you can download the whole solution and check it out

The UI part of StudentDetails.xaml will use a pivot control to display the student information along with the Course information which belongs to student. The XAML will look like below

A popup control is also used which will be displayed when the user clicks Add Course button in Application Bar.

We will create a separate ViewModel for StudentDetails page. Right Click on the ViewModels folder and add a new class called “StudentViewModel.cs”. It will contain the below important functions

In the constructor we initialize a new student object or get an existing student from the database based on the value of studentId. While adding a new student, we use the “InsertOnSubmit()” function and then persist the changes.

Similarly we add a new course by using the following code

In the “InitializeNewCourse()” method, we associate the Course object to that of parent student object so that the courses are added against the current student. The “AddNewCourse()” method is similar to adding a student.

In the StudentDetails.xaml.cs file, the OnNavigatedTo() method initializes the ViewModel

We check whether we are trying to add a new student or viewing an existing one using the ‘studentid’ query string and our viewmodel is populated accordingly. Based on whether we are adding a new student or viewing an existing one, we disable/enable our “Save” and “Add Course” button.

On the click of the Save and Add course button, we call the appropriate function in the view model.

Lets run our app in the emulator. Once the App is launched, our main page will appear blank as we have not added any student information. Click on the Add icon the add a new student.



You will notice that once you save a new student, the “add courses” button is enabled. Click on the button to add courses for the student


Once the student is added, the main screen will appear as shown below

The data is saved in the local database. If you close the app and start it again, you will see the same data.

As we have also used data binding using MVVM pattern in this example, those new to MVVM may have some difficulty understanding it. But the basics of creating a simple database using models and data context are explained and you can apply the same to any type of application that you want. You can download the whole solution and play around with the code which will give you a better idea.

Download Solution

Written by Mako

May 12th, 2014 at 5:40 pm