Nico's digital footprint

I grew up in the nineties, that makes me awesome by default

SQLite in Windows Phone 8: The sequel

by Nico

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
  1. [Table("SubTask")]
  2. public class SubTask : INotifyPropertyChanged
  3. {
  4.     private int _id;
  5.     private string _title;
  6.     private DateTime _date;
  7.     private int _taskId;
  8.  
  9.     [PrimaryKey, AutoIncrement]
  10.     public int Id
  11.     {
  12.         get { return _id; }
  13.         set
  14.         {
  15.             if (value == _id) return;
  16.             _id = value;
  17.             OnPropertyChanged();
  18.         }
  19.     }
  20.  
  21.     public string Title
  22.     {
  23.         get { return _title; }
  24.         set
  25.         {
  26.             if (value == _title) return;
  27.             _title = value;
  28.             OnPropertyChanged();
  29.         }
  30.     }
  31.  
  32.     public DateTime Date
  33.     {
  34.         get { return _date; }
  35.         set
  36.         {
  37.             if (value.Equals(_date)) return;
  38.             _date = value;
  39.             OnPropertyChanged();
  40.         }
  41.     }
  42.  
  43.     [Indexed]
  44.     public int TaskId
  45.     {
  46.         get { return _taskId; }
  47.         set
  48.         {
  49.             if (value == _taskId) return;
  50.             _taskId = value;
  51.             OnPropertyChanged();
  52.         }
  53.     }
  54.  
  55.     public event PropertyChangedEventHandler PropertyChanged;
  56.  
  57.     [NotifyPropertyChangedInvocator]
  58.     protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
  59.     {
  60.         PropertyChangedEventHandler handler = PropertyChanged;
  61.         if (handler != null) handler(this, new PropertyChangedEventArgs(propertyName));
  62.     }
  63. }

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
  1. private async void Application_Launching(object sender, LaunchingEventArgs e)
  2. {
  3.     try
  4.     {
  5.         await ApplicationData.Current.LocalFolder.GetFileAsync("taskDB.db");
  6.         Connection = new SQLiteAsyncConnection("taskDB.db");
  7.     }
  8.     catch (FileNotFoundException)
  9.     {
  10.         CreateDB();
  11.     }
  12. }
  13.  
  14. private async void CreateDB()
  15. {
  16.     Connection = new SQLiteAsyncConnection("taskDB.db");
  17.  
  18.     await Connection.CreateTableAsync<Task>();
  19.     await Connection.CreateTableAsync<SubTask>();
  20. }

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
  1. <phone:PhoneApplicationPage.Resources>
  2.     <DataTemplate x:Key="TaskListItemTemplate">
  3.         <StackPanel>
  4.             <TextBlock x:Name="Title"
  5.                         Style="{StaticResource JumpListAlphabetStyle}"
  6.                         Text="{Binding Title}"
  7.                         TextWrapping="Wrap" />
  8.             <TextBlock x:Name="Date"
  9.                         Margin="12,0,0,0"
  10.                         Text="{Binding Date}"
  11.                         TextWrapping="Wrap" />
  12.             <TextBlock Text="Main task ID" TextWrapping="Wrap" />
  13.             <TextBlock Text="{Binding TaskId}" />
  14.  
  15.         </StackPanel>
  16.     </DataTemplate>
  17. </phone:PhoneApplicationPage.Resources>
  18.  
  19.  
  20. <!--  Buttons are defined using the behaviors in the Cimbalino toolkit to allow a bindable appbar  -->
  21. <phone:PhoneApplicationPage.ApplicationBar>
  22.     <shell:ApplicationBar IsMenuEnabled="True" IsVisible="True" />
  23. </phone:PhoneApplicationPage.ApplicationBar>
  24.  
  25.  
  26. <!--  LayoutRoot is the root grid where all page content is placed  -->
  27. <Grid x:Name="LayoutRoot" Background="Transparent">
  28.     <!--  Bindable Appbar buttons  -->
  29.     <i:Interaction.Behaviors>
  30.         <behaviors:ApplicationBarBehavior>
  31.             <behaviors:ApplicationBarIconButton Command="{Binding SaveNewSubTaskCommand,
  32.                                                                     Mode=OneTime}"
  33.                                                 IconUri="/Assets/AppBar/save.png"
  34.                                                 Text="Save Task" />
  35.         </behaviors:ApplicationBarBehavior>
  36.     </i:Interaction.Behaviors>
  37.  
  38.     <Grid.RowDefinitions>
  39.         <RowDefinition Height="Auto" />
  40.         <RowDefinition Height="*" />
  41.     </Grid.RowDefinitions>
  42.     <phone:Pivot Title="SQLite POC" Grid.Row="1">
  43.         <phone:PivotItem x:Name="NewTask"
  44.                             CacheMode="{x:Null}"
  45.                             Header="new subtask">
  46.             <StackPanel>
  47.                 <TextBlock Text="Main task" />
  48.                 <toolkit:ListPicker ItemsSource="{Binding Tasks}" SelectedItem="{Binding SelectedTask, Mode=TwoWay}">
  49.                     <toolkit:ListPicker.ItemTemplate>
  50.                         <DataTemplate>
  51.                             <TextBlock Text="{Binding Title}" />
  52.                         </DataTemplate>
  53.                     </toolkit:ListPicker.ItemTemplate>
  54.                 </toolkit:ListPicker>
  55.                 <TextBlock Text="Title" TextWrapping="Wrap" />
  56.                 <TextBox x:Name="TextBoxTitle"
  57.                             Height="72"
  58.                             Text="{Binding NewSubTask.Title,
  59.                                         Mode=TwoWay}"
  60.                             TextWrapping="Wrap" />
  61.                 <TextBlock Text="Complete by" TextWrapping="Wrap" />
  62.                 <toolkit:DatePicker Value="{Binding NewSubTask.Date, Mode=TwoWay}" />
  63.             </StackPanel>
  64.         </phone:PivotItem>
  65.         <phone:PivotItem x:Name="AllTasks"
  66.                             CacheMode="{x:Null}"
  67.                             Header="all subtasks">
  68.             <phone:LongListSelector ItemTemplate="{StaticResource TaskListItemTemplate}" ItemsSource="{Binding SubTasks}" />
  69.         </phone:PivotItem>
  70.     </phone:Pivot>
  71. </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
  1. DataContext="{Binding SubTask, Source={StaticResource Locator}}"

And here’s the SubTaskViewModel

Code Snippet
  1. public class SubTaskViewModel : ViewModelBase
  2. {
  3.     private readonly IDataService _dataService;
  4.     private readonly INavigationService _navigationService;
  5.  
  6.     private IList<SubTask> _subTasks;
  7.     public IList<SubTask> SubTasks
  8.     {
  9.         get
  10.         {
  11.             return _subTasks;
  12.         }
  13.  
  14.         set
  15.         {
  16.             if (Equals(_subTasks, value))
  17.             {
  18.                 return;
  19.             }
  20.  
  21.             _subTasks = value;
  22.             RaisePropertyChanged(() => SubTasks);
  23.         }
  24.     }
  25.  
  26.     private SubTask _newSubTask;
  27.     private IList<Task> _tasks;
  28.     private Task _selectedTask;
  29.  
  30.     public SubTask NewSubTask
  31.     {
  32.         get
  33.         {
  34.             return _newSubTask;
  35.         }
  36.  
  37.         set
  38.         {
  39.             if (_newSubTask == value)
  40.             {
  41.                 return;
  42.             }
  43.  
  44.             _newSubTask = value;
  45.             RaisePropertyChanged(() => NewSubTask);
  46.         }
  47.     }
  48.  
  49.     public RelayCommand SaveNewSubTaskCommand
  50.     {
  51.         get
  52.         {
  53.             return new RelayCommand(async () =>
  54.                                                 {
  55.                                                     NewSubTask.TaskId = SelectedTask.Id;
  56.                                                     await _dataService.Save(NewSubTask);
  57.                                                     SubTasks.Add(NewSubTask);
  58.                                                     NewSubTask = new SubTask { Date = DateTime.Today };
  59.                                                 });
  60.         }
  61.     }
  62.  
  63.     public IList<Task> Tasks
  64.     {
  65.         get { return _tasks; }
  66.         set
  67.         {
  68.             if (Equals(_tasks, value))
  69.             {
  70.                 return;
  71.             }
  72.  
  73.             _tasks = value;
  74.             RaisePropertyChanged(() => Tasks);
  75.         }
  76.     }
  77.  
  78.     public Model.Task SelectedTask
  79.     {
  80.         get { return _selectedTask; }
  81.         set
  82.         {
  83.             if (Equals(_selectedTask, value))
  84.             {
  85.                 return;
  86.             }
  87.  
  88.             _selectedTask = value;
  89.             RaisePropertyChanged(() => SelectedTask);
  90.         }
  91.     }
  92.  
  93.     public SubTaskViewModel(INavigationService navigationService, IDataService dataService)
  94.     {
  95.         _navigationService = navigationService;
  96.         _dataService = dataService;
  97.         SubTasks = new List<SubTask>();
  98.  
  99.         NewSubTask = new SubTask { Date = DateTime.Today };
  100.         GetData();
  101.     }
  102.  
  103.     private async void GetData()
  104.     {
  105.         Tasks = await _dataService.LoadTasks();
  106.         SubTasks = await _dataService.LoadSubTasks();
  107.     }
  108. }

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
  1. public async Task<IList<SubTask>> LoadSubTasks()
  2. {
  3.     return await App.Connection.Table<SubTask>().ToListAsync();
  4. }

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

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

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


Tags:

.Net | MVVM Light | Metro | WP8 | sqlite

blog comments powered by Disqus
  Log in

About the author

Hi,

My name is Nico, I’m an MVP Windows Platform Development living in Belgium.
I’m currently employed as a .NET consultant at RealDolmen, one of Belgium’s leading IT single source providers.

I'm also founding member and board member of the Belgian Metro App Developer Network, a user group focussed on Windows 8 and Windows Phone development. If you're in Belgium feel free to drop by if we're doing an event. http://www.madn.be

Since June 2012 I'm a proud member of Microsoft's Extended Experts Team Belgium. And in February 2013 I became a member of DZone's Most Valuable Bloggers family.

In 2013 I became a book author and wrote "Windows 8 app projects, XAML & C# edition".

In 2014 I received the MVP award for the very first time.

I hope to get feedback from my readers either through comments, mail (nico_vermeir@hotmail.com), twitter, facebook, …

 

mvp

 

mvp

 

 

MeetLogo

 

MVBLogo

mybook

 

Month List