Learning .NET MAUI – Part 15 – SQLite

As an experienced XF programmer, you know that there are times you need a relational database, and SQLite has been the mobile db of choice for a very long time. In this post we’ll create a table in SQLite and if it has anything in it, we’ll display the contents of the table. If it is empty, we’ll go out to the service, get our data, and stash it in the table.

For the purposes of this demo, I’m only going to create one table, after that it is just SQL.

Picking up where we left off, the first thing you’ll need are a couple NuGet packages.

Getting from and writing to the database

The bulk of the changes happen in ZipCodeService

Add these two lines to the top of your class

SQLiteAsyncConnection conn;
private string fileName = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)+ @"/zip.db";

You can of course name your db whatever you want, and put it in any sandbox location.

Modify the constructor to initialize the SQLite connection

public ZipCodeService(IConnectivity connectivity)
{
    _httpClient = new HttpClient();
    this.connectivity = connectivity;
    conn = new SQLiteAsyncConnection(fileName);

}

Before we proceed we need to teach our Result and Root model classes to be database classes. We do that with a couple simple attributes, and while we’re at it, let’s add a unique id.

 public class Result
 {
     [PrimaryKey, AutoIncrement, Column("_id")]
     public int Id { get; set; }
     public string zip { get; set; }
     public string city { get; set; }
     public string state { get; set; }
 }

 [Table ("Result")]
 public class Root
 {
     public List<Result> results { get; set; }
 }

You are now ready to talk to the database. We want to make a connection to a table (we’ll call it Result) and if it is empty, we’ll go ahead as we have been doing, but this time once we have the results we’ll populate the table for next time we need the data.

First, let’s connect to the table and see how many results we get back

await conn.CreateTableAsync<Result>();
List <Result> resultsFromSQL = await conn.Table<Result>().ToListAsync();
if (resultsFromSQL.Count == 0)
{

If the table is empty then we continue on as we have in the past, but once we have data we populate the table

if (response.IsSuccessStatusCode)
{
    _root = await response.Content.ReadFromJsonAsync<Root>();
    _resultList = _root.results;
    var rowsAdded = await conn.InsertAllAsync(_resultList);
}

Note that InsertAllAsync returns the number of rows added. In this example, I just throw that number on the ground.

We set the return value to the resultsFromSQL (which is just a List<Result>)

 public async Task<List<Result>> GetResults()
 {
     await conn.CreateTableAsync<Result>();
     List <Result> resultsFromSQL = await conn.Table<Result>().ToListAsync();
     if (resultsFromSQL.Count == 0)
     {

         if (_resultList?.Count > 0)
             return _resultList;

         if (connectivity.NetworkAccess != NetworkAccess.Internet)
         {
             await Shell.Current.DisplayAlert("No Internet", "Please check your internet connection", "OK");
             return null;
         }

         var url = "https://www.zipwise.com/webservices/citysearch.php?key=dksr5ewwvyy7tnjk&format=json&string=Acton";
         var response = await _httpClient.GetAsync(url);

         if (response.IsSuccessStatusCode)
         {
             _root = await response.Content.ReadFromJsonAsync<Root>();
             _resultList = _root.results;
             var rowsAdded = await conn.InsertAllAsync(_resultList);
         }
     }
     else _resultList = resultsFromSQL;
     return _resultList;
 }

Next time through (if, e.g., you hit clear and then get the Zipcodes again) you’ll draw them from the database, thus improving performance tremendously.

Note: in many apps you’ll want your database contents to “expire” after a set amount of time, but here we don’t really have to worry about that as it is rare for a new city to be named Acton or for zipcodes to change. That said, I’d probably have the table expire every 30 days or so.

That’s it. Nothing else has to change. In fact, the only real way to see that this is working is to step through GetResults().

Source code for this blog post is here.