WP8/C#/SQLite: get last inserted id?

Question

SQLite with WP8 is driving me nuts. :(

All I want to do is to retrieve the value of the last inserted id...

I've got:

class ShoppingItem
{
  [SQLite.PrimaryKey, SQLite.AutoIncrement]
  public int Id {get; set;}
  public string Name {get; set;}
  public string Shop {get; set;}
  public bool isActive {get; set;}
}

Well, neither the used SQLiteConnection object nor its Table<ShoppingItem> seem to contain an appropriate member that contains the last ID.

So I tried to do:

private int GetLastInsertedRowID()
{
  int Result = -1;
  using (var db = new SQLiteConnection(m_DatabasePath)) {
    Result = db.ExecuteScalar<int>("SELECT last_insert_rowid();");
  }
  return Result;
}

But this function always returns 0. :( But when I read all entries of ShoppingItem, their IDs have values != 0.

So my question is: How can I retreive the last inserted id?

PS: Changing the SQL query to SELECT last_insert_rowid() FROM ShoppingItem; gave the same result.

PPS: Solutions like Getting the Last Insert ID with SQLite.NET in C# do not compile, apparently there is an older version SQLite used, with a totally different API

Answer 1

Your SELECT last_insert_rowid() call does not work because you are running it in a different database connection.

Anyway, you should just read the ID from the inserted ShoppingItem object, like this:

var si = new ShoppingItem() {
  Name = anItem,
  Shop = aShop,
  isActive = aIsActive,
};
db.Insert(si);
return si.Id;