Share via


How to create a local database app with MVVM for Windows Phone 8

[ This article is for Windows Phone 8 developers. If you’re developing for Windows 10, see the latest documentation. ]

Starting with Windows Phone OS 7.1, you can store relational data in a local database that resides in your application’s local folder. This topic describes how to create a multi-page to-do list app that uses a local database. The app is similar to the one featured in How to create a basic local database app for Windows Phone 8, but additionally demonstrates the Model-View-ViewModel (MVVM) pattern and how to use the pivot control, Windows Phone Toolkit, and database associations. For more information about using a local database in your Windows Phone app, see Local database for Windows Phone 8.

Tip

This topic corresponds to the Local Database Sample. For more information about using the MVVM pattern, see Developing a Windows Phone Application using the MVVM Pattern and A Case Study for Building Advanced Windows Phone Applications.

This topic contains the following sections.

The following image shows the app’s pivot page and new task page.

In the process, you will modify or create the following code files:

  • MainPage.xaml: Modify the main page of the app to add a pivot control that displays to-do tasks grouped by the categories: all, home, work, and hobbies.

  • NewTaskPage.xaml: Add this page to provide a UI for adding a new task to the database. This page uses the Windows Phone Toolkit ListPicker control for specifying the category associated with task.

  • Model\ToDoDataContext.cs: Create this class file to specify the LINQ to SQL data context and object models that represent the local database. With respect to MVVM, this class is the data model.

  • ViewModel\ToDoViewModel.cs: Create this class file to represent the ViewModel of the app. This class contains several observable collections for grouping to-do tasks by category. The ViewModel also provides methods that perform add and delete operations on the database.

  • App.xaml.cs: Modify this file to create the local database and a static ViewModel that can be accessed across the app.

  • MainPage.xaml.cs: Modify this page to set the page DataContext property to the app ViewModel and handle the add and delete button clicks.

  • NewTaskPage.xaml.cs: Modify this page to set the page DataContext property to the app ViewModel and handle the ok and cancel button clicks.

Preparing the app

In this section, you will create the app, install the Windows Phone Toolkit, set assembly references, and add the icons used by the app.

To create the app

  • Using the Windows Phone SDK, create a new project with the Windows Phone App template.

To install the Windows Phone Toolkit

  • The Windows Phone Toolkit is not included with the Windows Phone SDK. You must go to Codeplex and download the Windows Phone Toolkit separately. Navigate to that website and follow the directions to install it.

Note

There are different ways that you can install the toolkit. This topic assumes that your app has a reference to the Windows Phone Toolkit, Microsoft.Phone.Controls.Toolkit.

To set assembly references

  1. This app requires a reference to the LINQ to SQL assembly for Windows Phone. From the Project menu, click Add Reference, select System.Data.Linq from the Assemblies/Framework list, and then click OK.

Note

When targeting Windows Phone 8, this assembly is automatically referenced in new apps that use the Windows Phone App template.

  1. To use the pivot control, this app requires a reference to the Windows Phone Controls assembly. From the Project menu, click Add Reference, select Microsoft.Phone.Controls from the .NET tab, and then click OK.

To add icons used by the app

  1. In Solution Explorer, right-click your project and select Add, then New Folder.

  2. Name the new folder Images.

  3. In Solution Explorer, right-click the Images folder and select Add, then Existing Item. This opens the Add Existing Item menu, from which you can select the icons used by the app.

  4. In the Add Existing Item window, navigate to the following path to select the icons. This step assumes a default installation of Visual Studio. If you installed it in a different location, find the icon in the corresponding location.

    C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\Icons\dark

    From the folder, select the following icons:

    • appbar.add.rest.png

    • appbar.cancel.rest.png

    • appbar.check.rest.png

    • appbar.delete.rest.png

    These icons are designed for a dark background and are colored white; the icons may appear to be blank on the white background of the Add Existing Item window.

  5. In Solution Explorer, right-click each icon and set the file properties so that the icon is built as Content and always copied to the output directory (Copy always).

Creating the app UI

In this section, you will create UI for the main and new-task page of this app. With respect to MVVM, the app pages are the views. The main page demonstrates how to use a Pivot control for displaying data. The new task page demonstrates how to use the ListPicker control from the Windows Phone Toolkit for selecting data.

To create the main page UI

  1. In the main page of the app, MainPage.xaml, add the following attribute to the phone:PhoneApplicationPage element at the top of the page.

    xmlns:controls="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone.Controls"
    

    This namespace is required to use the Pivot control.

  2. In MainPage.xaml, above the grid named LayoutRoot, add the following resource element.

        <phone:PhoneApplicationPage.Resources>
            <DataTemplate x:Key="ToDoListBoxItemTemplate">
    
                <Grid HorizontalAlignment="Stretch" Width="420">
                    <Grid.ColumnDefinitions>
                        <ColumnDefinition Width="100" />
                        <ColumnDefinition Width="*" />
                        <ColumnDefinition Width="Auto" />
                        <ColumnDefinition Width="100" />
                    </Grid.ColumnDefinitions>
    
                    <CheckBox 
                        IsChecked="{Binding IsComplete, Mode=TwoWay}" 
                        Grid.Column="0" VerticalAlignment="Top"/>
    
                    <TextBlock 
                        Text="{Binding ItemName}" 
                        FontSize="{StaticResource PhoneFontSizeLarge}" 
                        Grid.Column="1" Grid.ColumnSpan="2" 
                        VerticalAlignment="Top" Margin="-36, 12, 0, 0"/>
    
                    <Button                                
                        Grid.Column="3"
                        x:Name="deleteTaskButton"
                        BorderThickness="0"                                                                  
                        Margin="0, -18, 0, 0"
                        Click="deleteTaskButton_Click">
    
                        <Image 
                        Source="/Images/appbar.delete.rest.png"
                        Height="75"
                        Width="75"/>
    
                    </Button>
                </Grid>
            </DataTemplate>
        </phone:PhoneApplicationPage.Resources>
    

    This item template is reused by each of the pivot pages to display a single row of data, a to-do task, from the local database. Each row includes a CheckBox to mark the task as completed, a TextBlock to display the to-do task text, and a Button to allow the task to be deleted. The CheckBox is configured with two-way binding to the IsCompleted property of the task. When you check-off a task in the UI, the new value will be automatically captured by the corresponding observable collection in the ViewModel to which it will be bound. The Textblock is bound to the ItemName property of the task.

  3. In MainPage.xaml, replace the grid named LayoutRoot with the following code. This code should be added below the resource element that was added in the previous step.

        <!--LayoutRoot is the root grid where all page content is placed.-->
        <Grid x:Name="LayoutRoot" Background="Transparent">
            <Grid.RowDefinitions>
                <RowDefinition Height="Auto"/>
                <RowDefinition Height="*"/>
            </Grid.RowDefinitions>
    
            <!--TitlePanel contains the name of the application and page title.-->
            <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
                <TextBlock 
                    x:Name="ApplicationTitle" 
                    Text="LOCAL DATABASE EXAMPLE: TO-DO LIST" 
                    Style="{StaticResource PhoneTextNormalStyle}"/>
            </StackPanel>
    
            <!--ContentPanel - place additional content here.-->
            <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
                <controls:Pivot Margin="0, -36, 0, 0">
    
                    <controls:PivotItem Header="all">
                        <ListBox 
                            x:Name="allToDoItemsListBox" 
                            ItemsSource="{Binding AllToDoItems}" 
                            Margin="12, 0, 12, 0" Width="440" 
                            ItemTemplate="{StaticResource ToDoListBoxItemTemplate}" />
                    </controls:PivotItem>
    
                    <controls:PivotItem Header="home">
                        <ListBox 
                            x:Name="homeToDoItemsListBox" 
                            ItemsSource="{Binding HomeToDoItems}" 
                            Margin="12, 0, 12, 0" Width="440" 
                            ItemTemplate="{StaticResource ToDoListBoxItemTemplate}" />
                    </controls:PivotItem>
    
                    <controls:PivotItem Header="work">
                        <ListBox 
                            x:Name="workToDoItemsListBox" 
                            ItemsSource="{Binding WorkToDoItems}" 
                            Margin="12, 0, 12, 0" Width="440" 
                            ItemTemplate="{StaticResource ToDoListBoxItemTemplate}" />
                    </controls:PivotItem>
    
                    <controls:PivotItem Header="hobbies">
                        <ListBox
                            x:Name="hobbiesToDoItemsListBox" 
                            ItemsSource="{Binding HobbiesToDoItems}" 
                            Margin="12, 0, 12, 0" Width="440" 
                            ItemTemplate="{StaticResource ToDoListBoxItemTemplate}" />
                    </controls:PivotItem>
    
                </controls:Pivot>
            </Grid>
        </Grid>
    

    This grid specifies the app title and the Pivot control. The pivot control contains four different pages: all, home, work, and hobbies, corresponding to each of the four possible categories that can be assigned to a to-do task. Each of these pages is bound to the AllToDoItems, HomeToDoItems, WorkToDoItems, and HobbiesToDoItems observable collections in the ViewModel, respectively.

  4. In MainPage.xaml, add the following code below the grid named LayoutRoot.

        <phone:PhoneApplicationPage.ApplicationBar>
            <shell:ApplicationBar IsVisible="True" IsMenuEnabled="True">
    
                <shell:ApplicationBarIconButton 
                    IconUri="/Images/appbar.add.rest.png" 
                    Text="add" 
                    x:Name="newTaskAppBarButton" 
                    Click="newTaskAppBarButton_Click"/>
    
            </shell:ApplicationBar>
        </phone:PhoneApplicationPage.ApplicationBar>
    

    This page uses the app bar to display an add button, for adding to-do tasks to the database. The method corresponding to this button will be created later in this topic.

To create the new task page UI

  1. In Solution Explorer, right-click your project and select Add, then New Item.

  2. In the Add New Item window, select Windows Phone Portrait Page and name the file NewTaskPage.xaml. Then click Add.

  3. In NewTaskPage.xaml, add the following attribute to the phone:PhoneApplicationPage element at the top of the page.

    xmlns:toolkit="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone.Controls.Toolkit"
    

    This namespace is required to use the Windows Phone Toolkit ListPicker control.

  4. In NewTaskPage.xaml, replace the grid named LayoutRoot, with the following code.

        <!--LayoutRoot is the root grid where all page content is placed.-->
        <Grid x:Name="LayoutRoot" Background="Transparent">
            <Grid.RowDefinitions>
                <RowDefinition Height="Auto"/>
                <RowDefinition Height="*"/>
            </Grid.RowDefinitions>
    
            <!--TitlePanel contains the name of the app and page title.-->
            <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
                <TextBlock 
                    x:Name="ApplicationTitle" 
                    Text="LOCAL DATABASE EXAMPLE: TO-DO LIST" 
                    Style="{StaticResource PhoneTextNormalStyle}"/>
                <TextBlock 
                    x:Name="PageTitle" 
                    Text="new task" 
                    Margin="9,-7,0,0" 
                    Style="{StaticResource PhoneTextTitle1Style}"/>
            </StackPanel>
    
            <!--ContentPanel - place additional content here.-->
            <StackPanel x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
                <TextBlock Text="Name"/>
                <TextBox x:Name="newTaskNameTextBox"/>
                <TextBlock Text="Category"/>
    
                <toolkit:ListPicker
                    x:Name="categoriesListPicker"
                    ItemsSource="{Binding CategoriesList}"
                    DisplayMemberPath="Name">
                </toolkit:ListPicker>
            </StackPanel>
        </Grid>
    

    This grid contains two StackPanel controls. The first specifies the app and page titles. The second StackPanel contains the data entry controls: a TextBox to enter the text of the new to-do task and the ListPicker control to specify the category of the task. The ListPicker is bound to CategoriesList, a ViewModel property of type List.

  5. In NewTaskPage.xaml, add the following code below the grid named LayoutRoot.

        <phone:PhoneApplicationPage.ApplicationBar>
            <shell:ApplicationBar IsVisible="True" IsMenuEnabled="True">
    
                <shell:ApplicationBarIconButton 
                    x:Name="appBarOkButton" 
                    IconUri="/Images/appbar.check.rest.png" 
                    Text="ok" 
                    Click="appBarOkButton_Click"/>
    
                <shell:ApplicationBarIconButton 
                    x:Name="appBarCancelButton" 
                    IconUri="/Images/appbar.cancel.rest.png" 
                    Text="cancel" 
                    Click="appBarCancelButton_Click"/>
    
            </shell:ApplicationBar>
        </phone:PhoneApplicationPage.ApplicationBar>
    

    This page uses the app bar to display an ok and cancel button. The methods corresponding to these buttons will be created later in this topic.

Creating the data model

In this section, you create the LINQ to SQL data context and the objects that represent the database tables and associations. First you will create the file and add templates for each of the tables. Then you will build out each of the tables and create the data context.

To prepare the data model file

  1. In Solution Explorer, right-click your project and select Add, then New Folder.

  2. Name the new folder Model.

  3. In Solution Explorer, right-click the Model folder and select Add, then New Item.

  4. In the Add New Item window, select Code File and name the file ToDoDataContext.cs. Then click Add.

  5. In ToDoDataContext.cs, add the following directives and the namespace that will contain the data model classes.

    using System;
    using System.ComponentModel;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    
    namespace LocalDatabaseSample.Model
    {
    
    }
    
  6. In ToDoDataContext.cs, add the following code to the LocalDatabaseSample.Model namespace twice. These classes will be renamed in the following steps for each of the tables.

Note

Until you rename the classes in the following steps, Visual Studio will display ambiguity errors.

``` csharp
    [Table]
    public class AddTableNameHere : INotifyPropertyChanged, INotifyPropertyChanging
    {

        //
        // TODO: Add columns and associations, as applicable, here.
        //

        // Version column aids update performance.
        [Column(IsVersion = true)]
        private Binary _version;

        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;

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

        #endregion

        #region INotifyPropertyChanging Members

        public event PropertyChangingEventHandler PropertyChanging;

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

        #endregion
    }
```

This is a basic template for an entity, a class that represents a local database table. The columns and association are missing for the moment to demonstrate the following code features that we recommend most entities have in common:

  - The \[table\] attribute specifies that the class will represent a database table.

  - The INotifyPropertyChanged interface is used for change tracking.

  - The INotifyPropertyChanging interface helps limit memory consumption related to change tracking.

  - The Binary version column, with the \[Column(IsVersion = true)\] attribute, significantly improves table update performance.

Note

Entities can inherit CLR object members from other entities such as events, methods, and properties. LINQ to SQL attributes from other entities cannot be inherited. For example, you can create a base class named L2SEntity that implements INotifyPropertyChanged and INotifyPropertyChanging. Other entities can inherit the events and methods from L2SEntity, but LINQ to SQL will recognize only those entities that are explicitly marked with the [Table] attribute. Any LINQ to SQL attributes written in the L2SEntity class itself will not be inherited by other entities.

  1. Rename one of the AddTableNameHere classes to ToDoItem. This class will store the to-do task information.

  2. Rename the other AddTableNameHere class to ToDoCategory. This class will store a list of categories.

To build out the ToDoItem class

  1. In ToDoDataContext.cs, add the following code to the ToDoItem class.

            // Define ID: private field, public property, and database column.
            private int _toDoItemId;
    
            [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
            public int ToDoItemId
            {
                get {return _toDoItemId;}
                set
                {
                    if (_toDoItemId != value)
                    {
                        NotifyPropertyChanging("ToDoItemId");
                        _toDoItemId = value;
                        NotifyPropertyChanged("ToDoItemId");
                    }
                }
            }
    
            // Define item name: private field, public property, and database column.
            private string _itemName;
    
            [Column]
            public string ItemName
            {
                get {return _itemName;}
                set
                {
                    if (_itemName != value)
                    {
                        NotifyPropertyChanging("ItemName");
                        _itemName = value;
                        NotifyPropertyChanged("ItemName");
                    }
                }
            }
    
            // Define completion value: private field, public property, and database column.
            private bool _isComplete;
    
            [Column]
            public bool IsComplete
            {
                get {return _isComplete;}
                set
                {
                    if (_isComplete != value)
                    {
                        NotifyPropertyChanging("IsComplete");
                        _isComplete = value;
                        NotifyPropertyChanged("IsComplete");
                    }
                }
            }
    

    These fields and properties add three columns to the database: ToDoItemId, ItemName, and IsCompleted. The [Column] attribute identifies to the LINQ to SQL runtime that the property represents a database column. For a full list of valid column attribute settings, see System.Data.Linq.Mapping..::.ColumnAttribute.

  2. In ToDoDataContext.cs, add the following code to the ToDoItem class.

            // Internal column for the associated ToDoCategory ID value
            [Column]
            internal int _categoryId;
    
            // Entity reference, to identify the ToDoCategory "storage" table
            private EntityRef<ToDoCategory> _category;
    
            // Association, to describe the relationship between this key and that "storage" table
            [Association(Storage = "_category", ThisKey = "_categoryId", OtherKey = "Id", IsForeignKey = true)]
            public ToDoCategory Category
            {
                get {return _category.Entity;}
                set
                {
                    NotifyPropertyChanging("Category");
                    _category.Entity = value;
    
                    if (value != null)
                    {
                        _categoryId = value.Id;
                    }
    
                    NotifyPropertyChanging("Category");
                }
            }
    

    This code defines the association between the ToDoItem and ToDoCategory tables. The private _categoryId field stores the identifier of the category corresponding to the to-do item. The _category entity reference identifies the other table to be associated with this table. The Category association handles retrieving the appropriate ToDoCategory object during the get, and assigning the appropriate ToDoCategory identifier value to _categoryId during the set.

    Together, the private entity reference and the public accessors provide a way to navigate across the association using dot notation. They also ensure that database relationships are kept up-to-date when object-level changes are made.

To build out the ToDoCategory class

  1. In ToDoDataContext.cs, add the following code to the ToDoCategory class.

            // Define ID: private field, public property, and database column.
            private int _id;
    
            [Column(DbType = "INT NOT NULL IDENTITY", IsDbGenerated = true, IsPrimaryKey = true)]
            public int Id
            {
                get {return _id;}
                set
                {
                    NotifyPropertyChanging("Id");
                    _id = value;
                    NotifyPropertyChanged("Id");
                }
            }
    
            // Define category name: private field, public property, and database column.
            private string _name;
    
            [Column]
            public string Name
            {
                get {return _name;}
                set
                {
                    NotifyPropertyChanging("Name");
                    _name = value;
                    NotifyPropertyChanged("Name");
                }
            }
    

    This code defines the Id and Name properties of a category.

  2. In ToDoDataContext.cs, add the following code to the ToDoCategory class.

            // Define the entity set for the collection side of the relationship.
            private EntitySet<ToDoItem> _todos;
    
            [Association(Storage = "_todos", OtherKey = "_categoryId", ThisKey = "Id")]
            public EntitySet<ToDoItem> ToDos
            {
                get { return this._todos; }
                set { this._todos.Assign(value); }
            }
    
    
            // Assign handlers for the add and remove operations, respectively.
            public ToDoCategory()
            {
                _todos = new EntitySet<ToDoItem>(
                    new Action<ToDoItem>(this.attach_ToDo), 
                    new Action<ToDoItem>(this.detach_ToDo)
                    );
            }
    
            // Called during an add operation
            private void attach_ToDo(ToDoItem toDo)
            {
                NotifyPropertyChanging("ToDoItem");
                toDo.Category = this;
            }
    
            // Called during a remove operation
            private void detach_ToDo(ToDoItem toDo)
            {
                NotifyPropertyChanging("ToDoItem");
                toDo.Category = null;
            }
    

    This code defines the collection side of the association with the ToDoItems table. The EntitySet constructor takes delegates for the add events and remove events on the set. This helps set category information properly on new ToDoItem objects that are added to the collection, even if that category was not set explicitly on the new ToDoItem object when it was created.

To create the LINQ to SQL data context

  • In ToDoDataContext.cs, add the following code to the LocalDatabaseSample.Model namespace.

        public class ToDoDataContext : DataContext
        {
            // Pass the connection string to the base class.
            public ToDoDataContext(string connectionString) : base(connectionString)
            { }
    
            // Specify a table for the to-do items.
            public Table<ToDoItem> Items;
    
            // Specify a table for the categories.
            public Table<ToDoCategory> Categories;
        }
    

    This data context specifies two tables, Items and Categories. The Items table will store the to-do task items, each based on the ToDoItem class. The Categories table will store the to-do task categories, each based on the ToDoCategory class.

Creating the ViewModel

In this section, you create the ViewModel of the app. The ViewModel is responsible for performing operations on the database and presenting data to the app pages via several observable collections and a list.

To prepare the ViewModel file

  1. In Solution Explorer, right-click your project and select Add, then New Folder.

  2. Name the new folder ViewModel.

  3. In Solution Explorer, right-click the ViewModel folder and select Add, then New Item.

  4. In the Add New Item window, select Code File and name the file ToDoViewModel.cs. Then click Add.

  5. In ToDoViewModel.cs, add the following code.

    using System.Collections.Generic;
    using System.Collections.ObjectModel;
    using System.ComponentModel;
    using System.Linq;
    
    // Directive for the data model.
    using LocalDatabaseSample.Model;
    
    
    namespace LocalDatabaseSample.ViewModel
    {
        public class ToDoViewModel : INotifyPropertyChanged
        {
            // LINQ to SQL data context for the local database.
            private ToDoDataContext toDoDB;
    
            // Class constructor, create the data context object.
            public ToDoViewModel(string toDoDBConnectionString)
            {
                toDoDB = new ToDoDataContext(toDoDBConnectionString);
            }
    
            //
            // TODO: Add collections, list, and methods here.
            //
    
            // Write changes in the data context to the database.
            public void SaveChangesToDB()
            {
                toDoDB.SubmitChanges();
            }
    
            #region INotifyPropertyChanged Members
    
            public event PropertyChangedEventHandler PropertyChanged;
    
            // Used to notify the app that a property has changed.
            private void NotifyPropertyChanged(string propertyName)
            {
                if (PropertyChanged != null)
                {
                    PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
                }
            }
            #endregion
        }
    }
    

    This is the template for the ViewModel. It uses a directive to the LocalDatabaseSample.Model namespace to reference the LINQ to SQL data model that was created in the previous section. The ViewModel implements the INotifyPropertyChanged interface for change tracking.

    Performing operations on the local database is a core function of the ViewModel. The LINQ to SQL data context, toDoDB, is referenced throughout the ViewModel and created in the constructor of the ViewModel. The SaveChangesToDB method provides a general-purpose saving mechanism to the views.

To create the observable collections and list

  • In ToDoViewModel.cs, add the following code to the ToDoViewModel class.

            // All to-do items.
            private ObservableCollection<ToDoItem> _allToDoItems;
            public ObservableCollection<ToDoItem> AllToDoItems
            {
                get { return _allToDoItems; }
                set
                {
                    _allToDoItems = value;
                    NotifyPropertyChanged("AllToDoItems");
                }
            }
    
            // To-do items associated with the home category.
            private ObservableCollection<ToDoItem> _homeToDoItems;
            public ObservableCollection<ToDoItem> HomeToDoItems
            {
                get { return _homeToDoItems; }
                set
                {
                    _homeToDoItems = value;
                    NotifyPropertyChanged("HomeToDoItems");
                }
            }
    
            // To-do items associated with the work category.
            private ObservableCollection<ToDoItem> _workToDoItems;
            public ObservableCollection<ToDoItem> WorkToDoItems
            {
                get { return _workToDoItems; }
                set
                {
                    _workToDoItems = value;
                    NotifyPropertyChanged("WorkToDoItems");
                }
            }
    
            // To-do items associated with the hobbies category.
            private ObservableCollection<ToDoItem> _hobbiesToDoItems;
            public ObservableCollection<ToDoItem> HobbiesToDoItems
            {
                get { return _hobbiesToDoItems; }
                set
                {
                    _hobbiesToDoItems = value;
                    NotifyPropertyChanged("HobbiesToDoItems");
                }
            }
    
            // A list of all categories, used by the add task page.
            private List<ToDoCategory> _categoriesList;
            public List<ToDoCategory> CategoriesList
            {
                get { return _categoriesList; }
                set
                {
                    _categoriesList = value;
                    NotifyPropertyChanged("CategoriesList");
                }
            }
    

    This code specifies the observable collections that are used in the main page by the Pivot control: AllToDoItems, HomeToDoItems, WorkToDoItems, and HobbiesToDoItems. It also specifies the CategoriesList list that is used on the new task page by the ListPicker control.

To load the collections and list

  • In ToDoViewModel.cs, add the following code to the ToDoViewModel class.

        // Query database and load the collections and list used by the pivot pages.
        public void LoadCollectionsFromDatabase()
        {
    
            // Specify the query for all to-do items in the database.
            var toDoItemsInDB = from ToDoItem todo in toDoDB.Items
                                select todo;
    
            // Query the database and load all to-do items.
            AllToDoItems = new ObservableCollection<ToDoItem>(toDoItemsInDB);
    
            // Specify the query for all categories in the database.
            var toDoCategoriesInDB = from ToDoCategory category in toDoDB.Categories
                                    select category;
    
    
            // Query the database and load all associated items to their respective collections.
            foreach (ToDoCategory category in toDoCategoriesInDB)
            {
                switch (category.Name)
                {
                    case "Home":
                        HomeToDoItems = new ObservableCollection<ToDoItem>(category.ToDos);
                        break;
                    case "Work":
                        WorkToDoItems = new ObservableCollection<ToDoItem>(category.ToDos);
                        break;
                    case "Hobbies":
                        HobbiesToDoItems = new ObservableCollection<ToDoItem>(category.ToDos);
                        break;
                    default:
                        break;
                }
            }
    
            // Load a list of all categories.
            CategoriesList = toDoDB.Categories.ToList(); 
    
        }
    

    In this code, the ViewModel loads the collections and list with data from the local database. With deferred loading, the database is not actually queried until the observable collections are instantiated.

Note

The foreach and switch statements are shown to demonstrate how you can take advantage of the EntitySet object to retrieve all of the ToDoItem objects associated with that particular category. In this particular method, three standard LINQ queries could have also achieved the same result.

To perform add and remove operations on the database

  1. In ToDoViewModel.cs, add the following code to the ToDoViewModel class.

            // Add a to-do item to the database and collections.
            public void AddToDoItem(ToDoItem newToDoItem)
            {
                // Add a to-do item to the data context.
                toDoDB.Items.InsertOnSubmit(newToDoItem);
    
                // Save changes to the database.
                toDoDB.SubmitChanges();
    
                // Add a to-do item to the "all" observable collection.
                AllToDoItems.Add(newToDoItem);
    
                // Add a to-do item to the appropriate filtered collection.
                switch (newToDoItem.Category.Name)
                {
                    case "Home":
                        HomeToDoItems.Add(newToDoItem);
                        break;
                    case "Work":
                        WorkToDoItems.Add(newToDoItem);
                        break;
                    case "Hobbies":
                        HobbiesToDoItems.Add(newToDoItem);
                        break;
                    default:
                        break;
                }
            }
    

    This method is called to add a new to-do item, a task, to the app. When a to-do item is added, several things need to be updated. First, the LINQ to SQL data context, toDoDB, is updated. Then the SubmitChanges method is called to persist changes to the database. Finally, the applicable observable collections are updated with calls to their Add methods.

  2. In ToDoViewModel.cs, add the following code to the ToDoViewModel class.

            // Remove a to-do task item from the database and collections.
            public void DeleteToDoItem(ToDoItem toDoForDelete)
            {
    
                // Remove the to-do item from the "all" observable collection.
                AllToDoItems.Remove(toDoForDelete);
    
                // Remove the to-do item from the data context.
                toDoDB.Items.DeleteOnSubmit(toDoForDelete);
    
                // Remove the to-do item from the appropriate category.   
                switch (toDoForDelete.Category.Name)
                {
                    case "Home":
                        HomeToDoItems.Remove(toDoForDelete);
                        break;
                    case "Work":
                        WorkToDoItems.Remove(toDoForDelete);
                        break;
                    case "Hobbies":
                        HobbiesToDoItems.Remove(toDoForDelete);
                        break;
                    default:
                        break;
                }
    
                // Save changes to the database.
                toDoDB.SubmitChanges();                
            }
    

    This method is called to remove a to-do item, a task, from the app. The sequence of removing items is somewhat opposite of adding them. First, the Remove method removes the item from the AllToDoItems observable collection. Then the DeleteOnSubmit method removes the item from the LINQ to SQL data context. After that, the item is removed from the applicable filtered collection. Finally, the SubmitChanges method is used to remove the item from (and save any other changes to) the local database.

Completing the app

In this section, you connect all of the pieces that have been created in the previous sections. First, the app class is modified to specify a static ViewModel and create the local database. Then the code-behind files for the pages are modified to consume the ViewModel and handle button clicks.

To complete the App class

  1. In the App.xaml.cs file, add the following directives to the top of the page.

    // Directives
    using LocalDatabaseSample.Model;
    using LocalDatabaseSample.ViewModel;
    
  2. In the App.xaml.cs file, add the following code inside the App class, above the class constructor.

            // The static ViewModel, to be used across the application.
            private static ToDoViewModel viewModel;
            public static ToDoViewModel ViewModel
            {
                get { return viewModel; }
            }
    

    This specifies a static ViewModel that can be used across the app by each of the pages.

  3. In the App.xaml.cs file, add the following code inside the class constructor for the App class, at the very end.

        // Specify the local database connection string.
        string DBConnectionString = "Data Source=isostore:/ToDo.sdf";
    
        // Create the database if it does not exist.
        using (ToDoDataContext db = new ToDoDataContext(DBConnectionString))
        {
            if (db.DatabaseExists() == false)
            {
                // Create the local database.
                db.CreateDatabase();
    
                // Prepopulate the categories.
                db.Categories.InsertOnSubmit(new ToDoCategory { Name = "Home" });
                db.Categories.InsertOnSubmit(new ToDoCategory { Name = "Work" });
                db.Categories.InsertOnSubmit(new ToDoCategory { Name = "Hobbies" });
    
                // Save categories to the database.
                db.SubmitChanges();
            }
        }
    
        // Create the ViewModel object.
        viewModel = new ToDoViewModel(DBConnectionString);
    
        // Query the local database and load observable collections.
        viewModel.LoadCollectionsFromDatabase();
    

    When the app object is instantiated, the local database is created if it does not already exist. When creating the database, the ToDoCategory table is prepopulated with three categories. Once the database exists, the ViewModel object is created and loaded with data.

To complete the MainPage class

  1. In the code-behind file for the main page, MainPage.xaml.cs, add the following directive.

    // Directive for the ViewModel.
    using LocalDatabaseSample.Model;
    
  2. In MainPage.xaml.cs, add the following code to the MainPage constructor, after the call to the InitializeComponent method.

                // Set the page DataContext property to the ViewModel.
                this.DataContext = App.ViewModel;
    

    This code sets the DataContext property of the page to the ViewModel. The page DataContext is distinctly different from the LINQ to SQL data context. In terms of MVVM, the former is a property of the View, and the latter defines the Model.

  3. In MainPage.xaml.cs, add the following code to the MainPage class.

            private void newTaskAppBarButton_Click(object sender, EventArgs e)
            {
                NavigationService.Navigate(new Uri("/NewTaskPage.xaml", UriKind.Relative));
            }
    
    
            private void deleteTaskButton_Click(object sender, RoutedEventArgs e)
            {
                // Cast the parameter as a button.
                var button = sender as Button;
    
                if (button != null)
                {
                    // Get a handle for the to-do item bound to the button.
                    ToDoItem toDoForDelete = button.DataContext as ToDoItem;
    
                    App.ViewModel.DeleteToDoItem(toDoForDelete);
                }
    
                // Put the focus back to the main page.
                this.Focus();
            }
    
            protected override void OnNavigatedFrom(System.Windows.Navigation.NavigationEventArgs e)
            {
                // Save changes to the database.
                App.ViewModel.SaveChangesToDB();    
            }
    

    This code shows the event-handling code for the page. When the new task button is clicked, the NavigationService object is used to navigate to the new task page. When the delete button is called, the corresponding ToDoItem object is retrieved and sent to the DeleteToDoItem method on the ViewModel. Whenever the user navigates away from the page, changes in the data context are automatically saved to the local database using the SaveChangesToDB method.

To complete the NewTaskPage class

  1. In the code-behind file for the new task page, NewTaskPage.xaml.cs, add the following directive.

    // Directive for the data model.
    using LocalDatabaseSample.Model;
    
  2. In NewTaskPage.xaml.cs, add the following code to the NewTaskPage constructor, after the call to the InitializeComponent method.

                // Set the page DataContext property to the ViewModel.
                this.DataContext = App.ViewModel;
    

    This code sets the DataContext property of the page to the ViewModel. The page DataContext is distinctly different from the LINQ to SQL data context. In terms of MVVM, the former is a property of the View, and the latter defines the Model.

  3. In NewTaskPage.xaml.cs, add the following code to the NewTaskPage class.

            private void appBarOkButton_Click(object sender, EventArgs e)
            {
                // Confirm there is some text in the text box.
                if (newTaskNameTextBox.Text.Length > 0)
                {
                    // Create a new to-do item.
                    ToDoItem newToDoItem = new ToDoItem
                    {
                        ItemName = newTaskNameTextBox.Text,
                        Category = (ToDoCategory)categoriesListPicker.SelectedItem
                    };
    
                    // Add the item to the ViewModel.
                    App.ViewModel.AddToDoItem(newToDoItem);
    
                    // Return to the main page.
                    if (NavigationService.CanGoBack)
                    {
                        NavigationService.GoBack();
                    }
                }
            }
    
            private void appBarCancelButton_Click(object sender, EventArgs e)
            {
                // Return to the main page.
                if (NavigationService.CanGoBack)
                {
                    NavigationService.GoBack();
                }
            }
    

    The appBarOkButton_Click method performs some minor validation on the task and then adds it to the ViewModel.

  4. You have now completed the app. Press F5 to start debugging and test the app.

See Also

Other Resources

LINQ to SQL support for Windows Phone 8

Local Database Sample

Windows Phone Training Kit

Introduction to LINQ

LINQ to SQL Documentation

Query Examples (LINQ to SQL)

How to use the Isolated Storage Explorer tool for Windows Phone 8

Developing a Windows Phone Application using the MVVM Pattern

A Case Study for Building Advanced Windows Phone Applications