SQLite and UWP

in ,

There are times when being connected all the time is just not possible. As a person that travels a fair amount every year I have a pre-travel checklist, it contains the usual: pack tooth brush, toothpaste etc. etc. Also on the list is: download all data. This normally entails maps, Channel 9 videos and any games I play. Unfortunately, there are many times where I would to love to use apps but their lack of offline experience makes it a challenge. This blog post is all about how developers can use SQLite in their UWP applications to make sure users get the optimal experiences when offline, experiencing poor connectivity or when a cloud implementation isn't ideal for storing data.

The first thing you need to do is download the SQLite Visual Studio extension from the SQLite website: http://www.sqlite.org/download.html

There you will be able see a download link for Universal App platform, so go ahead and download and install that VSIX file.

Now you have the Visual Studio extension installed it's time to open up Visual Studio and create an app. Start by creating a blank Universal Windows Platform application and then add let's set up all the dependencies to get your application ready.

Firstly add a reference to the SQLite UWP extension, to do this right click on References within the project and select Add Reference. Under the Universal Windows section you will Extension option, there you will then able to select SQLite for Universal App Platform.

Now you have the extension it's time to add a Nuget Package. The Nuget package you need to install is SQLite.Net-PCL

You are now ready to start writing code :) What are going to do is write a simple application that takes names of users and store them into a database. When we restart the application we are able to then read from the database the names of the user and display them.

I need to create a User table, to do this I create a class called User with a string property called Name:

public class User
{
    public string Name { get; set; }
}

In my MainPage.xaml the UI is pretty simple:

<StackPanel Grid.Column="0">
  <Button Content="Remove Table" Click="{x:Bind RemoveTableButton_Click}" />

  <StackPanel>
    <TextBox x:Name="NameTextBox" Header="Name" />
    <Button Content="Create User" Click="{x:Bind CreateUserButton_Click}" />
  </StackPanel>
</StackPanel>

<ListView ItemsSource="{x:Bind Users, Mode=OneWay}" Grid.Column="1">
  <ListView.ItemTemplate>
    <DataTemplate>
      <StackPanel>
        <TextBlock Text="{Binding Name}" />
      </StackPanel>
    </DataTemplate>
  </ListView.ItemTemplate>
</ListView>

I have textbox to enter the name, two buttons, one to create a user, one to remove the table - in case I want to reset the demo.  To display the contents of the table I just have a ListView which is data bound to a ObservableCollection of Users.

This is what the code in the MainPage.xaml.cs looks like. I have to define a file where my SQL database is going to live:

this.path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");

When the user enters the name and presses the Create User button it calls this event handler:

private void CreateUserButton_Click(object sender, RoutedEventArgs e)
{
    using (var connection = new SQLiteConnection(new Net.Platform.WinRT.SQLitePlatformWinRT(), path))
    {
        if (!TableExists(connection))
        {
            connection.CreateTable(Net.Interop.CreateFlags.AutoIncPK);
        }

        connection.InsertOrReplace(new User() { Name = this.NameTextBox.Text }, typeof(User));
        this.Users = new ObservableCollection(connection.Table().Select(i => i));
    }
}

I'll briefly walk through this code. I want connect to the database so I instantiate a SQLiteConnection and tell it that I'm targeting the Windows Runtime platform and provide the path to the database - if it doesn't exist then it will create one. The next things I do is check to see if the table exists in the database. If not then I call CreateTable using the User type to define the schema. I have a little helper method to check if the table exists.

private static bool TableExists<T>(SQLiteConnection connection)
{
    var cmd = connection.CreateCommand($"SELECT name FROM sqlite_master WHERE type='table' AND name='{typeof(T).Name}'");
    return cmd.ExecuteScalar() != null;
}

Once I know I have a table I then insert the data from the text box by wrapping it in a User object. Finally I update the collection that the ListView is bound to by performing simple Linq statement to pull the User objects out from the database.

I use the same code to pull data from the database on the OnNavigateTo code to ensure that the ListView is populated when the user starts the application.

protected override void OnNavigatedTo(NavigationEventArgs e)
{
    using (var connection = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path))
    {
        if (TableExists(connection))
        {
            this.Users = new ObservableCollection(connection.Table().Select(i => i));
        }
    }
}

When the Remove Table button is clicked I simply call DropTable.

private void RemoveTableButton_Click(object sender, RoutedEventArgs e)
{
    using (var connection = new SQLiteConnection(new Net.Platform.WinRT.SQLitePlatformWinRT(), path))
    {
        connection.DropTable();
        this.Users.Clear();
    }
}

As you can see using SQLite is really simple and yet it provides so much richness to your application.

Happy coding!

GitHub source: https://github.com/shenchauhan/blog/tree/master/SQLite