Exercise 4: Access a SQLite database using asynchronous methods

In this exercise, you will update an existing project and modify the synchronous database calls to use their asynchronous counterparts from the SQLite.NET library.

To complete the exercise, you will need Visual Studio for Windows or macOS with the Xamarin development tools installed. You will also need either an emulator/simulator or a device to run the exercise on. Please see the setup page if you need help installing the Xamarin development environment.

Open the starter solution

This exercise is a continuation of the previous exercise. You can use your existing solution or begin from the prior Exercise 3 > Completed solution in your copy of the cloned or downloaded course materials.

Create an Async connection

1. Open the PersonRepository.cs file in the People PCL project.
2. Change the SQLiteConnection conn property to a SQLiteAsyncConnection. This requires updating the property and the instantiation.
3. In the constructor, notice the call to CreateTable is no longer valid. Replace this call with CreateTableAsync.
4. CreateTableAsync is a Task-returning async method. Use the Wait() method on the returned Task to make this a blocking call to ensure the table is created before the constructor completes. Note that we are using this blocking call to keep the exercise as simple as possible. In general you should not mix asynchronous and synchronous code as there are scenarios where it can cause an application to deadlock.

private SQLiteAsyncConnection conn;

public PersonRepository (string dbPath)
{
    conn = new SQLiteAsyncConnection(dbPath);
    conn.CreateTableAsync<Person>().Wait();
}

Use async database calls

1. Modify the AddNewPerson method to insert a new Person using an asynchronous call:
   • Refactor the method to use async/await.
   • Change the return type to be a Task so we can see exceptions that might happen in the method from the caller.
   • You will need a new namespace: System.Threading.Tasks to return a Task type.
   • Rename the method to append the term Async to the end of the method name.

using System.Threading.Tasks;
...
public async Task AddNewPersonAsync(string name)
{
    int result = 0;
    try
    {
        //basic validation to ensure a name was entered
        if (string.IsNullOrEmpty(name))
            throw new Exception("Valid name required");

        // TODO: insert a new person into the Person table
        result = await conn.InsertAsync(new Person { Name = name });

        StatusMessage = string.Format("{0} record(s) added [Name: {1})", result, name);
    }
    catch (Exception ex)
    {
        StatusMessage = string.Format("Failed to add {0}. Error: {1}", name, ex.Message);
    }
}

2. Modify the GetAllPeople method to return the results using an asynchronous call.
   • Refactor the method to use async/await and ToListAsync to perform the SELECT query.
   • Rename the method to append the term Async to the end of the method name.
   • The returning result from the method will need to be modified to return a Task<List<Person>>.

public async Task<List<Person>> GetAllPeopleAsync()
{
    try
    {
        return await conn.Table<Person>().ToListAsync();
    }
    catch (Exception ex)
    {
        StatusMessage = string.Format("Failed to retrieve data. {0}", ex.Message);
    }

    return new List<Person>();
}

3. In the MainPage.xaml.cs file, modify both of the Button.Click event handlers to use the async methods from the PersonRepository class, making use of the async and await keywords.

public async void OnNewButtonClicked(object sender, EventArgs args)
{
    statusMessage.Text = string.Empty;
    await App.PersonRepo.AddNewPersonAsync(newPerson.Text);
    statusMessage.Text = PersonRepository.Instance.StatusMessage;
}

public async void OnGetButtonClicked(object sender, EventArgs args)
{
    statusMessage.Text = string.Empty;
    var people = await App.PersonRepo.GetAllPeopleAsync();
    peopleList.ItemsSource = people;
}

4. Build the solution and run the app to verify it still builds and functions correctly.

Exercise summary

In this exercise, we refactored our existing project to utilize the asynchronous versions of the SQLite.NET methods so that our UI thread is not tied up when I/O is being performed.

If you have time, try adding some filters into the query, adding support to edit records and delete records through new buttons in the UI.

You can view the completed solution in the Exercise 4 > Completed folder of your copy of the cloned or downloaded course materials.