How to create a basic local database app for Windows Phone 8

  • Article

[ 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 app’s local folder. This topic walks you through creating a basic single-page to-do list app that uses a local database for storing data. For more information about using a local database in your Windows Phone app, see Local database for Windows Phone 8.

Tip

To implement this app with an additional database table and app page, the Pivot control, and the Windows Phone Toolkit, while using the Model-View-ViewModel (MVVM) pattern, see How to create a local database app with MVVM for Windows Phone 8.

The following image shows an example of what the app may look like for an aspiring developer.

Entering text in the text box and touching add adds “to-do” items to the list in the app. Touching the delete icon removes items from the list. Because the to-do items are saved in a local database, the values in the list will persist between app launches.

This topic contains the following sections.

 

Creating the app UI

In this section, you create the app project and prepare the user interface.

To create the app UI

  1. Using the Windows Phone SDK, create a new project using the **Windows Phone App ** template.

  2. After the project is created, from the Project menu, select Add Existing Item. This opens the Add Existing Item menu, from which we can select an icon for deleting to-do items.

  3. In the Add Existing Item window, navigate to the following path and select the icon named appbar.delete.rest.png. This icon is designed for a dark background and is colored white; the icon may appear to be blank on the white background of the Add Existing Item window.

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

Note

This step assumes a default installation of Visual Studio. If you installed it in a different location, find the icon in the corresponding location.

  1. Click Add. This adds the icon to the list in Solution Explorer.

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

  3. On MainPage.xaml, remove the XAML code for the Grid named LayoutRoot and replace it 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 application and page title.-->
        <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
            <TextBlock x:Name="ApplicationTitle" Text="LOCAL DATABASE EXAMPLE" Style="{StaticResource PhoneTextNormalStyle}"/>
            <TextBlock x:Name="PageTitle" Text="to-do list" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
        </StackPanel>

        <!--ContentPanel - place additional content here.-->
        <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
            <Grid.RowDefinitions>
                <RowDefinition Height="Auto" />
                <RowDefinition Height="Auto" />
            </Grid.RowDefinitions>

            <!-- Bind the list box to the observable collection. -->
            <ListBox x:Name="toDoItemsListBox" ItemsSource="{Binding ToDoItems}" 
                     Grid.Row="0" Margin="12, 0, 12, 0" Width="440">
                <ListBox.ItemTemplate>
                    <DataTemplate>
                        <Grid HorizontalAlignment="Stretch" Width="440">
                            <Grid.ColumnDefinitions>
                                <ColumnDefinition Width="50" />
                                <ColumnDefinition Width="*" />                               
                                <ColumnDefinition Width="100" />                                
                            </Grid.ColumnDefinitions>
                            <CheckBox
                                IsChecked="{Binding IsComplete, Mode=TwoWay}"
                                Grid.Column="0"
                                VerticalAlignment="Center"/>
                            <TextBlock
                                Text="{Binding ItemName}"
                                FontSize="{StaticResource PhoneFontSizeLarge}"
                                Grid.Column="1"
                                VerticalAlignment="Center"/>
                            <Button
                                Grid.Column="2"
                                x:Name="deleteTaskButton"
                                BorderThickness="0"                                
                                Margin="0"
                                Click="deleteTaskButton_Click">
                                <Image Source="appbar.delete.rest.png"/>                                
                            </Button>
                        </Grid>
                    </DataTemplate>
                </ListBox.ItemTemplate>
            </ListBox>      

            <Grid Grid.Row="1">                
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="*" />
                    <ColumnDefinition Width="Auto" />
                </Grid.ColumnDefinitions>
                <TextBox
                    x:Name="newToDoTextBox"                    
                    Grid.Column="0"
                    Text="add new task"
                    FontFamily="{StaticResource PhoneFontFamilyLight}"                    
                    GotFocus="newToDoTextBox_GotFocus"/>
                <Button 
                    Content="add"
                    Grid.Column="1"
                    x:Name="newToDoAddButton"
                    Click="newToDoAddButton_Click"/>                
            </Grid>
        </Grid>
    </Grid>

    The XAML code adds two Grid elements to the app. One grid contains the toDoItemsListBox that displays to-do items. As more to-do items are bound to the list, the ListBox increases in size, pushing the second grid further down the screen. The other Grid element contains the newToDoTextBox and a Button for entering new to-do items.

Building the data context

In this section, you specify an object model that determines the database schema and you create the data context.

To build the data context

  1. Open the code-behind file for the main page, named MainPage.xaml.cs. This is the page that will contain most of the app logic. This app was deliberately kept to a single page for simplicity. A real-world app will often use the Model-View-ViewModel (MVVM) programming pattern. For more information, see Implementing the Model-View-ViewModel pattern for Windows Phone 8.

  2. This app requires references 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. At the top of the page, add the following directives.

    using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.ComponentModel;
using System.Collections.ObjectModel;

  2. Below the MainPage class, add the following code. This is an entity class named ToDoItem that represents the database table of the app in the local database. The class implements INotifyPropertyChanged for change tracking. Implementing INotifyPropertyChanging helps limit memory consumption related to change tracking. The table attribute, [Table], indicates to the LINQ to SQL runtime to map the class to a local database table.

    [Table]
public class ToDoItem : INotifyPropertyChanged, INotifyPropertyChanging
{
// 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");
        }
    }
}
// Version column aids update performance.
[Column(IsVersion = true)]
private Binary _version;

#region INotifyPropertyChanged Members

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));
    }
}

#endregion

#region INotifyPropertyChanging Members

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));
    }
}

#endregion
}

    The ToDoItem class has three public properties that correspond to three database columns:

    • ToDoItemId: an identifier column that is automatically populated by the database. This column is also the primary key, for which a database index is automatically created. These settings and more are specified with the LINQ to SQL Column mapping attribute written above the property syntax.

    • ItemName: a column to store the text of the to-do item.

    • IsComplete: a column to store the completion state of the to-do item.

Important Note:

To limit memory consumption required for change tracking, always implement the INotifyPropertyChanging interface on the objects in your data context.

  1. Below the MainPage class, add the following code. This is a class named ToDoDataContext. This class inherits from DataContext and is referred to as the data context. Most importantly, this code calls the base constructor and declares the database table named ToDoItems.

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

    // Pass the connection string to the base class.
    public ToDoDataContext(string connectionString)
        : base(connectionString)
    { }

    // Specify a single table for the to-do items.
    public Table<ToDoItem> ToDoItems;
}

Note

The database connection string is not required to be a static field; it is just for convenience in this particular example. For more information about connection strings, see Local database connection strings for Windows Phone 8.

Creating the database

In this section, you add the code that creates the database if it does not exist.

To create the database

  1. Open the code-behind file for the app, named App.xaml.cs. This contains code for the app object.

  2. In the constructor of the app object, named App(), add the following code at the end of the method. This database-creation code will be added here so that the database will be present before code from the main page runs.

    // Create the database if it does not exist.
using (ToDoDataContext db = new ToDoDataContext(ToDoDataContext.DBConnectionString))
{
    if (db.DatabaseExists() == false)
    {
        //Create the database
        db.CreateDatabase();
    }
}

    In the constructor of the ToDoDataContext class, the static field for the connection string is used for convenience. This is equivalent to passing the value "Data Source=isostore:/ToDo.sdf" to the constructor.

Completing the app

In this section, you complete the remaining portions of the app code.

To complete the app

  1. Open the code-behind file for the main page, named MainPage.xaml.cs. This file will contain the remainder of the app code.

  2. Replace the MainPage class with the following code. To support data-binding, this code adds the INotifyPropertyChanged interface to the signature of the MainPage class and the corresponding INotifyPropertyChanged members.

    public partial class MainPage : PhoneApplicationPage, INotifyPropertyChanged
{

    // Constructor
    public MainPage()
    {
        InitializeComponent();
    }


    #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
}

  3. In the MainPage class, above the class constructor, add the following code. This code declares a private data context named toDoDB and public property, an ObservableCollection, named ToDoItems. ToDoItems will be used to for binding data to the UI.

    // Data context for the local database
private ToDoDataContext toDoDB;

// Define an observable collection property that controls can bind to.
private ObservableCollection<ToDoItem> _toDoItems;
public ObservableCollection<ToDoItem> ToDoItems
{
    get
    {
        return _toDoItems;
    }
    set
    {
        if (_toDoItems != value)
        {
            _toDoItems = value;
            NotifyPropertyChanged("ToDoItems");
        }
    }
}

  4. Inside the MainPage class constructor, add the following code below the call to InitializeComponent. This code instantiates the (local database) data context, again using the static connection string DBConnectionString, and sets the main page as the root of the (page) data context.

    // Connect to the database and instantiate data context.
toDoDB = new ToDoDataContext(ToDoDataContext.DBConnectionString);

// Data context and observable collection are children of the main page.
this.DataContext = this;

  5. In the MainPage class, below the class constructor, add the following code. When the page is navigated to, this code queries the local database and populates the ToDoItems collection with the results. Because of data binding configured in the XAML, the toDoItemsListBox control will automatically be populated with the corresponding to-do items.

    protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
{
// Define the query to gather all of the to-do items.
var toDoItemsInDB = from ToDoItem todo in toDoDB.ToDoItems
                    select todo;

// Execute the query and place the results into a collection.
ToDoItems = new ObservableCollection<ToDoItem>(toDoItemsInDB);

    // Call the base method.
    base.OnNavigatedTo(e);
}

  6. In the MainPage class, below the class constructor, add the following code. This code is used for adding to-do items to the database. newToDoTextBox_GotFocus clears the text box before the user enters the text for a new to-do item. newToDoAddButton_Click creates a new ToDoItem object and adds it to the ToDoItems collection and the toDoDB data context. It is not until the page is navigated away from and the SubmitChanges method is called that new to-do items are actually added to the local database.

    private void newToDoTextBox_GotFocus(object sender, RoutedEventArgs e)
{
    // Clear the text box when it gets focus.
    newToDoTextBox.Text = String.Empty;
}

private void newToDoAddButton_Click(object sender, RoutedEventArgs e)
{
    // Create a new to-do item based on the text box.
    ToDoItem newToDo = new ToDoItem { ItemName = newToDoTextBox.Text };

    // Add a to-do item to the observable collection.
    ToDoItems.Add(newToDo);

    // Add a to-do item to the local database.
    toDoDB.ToDoItems.InsertOnSubmit(newToDo);          
}

protected override void OnNavigatedFrom(System.Windows.Navigation.NavigationEventArgs e)
{
    // Call the base method.
    base.OnNavigatedFrom(e);

    // Save changes to the database.
    toDoDB.SubmitChanges();
}
Important Note:

New items that have been added to the data context are not saved to the database until the SubmitChanges method is called.

  1. In the MainPage class, below the class constructor, add the following code. This code is called when the user presses the delete icon. It removes the corresponding to-do item from the ToDoItems collection and the toDoDB data context. It is not until the SubmitChanges method is called that deleted to-do items are actually removed from the local database.

    private void deleteTaskButton_Click(object sender, RoutedEventArgs e)
{
    // Cast 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;

        // Remove the to-do item from the observable collection.
        ToDoItems.Remove(toDoForDelete);

        // Remove the to-do item from the local database.
        toDoDB.ToDoItems.DeleteOnSubmit(toDoForDelete);

        // Save changes to the database.
        toDoDB.SubmitChanges();

        // Put the focus back to the main page.
        this.Focus();
    }
}
Important Note:

Items removed from the data context are not removed from the database until the SubmitChanges method is called.
  1. 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