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. 

Sqlite reference

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.

AnyCPU

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,

InitialDB

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 .

NewValueInDB

Share

About Jesse Liberty

Jesse Liberty is a Master Consultant for Falafel Software, and has three decades of experience writing and delivering software projects. He is the author of 2 dozen books and multiple Pluralsight courses, and has been a Technical Evangelist for Telerik and for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS.
This entry was posted in Mini-Tutorial, Windows 8 and tagged . Bookmark the permalink.

8 Responses to Windows 8 Storing Data With SQLite

  1. medium.com says:

    But in case you prefer richer taste of drinks, then vacuum type machine or
    manual type machine can be quite a better choice.

    If water coming out of one’s faucet tastes terrible to you personally, so will your coffee.

    We as buyers even offers responsibility in taking good care in the
    things we buy as we want them to last a long time.

  2. JP says:

    In the Load()- Method of the Repository- class you define a variable ‘list’, which is not used.

  3. malike says:

    Not so far I have found new cool tool to work with SQLite on win – Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview

  4. steve says:

    Jesse, thanks for this resource. (and for your great podcast)

    How much of this is applicable to a Windows 8 -Phone- App, I’m wondering ?

    Specifically, are there any Sqlite api calls you can’t do on WP8 ?

  5. Pingback: The Daily Six Pack: April 10, 2013 | Dirk Strauss

  6. Pingback: Windows Store Developer Links – 2013-04-09 | Dan Rigby

  7. Priyanka says:

    Hi Jesse,
    Could you make a link available to download the above source?

  8. Hi Jesse,
    Have you ever heard of Siaqodb? (http://siaqodb.com/)

    They have support for Windows 8 (with async support), Windows Phone 7 and 8, Silverlight, .NET, .NET Compact framework, Mono, MonoTouch, Mono for Android and Unity3d.

    One nice thing about them – and quite unique – is having a Sync Framework provider that is supported in several of those platforms.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>