Windows 8 Storing Data With SQLite

In a previous blog post, I showed the repository pattern with local or remote data storage.  In this blog post, based in part on work done for my upcoming book Pro Windows 8 With C# and XAML by Jesse Liberty and Jon Galloway, we’re going to explore storing data using Sqlite.

To use SQLite you will need to add the SQLite Visual Studio Extension.   To do so, open VS and go to Tools->Extensions and Updates. Click on Online and in the search type SQLite for windows runtime.  When it comes up click download, it will install the extension.

Restart Visual Studio and create a new application using the Blank template.

When Visual Studio is ready, we’ll add the references. Right click on References and choose Add Reference.  Under Windows Extensions, click SQLite for Windows Runtime and click OK. 

Next we need to add the Nuget Package for SQLite .NET. Go to Tools->Library Package Manager->Manage NuGet packages. Search online for Sqlite-net.  Click install and install that package. 

Notice that you may have alerts on your references that “Any CPU is not supported…”  You must support a specific CPU.  Go to Build->Configuration Manager.  Choose the CPU you want to support. Rebuild.

Add a DataModel folder to your project.  Add a new Customer class to the DataModel folder.  This class is the same as in the previous example, except that we attribute the Id property with PrimaryKey, AutoIncrement,

public class Customer
{
   [PrimaryKey, AutoIncrement]
   public int Id { get; set; }

   public string Email { get; set; }
   public string FirstName { get; set; }
   public string LastName { get; set; }
   public string Title { get; set; }
}

Add the IDataRepository interface to the DataModel folder (this is identical to the previous example),

public interface IDataRepository
{
   Task Add( Customer customer );
   Task<ObservableCollection<Customer>> Load();
   Task Remove( Customer customer );
   Task Update( Customer customer );
}

Implement the IDataRepository interface with the concrete class SqliteRepository,

public class SqliteRepository : IDataRepository
{

Add two data members.  The first, _dbPath will point to a file in the LocalFolder, the second will be an ObservableCollection<Customer>,

 private static readonly string _dbPath =
     Path.Combine( 
     Windows.Storage.ApplicationData.Current.LocalFolder.Path, 
     "app.sqlite" );
 ObservableCollection<Customer> customers;

The constructor calls the initialize method,

public SqliteRepository()
{
   Initialize();
}

The initialize method checks to see if we have any records. If not, it seeds the database with two hard-coded records, otherwise it calls Load.

 public void Initialize()
 {
    using ( var db = new SQLite.SQLiteConnection( _dbPath ) )
    {
       db.CreateTable<Customer>();

       //Note: This is a simplistic initialization scenario
       if ( db.ExecuteScalar<int>( 
              "select count(1) from Customer" ) == 0 )
       {

          db.RunInTransaction( () =>
          {
             db.Insert( new Customer() { 
                FirstName = "Jon", LastName = "Galloway" } );
             db.Insert( new Customer() { 
                FirstName = "Jesse", LastName = "Liberty" } );
          } );
       }
       else
       {
          Load();
       }
    }
 }

The Load method creates a new observable collection, creates an async connection to our path and then calls QueryAsync<Customer> passing in the search string, thus populating the ObservableCollection<Customer> which it returns,

 public async Task<ObservableCollection<Customer>> Load()
 {
    var list = new ObservableCollection<Customer>();
    var connection = new SQLiteAsyncConnection( _dbPath );

    customers = new ObservableCollection<Customer>( 
       await connection.QueryAsync<Customer>( 
             "select * from Customer" ) );
    return customers;
 }

The Add method calls Customers.Add passing in a customer, gets the connection to the database, and returns the result of calling InsertAsync, passing in the new customer,

public Task Add( Customer customer )
{
   customers.Add( customer );

   var connection = new SQLiteAsyncConnection( _dbPath );
   return connection.InsertAsync( customer );
}

Remove is the inverse of Add, it removes a given customer record from the customers table.

 public Task Remove( Customer customer )
 {
    customers.Remove( customer );

    var connection = new SQLiteAsyncConnection( _dbPath );
    return connection.DeleteAsync( customer );
 }

Finally, Update works much as it did in the previous example,  we first get our old version of the customer (using the lambda expression) and we remove the old and replace it with the new version. 

public Task Update( Customer customer )
{
   var oldCustomer = customers.FirstOrDefault( 
      c => c.Id == customer.Id );

   if ( oldCustomer == null )
   {
      throw new System.ArgumentException( 
              "Customer not found." );
   }
   customers.Remove( oldCustomer );
   customers.Add( customer );

   var connection = new SQLiteAsyncConnection( _dbPath );
   return connection.UpdateAsync( customer );
}

That completes our repository, we can now turn to the view model, which we’ll also place in the DataModel folder for this example.  The ViewModel is unchanged from the previous demo

public class ViewModel : INotifyPropertyChanged
{
   IDataRepository _data;

   public ViewModel( IDataRepository data )
   {
      _data = data;
   }

   async public void Initialize()
   {
      Customers = await _data.Load();
   }

   private Customer selectedItem;
   public Customer SelectedItem
   {
      get { return this.selectedItem; }
      set
      {
         if ( value != selectedItem )
         {
            selectedItem = value;
            RaisePropertyChanged();
         }
      }
   }

   private ObservableCollection<Customer> customers;
   public ObservableCollection<Customer> Customers
   {
      get { return customers; }
      set
      {
         customers = value;
         RaisePropertyChanged();
      }
   }

   internal void AddCustomer( Customer cust )
   {
      _data.Add( cust );
      RaisePropertyChanged( "Customers" );
   }

   internal void DeleteCustomer( Customer cust )
   {
      _data.Remove( cust );
      RaisePropertyChanged( "Customers" );
   }

   public event PropertyChangedEventHandler PropertyChanged;
   private void RaisePropertyChanged(
       [CallerMemberName] string caller = "" )
   {
      if ( PropertyChanged != null )
      {
         PropertyChanged( 
            this, new PropertyChangedEventArgs( caller ) );
      }
   }
}

Additionally, our view is unchanged from the previous demo.  (Don’t forget to uncomment the styles!)

   <Page.Resources>
      <Style TargetType="TextBlock">
         <Setter Property="FontSize"
                 Value="20" />
         <Setter Property="Margin"
                 Value="5" />
         <Setter Property="HorizontalAlignment"
                 Value="Right" />
         <Setter Property="Grid.Column"
                 Value="0" />
         <Setter Property="Width"
                 Value="100" />
         <Setter Property="VerticalAlignment"
                 Value="Center" />
      </Style>
      <Style TargetType="TextBox">
         <Setter Property="Margin"
                 Value="5" />
         <Setter Property="HorizontalAlignment"
                 Value="Left" />
         <Setter Property="Grid.Column"
                 Value="1" />
      </Style>
   </Page.Resources>
   <Page.BottomAppBar>
      <AppBar x:Name="BottomAppBar1"
              Padding="10,0,10,0"
              AutomationProperties.Name="Bottom App Bar">
         <Grid>
            <Grid.ColumnDefinitions>
               <ColumnDefinition Width="50*" />
               <ColumnDefinition Width="50*" />
            </Grid.ColumnDefinitions>
            <StackPanel x:Name="LeftPanel"
                        Orientation="Horizontal"
                        Grid.Column="0"
                        HorizontalAlignment="Left">
               <Button x:Name="Save"
                       Style="{StaticResource 
                          SaveAppBarButtonStyle}"
                       Tag="Save"
                       Click="Save_Click" />
               <Button x:Name="Delete"
                       Style="{StaticResource 
                          DeleteAppBarButtonStyle}"
                       Tag="Delete"
                       Click="Delete_Click" />
            </StackPanel>
         </Grid>
      </AppBar>
   </Page.BottomAppBar>

   <StackPanel Margin="150">
      <StackPanel Orientation="Horizontal">
         <TextBlock Text="Email"
                    Margin="5" />
         <TextBox Width="200"
                  Height="40"
                  Name="Email"
                  Margin="5" />
      </StackPanel>
      <StackPanel Orientation="Horizontal">
         <TextBlock Text="First Name"
                    Margin="5" />
         <TextBox Width="200"
                  Height="40"
                  Name="FirstName"
                  Margin="5" />
      </StackPanel>
      <StackPanel Orientation="Horizontal">
         <TextBlock Text="Last Name"
                    Margin="5" />
         <TextBox Width="200"
                  Height="40"
                  Name="LastName"
                  Margin="5" />
      </StackPanel>
      <StackPanel Orientation="Horizontal">
         <TextBlock Text="Title"
                    Margin="5" />
         <TextBox Width="200"
                  Height="40"
                  Name="Title"
                  Margin="5" />
      </StackPanel>
      <ScrollViewer>
         <ListView Name="xCustomers"
                   ItemsSource="{Binding Customers}"
                   SelectedItem="{Binding SelectedItem, 
                      Mode=TwoWay}"
                   Height="400">
            <ListView.ItemTemplate>
               <DataTemplate>
                  <StackPanel>
                     <TextBlock Text="{Binding FirstName}" />
                     <TextBlock Text="{Binding LastName}" />
                     <TextBlock Text="{Binding Title}" />
                  </StackPanel>
               </DataTemplate>
            </ListView.ItemTemplate>
         </ListView>
      </ScrollViewer>
   </StackPanel>

In the code behind, the first thing we do is create two member varialbes. The first is an instance of IDataRepository that instantiates our SqliteRepository. The second is to declare a member variable of type ViewModel,

public sealed partial class MainPage : Page
{
   private IDataRepository data = new SqliteRepository();
   private ViewModel _vm;

In the constructor, as we did last time, we’ll create the view Model, initialize it and then set it as the DataContext,

public MainPage()
{
    this.InitializeComponent();
    _vm = new ViewModel( data );
    _vm.Initialize();
    DataContext = _vm;
}

The save and delete event handlers are identical to the previous example,

private void Save_Click( object sender, RoutedEventArgs e )
{
   Customer cust = new Customer
   {
      Email = Email.Text,
      FirstName = FirstName.Text,
      LastName = LastName.Text,
      Title = Title.Text
   };
   _vm.AddCustomer( cust );
}

private void Delete_Click( object sender, RoutedEventArgs e )
{
   if ( _vm.SelectedItem != null )
   {
      _vm.DeleteCustomer( _vm.SelectedItem );
   }
}

That’s it, we can now build and run the application.  When it first comes up you’ll see that the database was seeded with two values,

We can easily add a new value, then swipe up from the bottom to bring up the save button. As soon as the button is clicked, the new value is saved and shown in our list box .