Deserialize JSON into SQLite Database

Question

My model is GasStation.

using Newtonsoft.Json;
using SQLite;
using System;
using System.Collections.Generic;
using System.Text;

namespace TDEv2.Models
{
    public class GasStation
    {
        [JsonProperty("costcentre")][PrimaryKey]
        public string CostCentre { get; set; }

        [JsonProperty("name")]
        public string Name { get; set; }

        [JsonProperty("id")]
        public string Id { get; set; }
    }
}

My GasStationQuery contains this:

namespace TDEv2.Models
{
    public class GasStationQuery
    {
        public GasStation[] GasStations { get; set; }
    }
}

My JSON Array looks like this:

    gasstations: [
    {
        "id": 01,
        "name": "GasStation1",
        "costcentre": 123
    },
    {
        "id": 02,
        "name": "GasStation2",
        "costcentre": 456
    }
    ]

Now I want to deserialize this into my SQLite database:

using SQLite;
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using TDEv2.Models;

namespace TDEv2.Data
{
    public class GasStationDatabase
    {

        readonly SQLiteAsyncConnection database;

        public GasStationDatabase(string dbPath)
        {
            database = new SQLiteAsyncConnection(dbPath);
            database.CreateTableAsync<GasStation>().Wait();
        }

        public Task<List<GasStation>> GetItemsAsync()
        {
            return database.Table<GasStation>().ToListAsync();
        }

        public Task<GasStation> GetItemAsync(string costCentre)
        {
            return database.Table<GasStation>().Where(i => i.CostCentre == costCentre).FirstOrDefaultAsync();
        }

        public Task<int> SaveItemAsync(GasStation gasStation)
        {
            if (gasStation.CostCentre != null)
            {
                return database.UpdateAsync(gasStation);
            }
            else
            {
                return database.InsertAsync(gasStation);
            }
        }

    }
}

Now I want to do an initial sync to fill my database to work with offline on the devices, but I don't know further steps since I am programming for not that long.

Here's my try to fill the database:

using System.Net;
using TDEv2.Data;
using TDEv2.Models;

namespace TDEv2.Services
{
    public class InitialAsyncGasStationDatabase
    {
        private GasStationDatabase db;
        public GasStationQuery InitialAsyncGasStationsToDatabase()
        {
            string json;
            using (WebClient client = new WebClient())
            {
                json = client.DownloadString($"http://xxx/gasstations.json");
            }
            foreach (GasStation gasStation in json)
            {
                db.SaveItemAsync(gasStation);
            }
            return;
        }
    }
}

The code doesn't work. I am getting an error in the foreach section with Cannot convert type "char" to "TDEv2.Models.GasStation"

Answer 1

Probably your source has a list of GasStations, so you can Deserialize your json object into a List of GasStation,

private GasStationDatabase db;
    public GasStationQuery InitialAsyncGasStationsToDatabase()
    {
        string json;
        using (WebClient client = new WebClient())
        {
            json = client.DownloadString($"http://xxx/gasstations.json");
        }

        var gasStationList = JsonConvert.DeserializeObject<List<GasStation>>(json);

        foreach (GasStation gasStation in gasStationList )
        {
            db.SaveItemAsync(gasStation);
        }
        return;
    }

Answer 2

you need to deserialize your json into an object before you can save it to the db

        using (WebClient client = new WebClient())
        {
            json = client.DownloadString($"http://xxx/gasstations.json");
        }

        // using newtonsoft json.net - use http://json2csharp.com/ to verfiy
        // that your C# model class actually matches your json
        var data = JsonConvert.DeserializeObject<GasStationQuery>(json);

        foreach (GasStation gasStation in data.GasStations)
        {
            db.SaveItemAsync(gasStation);
        }