Insert and Replace existing value in sqlite by overriding the row

Question

I have a remote web service that returns me two int values namely:

1. Frequency
2. Conserve

I have a model defined as Parameters that is as follows :

public class Parameters
{
    public int Frequency { get; set; }
    public int Conserve { get; set; }
}

On each start of the application, a call is made to the web service to get the values. At the first launch of the app, the values are saved in to an sqlite database.

Subsequently, after each start of the application, a call is made to the web service to get the values. Each and every time the values are saved to the sqlite db.

The method that does is:

public async Task InsertParams()
{
        await con.CreateTableAsync<Parameters>();

        ParamService ps = new ParamService();

        Parameters pm = new Parameters();

        pm = await ps.GetParams(); // call to webservice

        await con.InsertOrReplaceAsync(pm);
}

The issue is that if initially the value of frequency is 30 on the web service, it inserts it in the db. But when the value changes to 50, it does not override the row in the sqlite db.

I want only a single row in the sqlite db that should be overriden /replace. Please advise how can I achieve this. Thanks.

Answer 1

InsertOrReplace method requires a unique identifier ID.
Otherwise how do you expect it to check if a row exists and update it if needed?

So you have to options:
1. Add an Id property and keep it the same all the time.

public class Parameters
{
    [PrimaryKey]
    public int Id { get; set; }
    public int Frequency { get; set; }
    public int Conserve { get; set; }
}

...
// TODO: Hacky solution
pm = await ps.GetParams(); // call to webservice
pm.Id = 1;
await con.InsertOrReplaceAsync(pm);
...

2. Instead of InsertOrReplace truncate the table and insert a single row.

P.S.: Keeping a single row in a SQL table does not feel natural, you may consider some other caching solutions in order to valid use of hacks described above.