WinRT app guide: Step 10: Adding a lightweight database (SQLite) and CRUD support + two-way element binding
To read the other steps in the step by step guide for creating a Metr0/ WInRT application go here
Windows Store App MiHSP using the RadControls for Windows 8
A fairly delayed post unfortunately, to get us moving faster I’ve decided to add more features this time around so prepare for some big steps!
So far we haven’t discussed how we are going to store data, but I think it’s about time we do that. And to do this we will use SQLite. To do this we need to add SQLite for Windows Runtime. You do this by selecting Tools -> Extensions -> Online (search). After installing add a reference to it, by right clicking on the project -> Add Reference -> Extensions . Select SQLite as well as C++ Runtime Package , you’ll need both. To work with SQLite in C# we need a wrapper, and for now we will use sqlite-net. Open your Package Manager Console type: PM> Install-Package sqlite-net and hit enter.
You are now ready to go.
Let’s first add the rest of the properties on our model to the View. Just as we did last time, add them to the datatemplate and to the editing field using a two-way element binding.
Notice how the item changes in the ListView as we edit in the edit field!
For the scores we use sliders, and for the input just plain old TextBox and TextBlock. Easy!
.
It’s time to do some major work on our code, and we start by redoing the ‘repository’ so it works with the database. For now we will do synchronous work – but we will get back to this.
Run the CreateTable(); method once to create the table – we’ll get back to handle the creation of the table after deployment later.
Okey, the code behind. I must admit, I’m not a big fan of code behind, but I am keeping this guide VERY simple so everybody gets a chance to follow- and also why you see those beautiful click events and handlers we have added. This is a very small application so to keep things as simple as possible we are handling the actions from the user on click events and then resetting the list with items (which due to the grouping for the presentation in the view cannot simply be a simple ObservableCollection property).
A last thing we will do is to remove the fake data we had for the statistics so we can really start having some fun with the RadControls. So we hook up the ‘real’ data instead, and we can now see how much time we have spent on each subject in the list.
Code:
[sourcecode language=“XML”]
<Page.Resources>
<CollectionViewSource x:Name="activitiesCollection" IsSourceGrouped="True"></CollectionViewSource>
</Page.Resources>
<Grid Style="{StaticResource LayoutRootStyle}" x:Name="LayoutRoot">
<Grid.Resources>
</Grid.Resources>
<Grid.RowDefinitions>
<RowDefinition Height="140"/>
<RowDefinition Height="\*"/>
<RowDefinition Height="100"/>
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="120"/>
<ColumnDefinition Width="\*"/>
<ColumnDefinition Width="120"/>
</Grid.ColumnDefinitions>
<TextBlock Grid.Column="1" HorizontalAlignment="Left" VerticalAlignment="Bottom" Style="{StaticResource HeaderTextStyle}" Foreground="#39ACE3" Text="MIH!SP"/>
<Grid Grid.Column="1" Grid.Row="1">
<Grid.ColumnDefinitions>
<ColumnDefinition x:Name="column" Width="2\*"/>
<ColumnDefinition Width="\*"/>
<ColumnDefinition Width="3\*"/>
</Grid.ColumnDefinitions>
<ListView x:Name="itemListView" Padding="0,10,0,0" ItemsSource="{Binding Source={StaticResource activitiesCollection}}" SelectedItem="{Binding ActivityToAdd}">
<ListView.GroupStyle>
<GroupStyle>
<GroupStyle.HeaderTemplate>
<DataTemplate>
<Border Background="#A66C11" Width="{Binding ElementName=column, Path=ActualWidth}" HorizontalAlignment="Stretch">
<TextBlock Text='{Binding Key}' Style="{StaticResource SubheaderTextStyle}" Margin="5"/>
</Border>
</DataTemplate>
</GroupStyle.HeaderTemplate>
</GroupStyle>
</ListView.GroupStyle>
<ListView.ItemTemplate>
<DataTemplate>
<Border Background="#004FC6" Padding="20" >
<StackPanel Orientation="Vertical" HorizontalAlignment="Stretch">
<StackPanel.Resources>
<Style BasedOn="{StaticResource TitleTextStyle}" TargetType="TextBlock" />
</StackPanel.Resources>
<TextBlock Text="{Binding Title}" Style="{StaticResource TitleTextStyle}"/>
<TextBlock Text="{Binding CategoryName}"/>
<TextBlock TextWrapping="Wrap" Text="{Binding Comment}" Style="{StaticResource BodyTextStyle}"/>
<StackPanel Orientation="Horizontal">
<TextBlock Text="Importance" Margin="0,0,10,0"/>
<TextBlock Text="{Binding ImportanceScore}" Foreground="Yellow"/>
<TextBlock Text="Urgency" Margin="10,0,10,0"/>
<TextBlock Text="{Binding UrgencyScore}" Foreground="Yellow"/>
<TextBlock Text="Worth" Margin="10,0,10,0"/>
<TextBlock Text="{Binding WorthScore}" Foreground="Yellow"/>
</StackPanel>
<StackPanel Orientation="Horizontal">
<TextBlock Text="Time spent" Margin="0,0,10,0"/>
<TextBlock Text="{Binding TimeSpent}" Foreground="Yellow"/>
<TextBlock Text="Time estimate" Margin="10,0,10,0"/>
<TextBlock Text="{Binding TimeEstimate}" Foreground="Yellow"/>
</StackPanel>
</StackPanel>
</Border>
</DataTemplate>
</ListView.ItemTemplate>
<ListView.ItemContainerStyle>
<Style TargetType="ListViewItem">
<Setter Property="HorizontalContentAlignment" Value="Stretch"></Setter>
<Setter Property="Padding" Value="0,5"></Setter>
</Style>
</ListView.ItemContainerStyle>
</ListView>
<StackPanel Grid.Column="1" >
<Button x:Name="ViewDetails" Height="75" HorizontalAlignment="Stretch" VerticalAlignment="Top" Background="#95A810" Margin="20,0,20,0" BorderBrush="{x:Null}" Click="ViewDetails\_Click" >Add / Edit</Button>
<Button x:Name="Delete" Height="75" HorizontalAlignment="Stretch" VerticalAlignment="Top" Background="#95A810" Margin="20,0,20,0" BorderBrush="{x:Null}" Click="Delete\_Click" >Delete</Button>
</StackPanel>
<Border x:Name="EditAdd" Visibility="Collapsed" HorizontalAlignment="Stretch" VerticalAlignment="Center" Grid.Column="2" Background="#004FC6" Padding="20">
<Grid>
<Grid.Resources>
<Style BasedOn="{StaticResource TitleTextStyle}" TargetType="TextBlock" />
</Grid.Resources>
<Grid.RowDefinitions>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
<RowDefinition Height="auto"/>
</Grid.RowDefinitions>
<TextBlock Text="Title" Style="{StaticResource TitleTextStyle}"/>
<TextBox Margin="0,10,0,0" Grid.Row="1" Text="{Binding SelectedItem.Title, ElementName=itemListView,Mode=TwoWay}"/>
<TextBlock Margin="0,10,0,0" Text="Category" Grid.Row="2" Style="{StaticResource TitleTextStyle}"/>
<ComboBox Margin="0,10,0,0" Grid.Row="3" Grid.ColumnSpan="2" x:Name="categoriesListBox" SelectedItem="{Binding Path=SelectedItem.CategoryName, ElementName=itemListView,Mode=TwoWay}"/>
<TextBlock Text="Comment" Grid.Row="4" Style="{StaticResource TitleTextStyle}"/>
<TextBox Margin="0,10,0,0" Grid.Row="5" Text="{Binding SelectedItem.Comment, ElementName=itemListView,Mode=TwoWay}" Height="100" TextWrapping="Wrap"/>
<StackPanel Grid.Row="6" Orientation="Horizontal">
<TextBlock Text="Importance Score" Margin="0,0,10,0"/>
<Slider Minimum="0" Maximum="10" Value="{Binding SelectedItem.ImportanceScore, ElementName=itemListView,Mode=TwoWay}" Width="200"/>
</StackPanel>
<StackPanel Grid.Row="7" Orientation="Horizontal">
<TextBlock Text="Urgency Score" Margin="0,0,10,0"/>
<Slider Minimum="0" Maximum="10" Value="{Binding SelectedItem.UrgencyScore, ElementName=itemListView,Mode=TwoWay}" Width="200"/>
</StackPanel>
<StackPanel Grid.Row="8" Orientation="Horizontal">
<TextBlock Text="Worth Score" Margin="0,0,10,0"/>
<Slider Minimum="0" Maximum="10" Value="{Binding SelectedItem.WorthScore, ElementName=itemListView,Mode=TwoWay}" Width="200"/>
</StackPanel>
<StackPanel Grid.Row="9" Orientation="Horizontal">
<TextBlock Text="Time spent" Margin="0,0,10,0"/>
<TextBox Width="100" Text="{Binding SelectedItem.TimeSpent, ElementName=itemListView,Mode=TwoWay}"/>
<TextBlock Text="Time estimate" Margin="20,0,10,0"/>
<TextBox Width="100" Text="{Binding SelectedItem.TimeEstimate, ElementName=itemListView,Mode=TwoWay}"/>
</StackPanel>
<Grid Grid.Row="11" Margin="0,10,0,0">
<Grid.ColumnDefinitions>
<ColumnDefinition/>
<ColumnDefinition/>
<ColumnDefinition/>
</Grid.ColumnDefinitions>
<Button Content="Add" HorizontalAlignment="Stretch" Background="#95A810" Click="Add\_Click"/>
<Button Grid.Column="1" Content="Update" HorizontalAlignment="Stretch" Background="#95A810" Click="Update\_Click"/>
<Button Grid.Column="2" Content="Cancel" HorizontalAlignment="Stretch" Background="#95A810" Click="Cancel\_Click"/>
</Grid>
</Grid>
</Border>
<FlipView Grid.Column="2" x:Name="ChartView">
<FlipViewItem>
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="0.8\*"/>
<RowDefinition Height="0.2\*"/>
</Grid.RowDefinitions>
<Chart:RadCartesianChart HorizontalAlignment="Stretch" VerticalAlignment="Stretch">
<Chart:RadCartesianChart.Grid>
<Chart:CartesianChartGrid MajorLinesVisibility="Y"/>
</Chart:RadCartesianChart.Grid>
<Chart:RadCartesianChart.VerticalAxis>
<Chart:LinearAxis Minimum="1" LabelStyle="{StaticResource CaptionTextStyle}"/>
</Chart:RadCartesianChart.VerticalAxis>
<Chart:RadCartesianChart.HorizontalAxis>
<Chart:CategoricalAxis LabelStyle="{StaticResource CaptionTextStyle}"/>
</Chart:RadCartesianChart.HorizontalAxis>
<Chart:RadCartesianChart.Series>
<Chart:BarSeries ItemsSource="{Binding AllEntries}">
<Chart:BarSeries.PointTemplate>
<DataTemplate>
<Rectangle Fill="#B0127F" />
</DataTemplate>
</Chart:BarSeries.PointTemplate>
<Chart:BarSeries.ValueBinding>
<Chart:PropertyNameDataPointBinding PropertyName="TimeSpent"/>
</Chart:BarSeries.ValueBinding>
<Chart:BarSeries.CategoryBinding>
<Chart:PropertyNameDataPointBinding PropertyName="Title"/>
</Chart:BarSeries.CategoryBinding>
</Chart:BarSeries>
</Chart:RadCartesianChart.Series>
</Chart:RadCartesianChart>
<Border VerticalAlignment="Center" Grid.Row="1">
<TextBlock Text="Time spent per activity" Style="{StaticResource SubheaderTextStyle}" Padding="15,20"/>
</Border>
</Grid>
</FlipViewItem>
<FlipViewItem>
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="0.8\*"/>
<RowDefinition Height="0.2\*"/>
</Grid.RowDefinitions>
<Border Background="Orange" VerticalAlignment="Bottom" Grid.Row="1">
<TextBlock Text="Second flipitem" Style="{StaticResource BodyTextStyle}" Padding="15,20"/>
</Border>
</Grid>
</FlipViewItem>
</FlipView>
</Grid>
</Grid>
<Page.BottomAppBar>
<AppBar x:Name="bottomAppBar" Padding="10,0,10,0"/>
</Page.BottomAppBar>
‘Repository’ (Data access layer)
[sourcecode language=“csharp”]
using System.Collections.Generic;
using System.IO;
using Mihsp.Model;
using System.Collections.ObjectModel;
using System.Linq;
using SQLite;
namespace Mihsp.Repository
{
public class StudyActivityRepository
{
private static readonly string _dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, “db.sqlite”);
public void Add(StudyActivity studyActivity)
{
using(var connection = new SQLiteConnection(\_dbPath))
{
connection.Insert(studyActivity);
}
}
public void Remove(StudyActivity studyActivity)
{
using (var connection = new SQLiteConnection(\_dbPath))
{
connection.Delete(studyActivity);
}
}
public void Update(StudyActivity studyActivity)
{
using (var connection = new SQLiteConnection(\_dbPath))
{
connection.Update(studyActivity);
}
}
public ObservableCollection<StudyActivity> GetAll()
{
var list = new ObservableCollection<StudyActivity>();
using (var connection = new SQLiteConnection(\_dbPath))
{
list = new ObservableCollection<StudyActivity>(connection.Query<StudyActivity>("select \* from StudyActivity"));
}
return list;
}
public IEnumerable<IGrouping<string, StudyActivity>> GetAllGrouped()
{
return GetAll().OrderBy(x => x.Title).GroupBy(x => x.CategoryName);
}
}
}
[/sourcecode]
Codebehind
[sourcecode language=“csharp”]
using System.IO;
using Mihsp.Model;
using Mihsp.Repository;
using System.Collections.ObjectModel;
using SQLite;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using System.Linq;
namespace Mihsp
{
public sealed partial class MainPage : Page
{
private StudyActivityRepository \_studyActivityRepository { get; set; }
public MainPage()
{
this.InitializeComponent();
\_studyActivityRepository = new StudyActivityRepository();
SetCollectionViewSource();
SetStatsDataEntries();
//CreateTable();
}
public void CreateTable()
{
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
using (var db = new SQLite.SQLiteConnection(dbPath))
{
db.CreateTable<StudyActivity>();
db.RunInTransaction(() =>
{
db.Insert(new StudyActivity()
{
Title = "Bacon mancon",
CategoryName = "Bacon",
Accomplished = false,
Comment = "Cool",
ImportanceScore = 2,
WorthScore = 2,
UrgencyScore=3,
TimeSpent= 0,
TimeEstimate=60
});
});
}
}
void SetCategories()
{
var categories = \_studyActivityRepository.GetAll();
categoriesListBox.ItemsSource = !categories.Any() ? new [] { "Category1", "Category2"} : categories .GroupBy(x => x.CategoryName).Select(g => g.First().CategoryName);
}
void SetCollectionViewSource()
{
activitiesCollection.Source = \_studyActivityRepository.GetAllGrouped();
}
StudyActivity GetCurrentItem()
{
return activitiesCollection.View.CurrentItem == null ? new StudyActivity() : activitiesCollection.View.CurrentItem as StudyActivity;
}
private void Add\_Click(object sender, RoutedEventArgs e)
{
\_studyActivityRepository.Add(GetCurrentItem());
SetCollectionViewSource();
}
private void Cancel\_Click(object sender, RoutedEventArgs e)
{
SetStatsDataEntries();
EditAdd.Visibility = Visibility.Collapsed;
ChartView.Visibility = Visibility.Visible;
}
private void Delete\_Click(object sender, RoutedEventArgs e)
{
\_studyActivityRepository.Remove(GetCurrentItem());
SetCollectionViewSource();
}
private void Update\_Click(object sender, RoutedEventArgs e)
{
\_studyActivityRepository.Update(GetCurrentItem());
SetCollectionViewSource();
}
private void ViewDetails\_Click(object sender, RoutedEventArgs e)
{
SetCategories();
EditAdd.Visibility = Visibility.Visible;
ChartView.Visibility = Visibility.Collapsed;
}
private ObservableCollection<StudyActivity> \_allEntries = new ObservableCollection<StudyActivity>();
public ObservableCollection<StudyActivity> AllEntries
{
get { return \_allEntries; }
}
private void SetStatsDataEntries()
{
\_allEntries = \_studyActivityRepository.GetAll();
}
}
}
[/sourcecode]
Comments
Can you store it as a long? Then use DateTime constructor option to convert it back again when you want to use it?
Sadly, I only get: "no such table: StudyActivity".. Any solution?
Iris, we just launched Ninja WinRT Database; an ACID compliant object database for Windows Runtime. http://www.kellermansoftware.com/p-49-ninja-winrt-database.aspx Full disclosure, I am the owner of Kellerman Software.
Greg Finzer This may help, here is Linq to SQLite. https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx
After deployment of First package to win store, if we want to update the packge, will it impact the sqlite database? As the path "Windows.Storage.ApplicationData.Current.LocalFolder.Path" will be different for First pacakge and later one. please correct me if i m wrong as am new to winRT development
Last modified on 2012-10-02