How to use InsertOrReplace in sqlite.net PCL?

Question

I am using sqlite.net version for PCL ( https://github.com/oysteinkrog/SQLite.Net-PCL ).

Here is my simple class.

public class LogEntry { [PrimaryKey, AutoIncrement] public int Key { get; set;} public DateTime Date { get; set; } } 

When a new LogEntry instance is created, the key is automatically set to 0. I set the date to something, and then call InsertOrReplace. The record is saved in my database. The Key field gets the auto-increment value, which should be 0, since it is the first record.

Then I create a new instance of LogEntry (the key is automatically initialized to 0) and sets the date to something else. Then I call InsertOrReplace. Since there is an existing entry with key 0, the entry will be updated.

What is the right way to handle this? I considered initializing the -1 key, but that didn't work either.

Does anyone have an example of this?

Answer 1

I am having the same problem as you describe. Try

 var rowsAffected = Connection.Update(object); if(rowsAffected == 0) { // The item does not exists in the database so lets insert it rowsAffected = Connection.Insert(object); } var success = rowsAffected > 0; return success; 

I just tried above and it works as expected

Answer 2

If you change the key to a type with a null value (int?), It should work. then SQLite sees the null value and generates a new identifier when necessary.

 public class LogEntry { [PrimaryKey, AutoIncrement] public int? Key { get; set;} public DateTime Date { get; set; } } 

Answer 3

How this works is a source of great confusion, but while Insert treats zero primary keys as a special case when AutoIncrement set, InsertOrReplace not.

So using

 [PrimaryKey, AutoIncrement] public int id { get; set; } 

if you InsertOrReplace series of null id records in a new table, the first will be stored in id: 0 , and each subsequent one will save it. If you just Insert each of them, then because of AutoIncrement first one will save in id: 1 and the next one in id: 2 , etc., As you might expect.

If you change the key type to nullable int, then records with null identifiers will be treated as InsertOrReplace inserts, in which case you do not need the AutoIncrement attribute at AutoIncrement , they will still remain in the sequence starting from 1.

 [PrimaryKey] public int? id { get; set; } 

If you cannot use this for any reason, you can do your own checking for null identifiers and for those who call Insert instead, for example.

 Func<Foo, int> myInsertOrReplace = x => { return x.id == 0 ? _db.Insert(x) : _db.InsertOrReplace(x); }; 

but in this case you should use the AutoIncrement attribute, otherwise the first zero insert will be stored at 0, and the second will throw a constraint exception when it tries to insert another such.

Answer 4

My solution for this is similar to Joacar, but instead of updating, I select an element, if it is null, I create a new element, otherwise I update the values ​​of these elements and then call InserOrReplace.

 var existingKey = await this.GetItem(key); Item item; if (existingKey.Value != null) { profile = new Item { Id = existingKey.Id, Key = existingKey.Key, Value = newValue, }; this.InsertOrReplaceAsync(item); } else { item = new Item { Key = key, Value = value, }; this.InsertAsync(item); } 

It may not be optimal, but it worked for me.