SQLite in Windows Phone 8: The sequel

by Nico

February

Read the first part: SQLite with a bit of MVVM Light in Windows Phone 8

Last week I’ve blogged about using SQLite in an MVVM scenario in Windows Phone 8. Since, in my opinion, the post was already getting to an epic length I decided to leave out a part for this week.

I’ll try to keep it a bit shorter this time, should be able to. The part I left out was using relationships and foreign keys with SQLite and the sqlite-net library. I’ll be building upon the project of the previous post, so the task schedule thingy. What we’ll be doing is adding the possibility of creating subtasks so that a task can be divided into smaller tasks. Note that this is just a proof of concept (POC) it’s in no way a complete application and it will have some strange things but that’s the whole fun of a POC.

Let’s kick things off with the creation of a new class for holding the SubTasks.

Code Snippet
 [Table("SubTask")]
public class SubTask : INotifyPropertyChanged
{
    private int _id;
    private string _title;
    private DateTime _date;
    private int _taskId;
 
    [PrimaryKey, AutoIncrement]
    public int Id
    {
        get { return _id; }
        set
        {
            if (value == _id) return;
            _id = value;
            OnPropertyChanged();
        }
    }
 
    public string Title
    {
        get { return _title; }
        set
        {
            if (value == _title) return;
            _title = value;
            OnPropertyChanged();
        }
    }
 
    public DateTime Date
    {
        get { return _date; }
        set
        {
            if (value.Equals(_date)) return;
            _date = value;
            OnPropertyChanged();
        }
    }
 
    [Indexed]
    public int TaskId
    {
        get { return _taskId; }
        set
        {
            if (value == _taskId) return;
            _taskId = value;
            OnPropertyChanged();
        }
    }
 
    public event PropertyChangedEventHandler PropertyChanged;
 
    [NotifyPropertyChangedInvocator]
    protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
    {
        PropertyChangedEventHandler handler = PropertyChanged;
        if (handler != null) handler(this, new PropertyChangedEventArgs(propertyName));
    }
}
 

By using attributes we define this class as being a column in the SQLite database and the property Id being a primary key with auto increment. This is all explained in more detail in the previous post. A new attribute here is Indexed on the TaskId property. That attribute states that that property will be an index, it will hold the primary key of the Task table.

Remember that the database was created in the App.xaml.cs? We’ll need to add a line there to create the SubTask table as well. If you haven’t shut down the emulator between the previous post and this one, or you’re testing on an actual device, you’ll need to remove the app so that the database is destroyed or the new table won’t get generated.

Code Snippet
 private async void Application_Launching(object sender, LaunchingEventArgs e)
{
    try
    {
        await ApplicationData.Current.LocalFolder.GetFileAsync("taskDB.db");
        Connection = new SQLiteAsyncConnection("taskDB.db");
    }
    catch (FileNotFoundException)
    {
        CreateDB();
    }
}
 
private async void CreateDB()
{
    Connection = new SQLiteAsyncConnection("taskDB.db");
 
    await Connection.CreateTableAsync<Task>();
    await Connection.CreateTableAsync<SubTask>();
}
 

I’ve added the complete snippet as reference, line 19 is the new one.

Next up is the view, this will be quite similar to the Mainpage, a pivot with two pages. One containing a form for adding a new subtask and one containing a list of all subtasks.

Code Snippet
 <phone:PhoneApplicationPage.Resources>
    <DataTemplate x:Key="TaskListItemTemplate">
        <StackPanel>
            <TextBlock x:Name="Title"
                        Style="{StaticResource JumpListAlphabetStyle}"
                        Text="{Binding Title}"
                        TextWrapping="Wrap" />
            <TextBlock x:Name="Date"
                        Margin="12,0,0,0"
                        Text="{Binding Date}"
                        TextWrapping="Wrap" />
            <TextBlock Text="Main task ID" TextWrapping="Wrap" />
            <TextBlock Text="{Binding TaskId}" />
 
        </StackPanel>
    </DataTemplate>
</phone:PhoneApplicationPage.Resources>
 
 
<!--  Buttons are defined using the behaviors in the Cimbalino toolkit to allow a bindable appbar  -->
<phone:PhoneApplicationPage.ApplicationBar>
    <shell:ApplicationBar IsMenuEnabled="True" IsVisible="True" />
</phone:PhoneApplicationPage.ApplicationBar>
 
 
<!--  LayoutRoot is the root grid where all page content is placed  -->
<Grid x:Name="LayoutRoot" Background="Transparent">
    <!--  Bindable Appbar buttons  -->
    <i:Interaction.Behaviors>
        <behaviors:ApplicationBarBehavior>
            <behaviors:ApplicationBarIconButton Command="{Binding SaveNewSubTaskCommand,
                                                                    Mode=OneTime}"
                                                IconUri="/Assets/AppBar/save.png"
                                                Text="Save Task" />
        </behaviors:ApplicationBarBehavior>
    </i:Interaction.Behaviors>
 
    <Grid.RowDefinitions>
        <RowDefinition Height="Auto" />
        <RowDefinition Height="*" />
    </Grid.RowDefinitions>
    <phone:Pivot Title="SQLite POC" Grid.Row="1">
        <phone:PivotItem x:Name="NewTask"
                            CacheMode="{x:Null}"
                            Header="new subtask">
            <StackPanel>
                <TextBlock Text="Main task" />
                <toolkit:ListPicker ItemsSource="{Binding Tasks}" SelectedItem="{Binding SelectedTask, Mode=TwoWay}">
                    <toolkit:ListPicker.ItemTemplate>
                        <DataTemplate>
                            <TextBlock Text="{Binding Title}" />
                        </DataTemplate>
                    </toolkit:ListPicker.ItemTemplate>
                </toolkit:ListPicker>
                <TextBlock Text="Title" TextWrapping="Wrap" />
                <TextBox x:Name="TextBoxTitle"
                            Height="72"
                            Text="{Binding NewSubTask.Title,
                                        Mode=TwoWay}"
                            TextWrapping="Wrap" />
                <TextBlock Text="Complete by" TextWrapping="Wrap" />
                <toolkit:DatePicker Value="{Binding NewSubTask.Date, Mode=TwoWay}" />
            </StackPanel>
        </phone:PivotItem>
        <phone:PivotItem x:Name="AllTasks"
                            CacheMode="{x:Null}"
                            Header="all subtasks">
            <phone:LongListSelector ItemTemplate="{StaticResource TaskListItemTemplate}" ItemsSource="{Binding SubTasks}" />
        </phone:PivotItem>
    </phone:Pivot>
</Grid>
 

The only new thing here is the ListPicker, it’s a control in the Windows Phone Toolkit (get it from NuGet) and it’s kind of like Windows Phone’s version of a combobox. The ItemTemplate is a TextBlock containing the Title of the Task.

If you want details on the bindings, read the previous post. The DataContext for this page binds to a SubTaskViewModel through the ViewModelLocator

Code Snippet
 DataContext="{Binding SubTask, Source={StaticResource Locator}}"
 

And here’s the SubTaskViewModel

Code Snippet
 public class SubTaskViewModel : ViewModelBase
{
    private readonly IDataService _dataService;
    private readonly INavigationService _navigationService;
 
    private IList<SubTask> _subTasks;
    public IList<SubTask> SubTasks
    {
        get
        {
            return _subTasks;
        }
 
        set
        {
            if (Equals(_subTasks, value))
            {
                return;
            }
 
            _subTasks = value;
            RaisePropertyChanged(() => SubTasks);
        }
    }
 
    private SubTask _newSubTask;
    private IList<Task> _tasks;
    private Task _selectedTask;
 
    public SubTask NewSubTask
    {
        get
        {
            return _newSubTask;
        }
 
        set
        {
            if (_newSubTask == value)
            {
                return;
            }
 
            _newSubTask = value;
            RaisePropertyChanged(() => NewSubTask);
        }
    }
 
    public RelayCommand SaveNewSubTaskCommand
    {
        get
        {
            return new RelayCommand(async () =>
                                                {
                                                    NewSubTask.TaskId = SelectedTask.Id;
                                                    await _dataService.Save(NewSubTask);
                                                    SubTasks.Add(NewSubTask);
                                                    NewSubTask = new SubTask { Date = DateTime.Today };
                                                });
        }
    }
 
    public IList<Task> Tasks
    {
        get { return _tasks; }
        set
        {
            if (Equals(_tasks, value))
            {
                return;
            }
 
            _tasks = value;
            RaisePropertyChanged(() => Tasks);
        }
    }
 
    public Model.Task SelectedTask
    {
        get { return _selectedTask; }
        set
        {
            if (Equals(_selectedTask, value))
            {
                return;
            }
 
            _selectedTask = value;
            RaisePropertyChanged(() => SelectedTask);
        }
    }
 
    public SubTaskViewModel(INavigationService navigationService, IDataService dataService)
    {
        _navigationService = navigationService;
        _dataService = dataService;
        SubTasks = new List<SubTask>();
 
        NewSubTask = new SubTask { Date = DateTime.Today };
        GetData();
    }
 
    private async void GetData()
    {
        Tasks = await _dataService.LoadTasks();
        SubTasks = await _dataService.LoadSubTasks();
    }
}
 

In this viewmodel we’ll have a list of both the Tasks and the SubTasks because we’ll have to select a Task to attach a SubTask to. The constructor loads in the NavigationService and the DataService from the Ioc container, Tasks and SubTasks get loaded and we’re good to go. Now the navigation isn’t really what it should be in this POC, because you have to go to a Task edit page, click the Add Subtask button and then select the Task you want to create a subtask for, it’s a bit of double work but I don’t care Smile .

Remember the DataService? It’s the class that takes care of all the CRUD operations in our app, it’ll need to do the same for the subtasks. I’m a bit lazy, so I’ve only did the insert and read part. Here’s the read snippet

Code Snippet
 public async Task<IList<SubTask>> LoadSubTasks()
{
    return await App.Connection.Table<SubTask>().ToListAsync();
}
 

Exactly the same as for reading out the Tasks. Now, for saving I altered the SaveTask function to be generic.

Code Snippet
 public async Task Save<T>(T newTask)
{
    await App.Connection.InsertAsync(newTask);
}
 

This function takes in basically anything and saves it in the DB, if you pass something in of a type that doesn’t have a table in the DB the app will crash, hard. So be careful how you use this and you might think of adding some error handling.

And there we are, a working SQLite proof of concept with relationships and everything. Since a picture says more than a thousand words, here are some screenshots.

The Edit Task screen with the Add subtask button

The Add SubTask screen with all the Tasks in a listpicker

List of SubTasks with the Task ID

The entire project can be downloaded in a zip file here (Link to my SkyDrive).

d592af01-2735-4441-8575-4ee9e34e89f2|1|5.0

Tags:

.Net | MVVM Light | Metro | WP8 | sqlite

Tony

3/26/2013 2:32:16 PM #

Hi Nico,

Great read and helped me understand SQLite and list picker. I have a question though. How about the reverse logic. Lets say I have a view called PartsView and its context is the viewmodel of PartsViewModel. Lets say on the view, PartsView, there are my normal textboxes, (PartNumber, Color.....) but I also have a ListPicker that is for PartType. In my PartModel I have a field called PartTypeID. So then, the user in looking at the Parts view. They enter, PartNumber, Color, then they tap on the textbox, "Part Type", it opens a ListPicker in FullMode. So then they select a PartType. Lets say the part type they selected is "Memory". After they select it, it closes the FullMode ListPicker view and returns back to the PartsView and then they see the textbox, "Part Type" populated with "Memory"

I'm assuming this is a parent child relationship, but do you have an example of that? Kind of like on a Windows Form, where the user selects the item from a combo or dropdown box.

Thanks,

Tony

Nico

3/27/2013 11:52:31 AM #

Hi Tony,

if I understand your question correctly you'd want to add a property to your PartsViewModel for SelectedPartType and do a two-way binding on your listpicker's selectedItem property

3/28/2013 4:40:34 AM #

Yee sir. Kind of like on a windows form that has a combo box.

SQLite in Windows Phone 8: The sequel

Related posts

Comments (3) -

Pingbacks and trackbacks (2)+

Add comment

Page List

About the author

Twitter

Month List

SQLite in Windows Phone 8: The sequel

Related posts

Comments (3) -

Pingbacks and trackbacks (2)+

Add comment

Page List

About the author

Twitter

Month List

Blogroll

Newsletter

Thank you