Creating a connection to the SQLite database

We will now add all the code needed to communicate with the database. The first thing we need to do is define a connection field that will hold the connection to the database:

1. Open up the Repositories/TodoItemRepository file.
2. Add a using SQLite statement at the start of the file right below the existing using statements, as in the following code:

using DoToo.Models;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using SQLite;

3. Add the following field right below the class declaration:

private SQLiteAsyncConnection connection;

The connection needs to be initialized. Once it is initialized, it can be reused throughout the life span of the repository. Since the method is asynchronous, it cannot be called from the constructor without introducing a locking strategy. To keep things simple, we will simply call it from each of the methods that are defined by the interface:

1. Add the following code to the TodoItemRepository class.
2. Add a using System.IO statement at the start of the file so that we can use Path.Combine(...):

private async Task CreateConnection()
{
    if (connection != null)
    {
        return;
    }
    
    var documentPath = Environment.GetFolderPath(
                       Environment.SpecialFolder.MyDocuments);
    var databasePath = Path.Combine(documentPath, "TodoItems.db"); 
 
    connection = new SQLiteAsyncConnection(databasePath);
    await connection.CreateTableAsync<TodoItem>();

    if (await connection.Table<TodoItem>().CountAsync() == 0)
    {
        await connection.InsertAsync(new TodoItem() 
        { 
            Title = "Welcome to DoToo",
            Due = DateTime.Now 
        });
    }
} 

The method begins by checking whether we already have a connection. If we do, we can simply return. If we don't have a connection set up, we define a path on the disk to indicate where we want the database file to be located. In this case, we will choose the MyDocuments folder. Xamarin will find the closest match to this on each platform that we target.

We then create the connection and store the reference to that connection in the connection field. We need to make sure that SQLite has created a table that mirrors the schema of the TodoItem table. To make the development of the app easier, we add a default to-do list item if the TodoItem table is empty.