Populate SQLite database from generic "list of items"

Question

I use a SQLite database to populate a listview with a generic list of TodoItems:

ListView.ItemsSource = await App.Database.GetItemsAsync("SELECT * FROM [TodoItem]");

I can read data of the same kind from a database source using the HttpClient class as well via

public async Task<List<TodoItem>> ReadDataAsync ()

....
    Items = new List<TodoItem> ();
....
    var content = await response.Content.ReadAsStringAsync ();
    Items = JsonConvert.DeserializeObject <List<TodoItem>> (content);
....
    return Items;

whereafter I can use this as a data source as well:

ListView.ItemsSource = await App.ReadDataAsync();

Both ways work well.

What I want now is combine both routines to accomplish a code which checks whether we have an online connection, if yes, drop the database and populate it using the result of ReadDataAsync from above, if no, leave the database alone.

I found no way to directly assign a List to my database, overwriting the whole contents, I think of something like:

App.Database.Items = await App.ReadDataAsync();

but SQLite doesn't seem to expose the data store directly. How can I accomplish this?

Answer 1

For android for example, you could try: There's no need to drop the database.

           //Check for internet connection 
            var connectivityManager = 
             (ConnectivityManager)this.GetSystemService("connectivity");
            var activeConnection = connectivityManager.ActiveNetworkInfo;
            if ((activeConnection != null) && activeConnection.IsConnected)
            {
                //Make call to the API
                var list = await App.ReadDataAsync();
                //Update or Insert local records
                foreach (var item in list)
                {
                    var success = UpdateOrInsert(item);
                    //Do something after - like retry incase of failure
                }
            } 

The UpdatedOrInsert method can look like:

     private bool UpdateOrInsert(TodoItem item)
     {
        var rowsAffected = App.Database.Update(item);
        if (rowsAffected == 0)
        {
            // The item doesn't exist in the database, therefore insert it
            rowsAffected = App.Database.Insert(item);
        }
        var success = rowsAffected > 0;
        return success;
     }