31 Days of Mango | Day #30: Local Database

Day30-LocalDatabaseDay29-GlobalizationDay28-MediaLibraryDay27-MicrophoneAPIDay26-BackgroundFileTransfers

This article is Day #30 in a series called 31 Days of Mango, and was written by guest author Chris Woodruff.  Chris can be reached on Twitter at @cwoodruff.

What is a Local Database?

In the original version of Windows Phone 7, we could save data but it took either custom code or using third party databases like SterlingDB to have the relational data repository that some applications need. This limited the types of applications some developers could produce for users.

In Windows Phone 7 Mango, developers still have isolated storage to keep data and information stored for their applications but now have SQL CE as a relational storage feature to created even better applications for Windows Phone.

Like other database solutions for the original Windows Phone 7, the native SQL CE database built into Mango has it data stored in the device’s Isolated Storage. You can learn more about Isolated Storage here. Microsoft also did not create new ways to work with the data on the phone and instead implemented LINQ to SQL for all database operations. LINQ to SQL is used to do all functions for the application when dealing with data including creating the data, populating data into the database, getting data and finally saving and deleting data.

A good tutorial for LINQ to SQL is here on MSDN.

Setting up a Local Database for your Mango Application

Like all starting points for Windows Phone 7, we will start with creating the Windows Phone Databound Application project within Visual Studio 2010.

clip_image002

We could have started with just the Windows Phone Application project but I like the additional features that the Databound project gives you to allow your application to have better design patterns like Model-View-ViewModel (MVVM).

Next we will update the MainPage of the project to allow for the data to be added to the database. Our data example will be collecting ideas that we all have and need to remember. We will not go into much detail about the design of the MainPage but here is the XAML to get the look and feel for our Idea collector.

<phone:PhoneApplicationPage
   x:Class="_31DaysMangoStorage.MainPage"
   xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation&quot;
   xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml&quot;
   xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
   xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
   xmlns:d="http://schemas.microsoft.com/expression/blend/2008&quot;
   xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006&quot;
   mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
   d:DataContext="{d:DesignData SampleData/MainViewModelSampleData.xaml}"
   FontFamily="{StaticResource PhoneFontFamilyNormal}"
   FontSize="{StaticResource PhoneFontSizeNormal}"
   Foreground="{StaticResource PhoneForegroundBrush}"
   SupportedOrientations="Portrait" Orientation="Portrait"
   shell:SystemTray.IsVisible="True">

    <!–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="31 Days of Mango" Style="{StaticResource PhoneTextNormalStyle}"/>
            <TextBlock x:Name="PageTitle" Text="Idea Tracker" 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 IdeaItems}"
                    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="newIdeaTextBox"                    
                   Grid.Column="0"
                   Text="add new idea"
                   FontFamily="{StaticResource PhoneFontFamilyLight}"                    
                   GotFocus="newIdeaTextBox_GotFocus"/>
                <Button
                   Content="add"
                   Grid.Column="1"
                   x:Name="newIdeaAddButton"
                   Click="newIdeaAddButton_Click"/>
            </Grid>
        </Grid>
    </Grid>

</phone:PhoneApplicationPage>
 

Also to allow for your application to compile and run without doing anything the code below will be added to the MainPage.xaml.cs file inside the MainPage class implementation and after the constructor.

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

}

private void newIdeaAddButton_Click(object sender, RoutedEventArgs e)
{

}

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

}
 

Working with the Data Context

The Data Context is the point that will allow us to work with the database and also the proxy classes that represent our database tables. The data context is also a class and works against a number of “plain old CLR object” (POCO) classes that we will create for this project. The table objects that represent our database tables will contain a collection of entities for each table record stored in the database. Other details about our database are also given to us through the data context such as table keys and association mappings between tables.

Just a reminder that this local database has no connection with SQL Server 2008 R2 that is running locally on your PC or a server in your company or hosting provider and is only to hold data on your device.

There is not much to the DataContext class besides the connection string we are familiar in development and properties to each table that will exist in our database. Think of the DataContext as the “Hub” for your application’s data. The code for the sample application’s DataContext is below.

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

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

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

Note that IdeaItem type will be detailed in the next section covering creating the Database.

Creating the Database

Unlike applications that are run from your PC or on IIS 7, databases in Windows Phone 7 Mango must be created and initialized on the first instance your app runs on the phone. We will first look at creating the classes that will represent our database tables and then look at database initialization.

For each table that we need to be built and exposed through the database on our phone for the application a new POCO needs to be created. Since these classes represent the entities that will be stored in the database let’s call them Entity classes. To begin the Entity class must adhere to the following two interfaces:

· INotifyPropertyChanged — The INotifyPropertyChanged interface is used to notify clients, typically binding clients that a property value has changed.

· INotifyPropertyChanging — The INotifyPropertyChanging interface is used to notify clients, typically binding clients that a property value is changing.

These two interfaces will allow each entity to notify the DataContext that is in the process of changing or has changed. This will then be reflected to the XAML views of our application through the binding we have set up.

The Enity class must be annotated as a Table to allow the DataContext to know how to work with it. An Entity classes must also have private and public properties for each Entity property as well as having the private property annotated to give valuable metadata about the Entity property (aka the database column). Remember to have primary key properties for each of your Entity classes.

Below is the IdeaItem Entity class that will be located in the IdeaItems table referenced in the DataContext we created before.

[Table]
public class IdeaItem : INotifyPropertyChanged, INotifyPropertyChanging
{
    private int _ideaItemId;
    [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
    public int IdeaItemId
    {
        get
        {
            return _ideaItemId;
        }
        set
        {
            if (_ideaItemId != value)
            {
                NotifyPropertyChanging("IdeaItemId");
                _ideaItemId = value;
                NotifyPropertyChanged("IdeaItemId");
            }
        }
    }

    private string _itemName;
    [Column]
    public string ItemName
    {
        get
        {
            return _itemName;
        }
        set
        {
            if (_itemName != value)
            {
                NotifyPropertyChanging("ItemName");
                _itemName = value;
                NotifyPropertyChanged("ItemName");
            }
        }
    }

    private bool _isComplete;
    [Column]
    public bool IsComplete
    {
        get
        {
            return _isComplete;
        }
        set
        {
            if (_isComplete != value)
            {
                NotifyPropertyChanging("IsComplete");
                _isComplete = value;
                NotifyPropertyChanged("IsComplete");
            }
        }
    }

    [Column(IsVersion = true)]
    private Binary _version;
    public event PropertyChangedEventHandler PropertyChanged;

    private void NotifyPropertyChanged(string propertyName)
    {
        if (PropertyChanged != null)
        {
            PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }

    public event PropertyChangingEventHandler PropertyChanging;
    private void NotifyPropertyChanging(string propertyName)
    {
        if (PropertyChanging != null)
        {
            PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
        }
    }
}
 

Finally we must create the database is it does not exist. This will be done in the Application class’s constructor. You will find this in the App.xaml.cs file. The code that will be added to the end of the constructor method is as followed.

using (IdeaDataContext db = new IdeaDataContext(IdeaDataContext.DBConnectionString))
{
    if (db.DatabaseExists() == false)
    {
        db.CreateDatabase();
    }
}
 

We now have a database that has been created and initialized in application’s Isolated Storage. One more thing to remember is that local databases in Windows Phone 7 mango cannot be shared directly between apps on the phone due to the sandboxing security set up in the Windows Phone 7 Mango operating system.

LINQ to SQL Support for Windows Phone Mango

Windows Phone 7.1 SDK allows for some but not all features of LINQ to SQL inside the Windows Phone. The following are just a few things to remember when working with data and LINQ to SQL in Windows Phone 7 Mango.

· ExecuteCommand is not supported

· ADO.NET Objects (such as DataReader) are not supported

· Only Microsoft SQL Server Compact Edition (SQL CE) data types are supported

To get more limitations and features of using LINQ to SQL on Mango please read the MSDN page here.

Retrieving Data from a Local Database

To retrieve data or any data related work with the local database, we must first create the DataContext and connect to the database. This will happen in MainPage.xaml.cs through a private variable for the DataContext, an observable collection property for the ideas in the database table and in the MainPage constructor as followed.

private IdeaDataContext ideaDB;

private ObservableCollection<IdeaItem> _ideaItems;
public ObservableCollection<IdeaItem> IdeaItems
{
    get
    {
        return _ideaItems;
    }
    set
    {
        if (_ideaItems != value)
        {
            _ideaItems = value;
            NotifyPropertyChanged("IdeaItems");
        }
    }
}

public MainPage()
{
    InitializeComponent();

    ideaDB = new IdeaDataContext(IdeaDataContext.DBConnectionString);
    this.DataContext = this;
}
 

To get our ideas located in the local database we will use LINQ to SQL to query and get the collection back from the database via the DataContext.

protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
{
    var ideaItemsInDB = from IdeaItem todo in ideaDB.IdeaItems
                        select todo;

    IdeaItems = new ObservableCollection<IdeaItem>(ideaItemsInDB);
    base.OnNavigatedTo(e);
}
 

The ideas are now bound and reflected in the MainPage.xaml.

clip_image003

Storing Data to a Local Database

Finally we will build the saving of our idea data to the local database. We will not send the ideas to the database until we need to increase performance. We will keep all of the ideas in the local observable collection we created as a MainPage property (IdeaItems). The addition of new ideas to the local database will take place on the button click when the new idea is added to the IdeaItems collection.

private void newIdeaAddButton_Click(object sender, RoutedEventArgs e)
{
    IdeaItem newIdea = new IdeaItem { ItemName = newIdeaTextBox.Text };

    IdeaItems.Add(newIdea);
    ideaDB.IdeaItems.InsertOnSubmit(newIdea);
}
 

As mentioned before the ideas collected from the user will not be stored in the database until the application has transitioned from the MainPage either when the user exits the application or the application moves to a new page in the application. The code for the OnNavigateFrom event for the MainPage is below.

protected override void OnNavigatedFrom(System.Windows.Navigation.NavigationEventArgs e)
{
    base.OnNavigatedFrom(e);
    ideaDB.SubmitChanges();
}
 

Summary

So that’s pretty much it!  You now have a simple way to create, store, and retrieve relational data in your Windows Phone applications.  How are you going to use this?

To download a full Windows Phone project that includes all of the code and concepts from this article, click the Download Code button below.

download

Tomorrow, in the final article of this series, we will cover the best practices for promoting your Windows Phone applications.  See you then!

toolsbutton

12 thoughts on “31 Days of Mango | Day #30: Local Database

  1. Pingback: The Morning Brew - Chris Alcock » The Morning Brew #992

  2. Pingback: 31 Days of Windows Phone Mango | Day # 30: Local Database

  3. Pingback: 31 Tage Mango | Tag #30: Lokale Datenbank | leitning.de

  4. Pingback: 31 Days of Mango | Day #31: Promoting Your App « Blankenblog

  5. HI,
    I’m surprised to see INotifyPropertyChanged in the model, usually this is used in the viewmodel to inform the view. Can you explain me why ?

    Thierry

  6. Pingback: WP Astuces » Compte rendu des 31 jours de Windows Phone Mango – Partie 1

  7. From Chris Woodruff, the author of this article:

    I understand the comment and would say that the best practice is to have the INotifyPropertyChanged in the View but I did not want to go down the path of explaining MVVM to the reader so I kept it as simple as possible. Would I ever code a production application with INotifyPropertyChanged in the View? Defenitely not but I am really trying to show how to create a use a local SQL CE database in WP7 and use it. The bottom line is that INotifyPropertyChanged can be used in the View. It is just not the best place for it. I hope you understand where I am coming from and can look at the big goal of the blog post.

    I do appreciate the comment and the passion you have.

    Chris Woodruff aka “Woody”
    Web: http://www.chriswoodruff.com
    Visit our podcast: http://deepfriedbytes.com

  8. Pingback: Día 30: Base de datos local | La Liga Silverlight

  9. How do I add a database as Content for my app?
    I need a readonly database for my App. So I thought I could create/setup the database through another project and then use the created database as content on my App. But the problem is in VS 2010 dev express, I cant find any option to add database to a windows phone project.

  10. Pingback: 31 Days Mango | For things that fit in the pocket

  11. Pingback: {31 день с Mango} День 30: Локальная база данных | Программизм

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s