Local Databases in Xamarin Forms with SQLite

SQLite Databases

Local databases in a Xamarin Forms App

I don't need to argue that databases are of great importance in almost any kind of application, so in this post, I will cover the usage of local SQLite databases inside a Xamarin Forms app.

Specifically, we will be creating a simple notes app, with a couple of views: one where the user will see the list of notes and another one for creating a note. While I will briefly explain the code that I use to define the interface, I will focus on the SQLite functionality itself, not really talking about the event handlers and the definition of elements inside the XAML file. If you are not familiar with XAML maybe check one of my previous posts first.

I do want to specify that I created this project using a .NET Standard library as a code sharing strategy and that I will cover Android, iOS and UWP implementation. If you are using Visual Studio for Mac, UWP won't be an option, and maybe your code sharing strategy will be PCL instead of .NET Standard, but the implementation is identical, except for the folder where you need to reference the SQLite packages.

FYI; you can find all the code for this example in this repository.

Adding the SQLite NuGet Package

This is the package that already contains all the functionality needed regarding connections and CRUD requests to an SQLite database, you will then have to add it to all your projects. In Visual Studio 2017 all you need to do is right click on the solution and select "Manage NuGet packages for solution", in the case of Visual Studio for Mac you will have to go to each project, right click on the References folder and select "add reference".

The "sqlite-net-pcl" package is the one to add, don't be confused by the fact that it has PCL in its name, it will work perfectly with a .NET Standard library.

The database path

Each platform will require a different path where to store the database file, mainly because each platform has a different directory structure. So this is the only code that can't be shared since each platform must specify this. To be able to get that platform-specific folder path, I want you to notice that inside the MainActivity.cs (Android project), the AppDelegate.cs (iOS project), and the MainPage.xaml.cs (UWP project) there is a call to the XamarinNotesApp.App's constructor. This class is in the shared project, and the constructor is the one that establishes the first view to be displayed, this is how each platform initializes itself with the code inside the shared project (whether that is a PCL, .NET Standard or Shared project).

The reason I explain this is because it will be precisely the App class' constructor that we will use to receive a string from those projects. Since each project is already calling this method, we may as well use this code for them to pass the platform-specific path the project will work with.

Hence I will create an overload of the App's constructor that will have the same functionality, but will also add some functionality of its own.

public static string DatabasePath = string.Empty;
public App(string databasePath)
{
    InitializeComponent();

    MainPage = new XamarinNotesApp.MainPage();

    DatabasePath = databasePath;
}

Notice that the value now being received through the string argument will be assigned to a new static string variable. This DatabasePath variable will now be accessible throughout the entire shared project, so from any Page I can use it. Now that we have this overload for the constructor, it is time to use it from every project.

The Android path

As I mentioned before, each platform will set its own directory path for the database, so before calling the App's constructor, I will create a new string that will hold that path, and the name of the file. In the case of Android then, in the MainActivity.cs file, I will add this code right before the LoadApplication method call.

string dbName = "notes.db3";
string folderPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
//! added using System.IO;
string dbPath = Path.Combine(folderPath, dbName);
LoadApplication(new App(dbPath));

In the case of Android, the database has to be stored in the personal folder. Notice that I use the Path's Combine method to combine this two strings together (the name of the file and the path of the directory where it will be stored). This method makes sure that these strings are correctly combined into a string that is formatted as a path.

Eventually, I am passing the dbPath to the second overload of the App's constuctor.

The iOS Path

Very similar to what we do in the case of Android, except that the directory where the database file is stored needs to be different. In this case I also access the personal folder to quickly get that path, but that is not the path where the database is to be stored. Instead I navigate to the parent directory, and then over to another directory: the Library folder. Basically this means that the Library folder is inside the Personal folder, only I cannot get the path to the Library folder as straight forward as I can with the Personal folder, so I need to combine a couple of strings to access it. This code is inside the AppDelegate.cs file.

string dbName = "notes.db3";
//! added using System.IO;
string folderPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "..", "Library");
string dbPath = Path.Combine(folderPath, dbName);
LoadApplication(new App(dbPath));

The Windows Path

 Finally, in the case of UWP applications, the folder will have to be the current LocalFolder for the application, which can be accessed through the ApplicationData's Current property. To use that class, a using directive to the Windows.Storage namespace is required:

string dbName = "notes.db3";
//! added using Windows.Storage;
string folderPath = ApplicationData.Current.LocalFolder.Path;
//! added using System.IO;
string dbPath = Path.Combine(folderPath, dbName);
LoadApplication(new XamarinNotesApp.App(dbPath));

The Pages

The MainPage to list the notes

Now its time to implement the functionality, let me first tell you about the way the app is structured. I will be using the default MainPage as the page where I will list the notes. So here I have created a ListView that already has an event handler for when an item from the list is created.

<Grid>
    <ListView x:Name="notesListView"
              ItemSelected="NotesListView_ItemSelected">
        <ListView.ItemTemplate>
            <DataTemplate>
                <TextCell/>
            </DataTemplate>
        </ListView.ItemTemplate>
    </ListView>
</Grid>

private void NotesListView_ItemSelected(object sender, EventArgs e) { }

I side this page there is also a ToolbarItem, that will eventually navigate the user to another page where they will be able to write the note. This item already has an event handler for when it is clicked.

<ContentPage.ToolbarItems>
    <ToolbarItem Text="New"
                 Clicked="NewToolbarItem_Clicked"/>
</ContentPage.ToolbarItems>

private void NewToolbarItem_Clicked(object sender, EventArgs e) { }

The NewNotePage

It is important to create this new Page as a Content Page with the template indicated here. The other Content Page (C#) template won't create a XAML file and you would have to create the UI by writing C#, having to run the app over and over to be able to take a look at the result.

This new page will also have a toolbar item so the user can save the note. Finally, it will contain both an entry for the note's title and an Editor for the contents of this note.

Again, the toolbaritem will have an event handler for when it is pressed.

Here is the code defining the content for the ContentPage:

<StackLayout>
    <Entry x:Name="titleEntry"/>
    <Editor x:Name="contentEditor"
            HeightRequest="500"/>
</StackLayout>

And here the XAML for the ToolbarItem and the C# for its click event handler:

<ContentPage.ToolbarItems>
    <ToolbarItem Text="Save"
                 Clicked="SaveToolbarItem_Clicked"/>
</ContentPage.ToolbarItems>

private void SaveToolbarItem_Clicked(object sender, EventArgs e){ }

Navigation

For the navigation from the MainPage to the NewNotePage (and vice versa) to work, the MainPage property for the App class has to be a NavigationPage. So in the two overloads for the App's constructor, I will change the definition of the MainPage to be this:

MainPage = new NavigationPage(new XamarinNotesApp.MainPage());

Notice that I am still creating a new XamarinNotesApp.MainPage, but it is now inside the constructor for the NavigationPage. This will create a new NavigationPage with a MainPage as its root page, and assign the NavigationPage to the MainPage property. Without this navigation as we will need it won't be possible.

The navigation that has to happen to the NewNotePage then has to be coded inside the event handler for the click of the new button that is inside the MainPage. That method will now look like this:

private void NewToolbarItem_Clicked(object sender, EventArgs e)
{
    Navigation.PushAsync(new NewNotePage());
}

Inserting and reading

The Model

Now that the event handlers are in place and that navigation is happening between the pages, we will need to insert and read. For that, we will have to define the type of elements that will be inserted, which will also define the name of the Table to be created, and the columns it will have. All this can be established by creating a class. The name of the class will be the name of the table, the properties of the class its columns, and the instances the entries.

public class Note
{
    //! added using SQLite;
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
}

Notice that I also added a couple of SQLite attributes to mark the Id property as the table's primary key, and as a value that has to be autoincremented starting in 1. There are other attributes that you could set, they are pretty much self-explanatory, but there are:

• Indexed: To mark a sort of foreign key
• MaxLength.
• Unique
• NotNull
• Table, Column: To change the default name that the table or column will use (which comes from the name of the class or property)
• Ignore

Inserting

Now, this is enough to start implementing the CRUD operations. Let's start with the insert function, inside the clicked event handler for the save toolbaritem that we have in the NewNotePage. Through the App class, we will have static access to the DatabasePath to be able to create the connection.

Of course, the first step is to create the note that will be inserted, with the information already inside the entry and the editor. Also notice that I will be always calling the CreateTable method (which is generic, hence that syntax). This is ok because it will be called in a "create if not exists" basis. Finally, note that the Insert method returns an integer with the number of entries that were added to the table, so I can evaluate if this was greater than one to display a message to the user.

private void SaveToolbarItem_Clicked(object sender, EventArgs e)
{
    Note note = new Note()
    {
        Title = titleEntry.Text,
        Content = contentEditor.Text
    };

    //! added using SQLite;
    using (SQLiteConnection conn = new SQLiteConnection(App.DatabasePath))
    {
        conn.CreateTable<Note>();
        int itemsInserted = conn.Insert(note);

        if (itemsInserted > 0)
            DisplayAlert("Done", "Note saved", "Ok");
        else
            DisplayAlert("Error", "Note not saved", "Ok");
    }
}

Reading

I want reading to happen every time I navigate to the MainPage, so that I can reload the data when navigation back from the NewNotePage in case more elements were added. We can accomplish this by reading from the OnAppearing overridden method, which precisely is executed every time the Page is about to appear on screen:

protected override void OnAppearing()
{
    base.OnAppearing();

    //! added using SQLite;
    using (SQLiteConnection conn = new SQLiteConnection(App.DatabasePath))
    {
        conn.CreateTable<Note>();
        List<Note> notes = conn.Table<Note>().ToList();
        notesListView.ItemsSource = notes;
    }
}

While reading is quite straight-forward, by just getting the Table as a query and transforming that query to a List with the ToList method, the ItemsSource is a bit more complex to explain.

Yes, it is simply assigning a value to a property from the ListView defined in XAML, but it is through Binding that we will really be able to display the elements. The ItemsSource by itself doesn't define how to display the elements, in this case, an object of type Note.

Enter the ItemTemplate (that has a DataTemplate) that I defined inside the ListView. By establishing this template for each of the cells that should be displayed, I can let the ListView establish the way in which it will display the elements, in this case, objects of type Note.

So far I've already set this template to have a TextCell, which will contain two labels by default. A Text and a Detail labels. To this labels is that I will write the Title and Content properties coming from the Note. Basically, the ListView will have as a source a list of Notes. Each TextCell will receive one of those Notes, and finally, each cell will set, from each note, the Title to be the value of the Text, and the Content to be the value of the Detail. This will be possible by changing the TextCell inside the DataTemplate to this:

<TextCell Text="{Binding Title}"
          Detail="{Binding Content}"/>

It will be the purpose of later posts to explain this Binding, for now, let's just test this app.

We can launch it on a Windows, Android or iOS device, it will work the same way on all of them. We will first need to navigate to the NewNotePage, save some notes from there, and then navigate back to the MainPage to have this listed. Because this is all saved on a file, we can close the app, and the next time we open it, the data will still be there.

---

This topic, along with many many others, is covered in greater depth in my 25-hour long "The Complete Xamarin Developer Course: iOS and Android" course, which you can practically steal from me by