Using Local Database in Windows Phone Apps

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 a great storage option when your app wants 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. Let's 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:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LocalDatabaseWPApp.Model
{
    [Table(Name = "Student")]
    public class Student : INotifyPropertyChanged, INotifyPropertyChanging
    {
        public Student()
        {

            _courses = new EntitySet<Courses>(
                    course =>
                    {
                        NotifyPropertyChanging("Courses");
                        course.Student = this;
                    },
                    course =>
                    {
                        NotifyPropertyChanging("Courses");
                        course.Student = null;
                    }
                );
        }

        private int _id;

        [Column(IsPrimaryKey = true, IsDbGenerated = true, 
        DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
        public int Id
        {
            get
            {
                return _id;
            }
            set
            {
                if (_id != value)
                {
                    NotifyPropertyChanging("Id");
                    _id = value;
                    NotifyPropertyChanged("Id");
                }
            }
        }

        private string _name;

        [Column(DbType = "nvarchar(255)", CanBeNull = false)]
        public string Name
        {
            get
            {
                return _name;
            }
            set
            {
                if (_name != value)
                {
                    NotifyPropertyChanging("Name");
                    _name = value;
                    NotifyPropertyChanged("Name");
                }
            }
        }

        private string _rollno;

        [Column(DbType = "nvarchar(255)", CanBeNull = false)]
        public string RollNo
        {
            get
            {
                return _rollno;
            }
            set
            {
                if (_rollno != value)
                {
                    NotifyPropertyChanging("RollNo");
                    _rollno = value;
                    NotifyPropertyChanged("RollNo");
                }
            }
        }

        private EntitySet<Courses> _courses;

        [Association(Name = "FK_Student_Courses", 
        Storage = "_courses", ThisKey = "Id", 
        OtherKey = "StudentId")]
        public EntitySet<Courses> Courses
        {
            get
            {
                return _courses;
            }
            set
            {
                _courses.Assign(value);
            }
        }

        public event PropertyChangingEventHandler PropertyChanging;

        // Used to notify the data context that a data context property is about to change
        private void NotifyPropertyChanging(string propertyName)
        {
            if (PropertyChanging != null)
            {
                PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
            }
        }

        public event PropertyChangedEventHandler PropertyChanged;

        // Used to notify the page that a data context property changed
        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }
    }
}

The [Table] mapping attribute specifies that this entity will map to a database table in the local database. Each of our properties 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 that 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:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LocalDatabaseWPApp.Model
{
    [Table(Name="Courses")]
    public class Courses : INotifyPropertyChanged, INotifyPropertyChanging
    {
        private int _courseid;
        [Column(IsPrimaryKey = true, IsDbGenerated = true, 
        DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
        public int CourseId
        {
            get
            {
                return _courseid;
            }

            set
            {
                if (_courseid != value)
                {
                    NotifyPropertyChanging("CourseId");
                    _courseid = value;
                    NotifyPropertyChanged("CourseId");
                }
            }
        }

        private string _coursename;
        [Column(DbType = "nvarchar(255)", CanBeNull = false)]
        public string CourseName
        {
            get
            {
                return _coursename;
            }

            set
            {
                if (_coursename != value)
                {
                    NotifyPropertyChanging("CourseName");
                    _coursename = value;
                    NotifyPropertyChanged("CourseName");
                }
            }
        }

        private EntityRef<Student> _student;
        [Association(Name = "FK_Student_Courses", 
        Storage = "_student", ThisKey = "StudentId",
        OtherKey = "Id", IsForeignKey = true)]
        public Student Student
        {
            get
            {
                return _student.Entity;
            }
            set
            {
                NotifyPropertyChanging("Student");
                _student.Entity = value;

                if (value != null)
                {
                    _studentid = value.Id;
                }

                NotifyPropertyChanged("Student");
            }
        }

        public int _studentid;
        [Column(CanBeNull=false)]
        public int StudentId
        {
            get
            {
                return _studentid;
            }
            set
            {
                if (_studentid != value)
                {
                    NotifyPropertyChanging("StudentId");
                    _studentid = value;
                    NotifyPropertyChanged("StudentId");
                }
            }
        }

        public event PropertyChangingEventHandler PropertyChanging;

        // Used to notify the data context that a data context property is about to change
        private void NotifyPropertyChanging(string propertyName)
        {
            if (PropertyChanging != null)
            {
                PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
            }
        }

        public event PropertyChangedEventHandler PropertyChanged;

        // Used to notify the page that a data context property changed
        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }
    }
}

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:

public class StudentDataContext : DataContext
    {
        // Specify the connection string as a static, used in main page and app.xaml.
        public static string DBConnectionString = "Data Source=isostore:/Students.sdf";

        // Pass the connection string to the base class.
        public StudentDataContext(string connectionString)
            : base(connectionString)
        {
            this.Students = this.GetTable<Student>();
            this.Courses = this.GetTable<Courses>();
        }

        // Specify a single table for the to-do items.
        public Table<Student> Students;
        public Table<Courses> Courses;
    }

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

Let's modify our UI to display the students list. Open MainPage.xaml and replace the ContentPanel grid with the following code:

<!--ContentPanel - place additional content here-->
       <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
           <phone:LongListSelector x:Name="llsStudents"
                                   Margin="0,0,-12,0"
                                   ItemsSource="{Binding Students}"
                                   SelectionChanged="llsStudents_SelectionChanged">
               <phone:LongListSelector.ItemTemplate>
                   <DataTemplate>
                       <StackPanel Margin="0,0,0,17">
                           <StackPanel Orientation="Horizontal">
                               <TextBlock Text="{Binding Id,StringFormat='Id: {0}'}"
                                    TextWrapping="Wrap"
                                    Style="{StaticResource PhoneTextLargeStyle}"/>
                               <TextBlock Text="{Binding Name,StringFormat='Name: {0}'}"
                                    TextWrapping="Wrap"
                                    MaxWidth="300"
                                    Style="{StaticResource PhoneTextLargeStyle}"/>
                           </StackPanel>
                           <TextBlock
                                   Text="{Binding RollNo, StringFormat='RollNo: {0}'}"
                                   TextWrapping="Wrap"
                                   MaxWidth="300"
                                   Margin="12,-6,12,0"
                                   Style="{StaticResource PhoneTextSubtleStyle}"/>
                       </StackPanel>
                   </DataTemplate>
               </phone:LongListSelector.ItemTemplate>
           </phone:LongListSelector>
       </Grid>

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 information.

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

<phone:PhoneApplicationPage.ApplicationBar>
    <shell:ApplicationBar IsVisible="True" IsMenuEnabled="False">
        <shell:ApplicationBarIconButton IconUri="/Assets/add.png"
                                        Text="add student"
                                        Click="ApplicationBarIconButton_Click_1" />
    </shell:ApplicationBar>

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:

public class MainViewModel : INotifyPropertyChanged
    {
        private StudentDataContext context;
        public ObservableCollection<Student> Students { get; private set; }
        public bool IsDataLoaded { get; private set; }

        public MainViewModel()
        {
            this.Students = new ObservableCollection<Student>();
            context = new StudentDataContext(StudentDataContext.DBConnectionString);

            if(!context.DatabaseExists())
            {
                context.CreateDatabase();
                context.SubmitChanges();
            }
        }

        public void LoadStudentsData()
        {
            if(context.Students.Count() > 0)
            {
                List<Student> studentList = context.Students.ToList();
                Students = new ObservableCollection<Student>(studentList);
            }

            IsDataLoaded = true;
        }

        public event PropertyChangedEventHandler PropertyChanged;
        private void NotifyPropertyChanged(String propertyName)
        {
            PropertyChangedEventHandler handler = PropertyChanged;
            if (null != handler)
            {
                handler(this, new PropertyChangedEventArgs(propertyName));
            }
        }
    }

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:

private static MainViewModel viewModel = null;

        /// <summary>
        /// A static ViewModel used by the views to bind against.
        /// </summary>
        /// <returns>The MainViewModel object.</returns>
        public static MainViewModel ViewModel
        {
            get
            {
                // Delay creation of the view model until necessary
                if (viewModel == null)
                    viewModel = new MainViewModel();

                return viewModel;
            }
        }

Add the following code to the Application_Activated() event:

private void Application_Activated(object sender, ActivatedEventArgs e)
       {
           if (!App.ViewModel.IsDataLoaded)
           {
               App.ViewModel.LoadStudentsData();
           }
       }

Once the application gets activated, we check if 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:

// Constructor
        public MainPage()
        {
            InitializeComponent();
            DataContext = App.ViewModel;
            // Sample code to localize the ApplicationBar
            //BuildLocalizedApplicationBar();
        }

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.

private void llsStudents_SelectionChanged(object sender, SelectionChangedEventArgs e)
       {
           if (llsStudents.SelectedItem == null)
               return;

           NavigationService.Navigate(new Uri("/StudentDetails.xaml?studentid=" + 
                                     (llsStudents.SelectedItem as Student).Id, UriKind.Relative));
           llsStudents.SelectedItem = null;
       }

       private void ApplicationBarIconButton_Click_1(object sender, EventArgs e)
       {
           NavigationService.Navigate(new Uri("/StudentDetails.xaml", UriKind.Relative));
       }

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:

<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,-20,12,0">
        <phone:Pivot Name="studentDetailsPivot">
            <phone:PivotItem Name="detailsPivot" Header="info">
                <StackPanel>
                    <TextBlock Text="Name"
                               Margin="10,0,0,0"
                               Style="{StaticResource PhoneTextLargeStyle}" />
                    <TextBox Name="txtName"
                             Text="{Binding Student.Name, Mode=TwoWay}"
                             Margin="0,-10,0,0"/>
                    <TextBlock Text="RollNo"
                               Margin="10,10,0,0"
                               Style="{StaticResource PhoneTextLargeStyle}" />
                    <TextBox Name="txtRollNo"
                             Text="{Binding Student.RollNo, Mode=TwoWay}"
                             Margin="0,-10,0,0"/>
                </StackPanel>
            </phone:PivotItem>
            <phone:PivotItem Name="coursesPivot" Header="courses">
                <phone:LongListSelector x:Name="llsCourses"
                                        Margin="0,0,-12,0"
                                        ItemsSource="{Binding Courses}">
                    <phone:LongListSelector.ItemTemplate>
                        <DataTemplate>
                            <StackPanel Margin="0,0,0,17">
                                <TextBlock
                                    Text="{Binding CourseName}"
                                    TextWrapping="Wrap"
                                    Style="{StaticResource PhoneTextLargeStyle}"/>

                            </StackPanel>
                        </DataTemplate>
                    </phone:LongListSelector.ItemTemplate>
                </phone:LongListSelector>
            </phone:PivotItem>
        </phone:Pivot>
        <Popup x:Name="popupAddCourse" Grid.Row="2">
            <Border  BorderThickness="2"
                     Margin="10"
                     BorderBrush="{StaticResource PhoneForegroundBrush}">
                <Grid Background="{StaticResource PhoneBackgroundBrush}">
                    <Grid.RowDefinitions>
                        <RowDefinition Height="Auto" />
                        <RowDefinition Height="Auto" />
                    </Grid.RowDefinitions>
                    <Grid.ColumnDefinitions>
                        <ColumnDefinition Width="*" />
                        <ColumnDefinition Width="*" />
                    </Grid.ColumnDefinitions>
                    <TextBox
                        Name="txtCourseDetails"
                        Grid.ColumnSpan="2"
                        Text="{Binding NewCourse.CourseName, Mode=TwoWay}"
                        TextWrapping="Wrap"
                        MaxLength="255"
                        Width="400"
                        MinHeight="200"
                        Margin="10,0" />
                    <Button
                        Name="addCourseButton"
                        Content="add"
                        Click="addButton_Click_1"
                        Grid.Row="1"
                        Grid.Column="0"
                        Margin="10,0"
                        />
                    <Button
                        Name="cancelButton"
                        Content="cancel"  
                        Click="cancelButton_Click_1"
                        Grid.Row="1"
                        Grid.Column="1"
                        Margin="10,0"
                        />
                </Grid>
            </Border>
        </Popup>
    </Grid>
</Grid>

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:

public StudentViewModel()
        {
            this.Student = new Student();
        }

        public StudentViewModel(int studentId)
        {
            this.Student = context.Students.Where(b => b.Id == studentId).FirstOrDefault();

            context.SubmitChanges();

            LoadCourses();
        }

        private void LoadCourses()
        {
            List<Courses> courseList = context.Courses
                .Where(n => n.StudentId == this.Student.Id)
                .ToList();
            this.Courses = new ObservableCollection<Courses>(courseList);  
        }

        public void Save()
        {
            if (Student.Id <= 0)
            {
               context.Students.InsertOnSubmit(Student);
            }

            context.SubmitChanges();
        }

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:

public void InitializeNewCourse()
        {
            NewCourse = new Courses();
            NewCourse.Student = this.Student;
            NewCourse.CourseName = "";
        }

        public void AddNewCourse()
        {
            context.Courses.InsertOnSubmit(NewCourse);
            context.SubmitChanges();
            LoadCourses();
        }

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.

protected override void OnNavigatedTo(NavigationEventArgs e)
        {
            int studentid = -1;
            string selectedIndex = "";
            if (NavigationContext.QueryString.TryGetValue("studentid", out selectedIndex))
            {
                studentid = int.Parse(selectedIndex);
                _studentViewModel = new StudentViewModel(studentid);
            }
            else
            {
                _studentViewModel = new StudentViewModel();
            }

            DataContext = _studentViewModel;
            ENableControlsBasedOnStudent();
        }

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.

private void ApplicationBarSaveButton_Click_1(object sender, EventArgs e)
       {
           UpdateTextBoxBinding();
           _studentViewModel.Save();
           ENableControlsBasedOnStudent();
       }

       private void ApplicationBarAddCourseButton_Click_1(object sender, EventArgs e)
       {
           UpdateTextBoxBinding();
           _studentViewModel.InitializeNewCourse();
           popupAddCourse.IsOpen = true;
           txtCourseDetails.Focus();
       }

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, then 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