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

Question

SQLite with WP8 is driving me crazy .:(

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

I have:

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 SQLiteConnection object used nor its Table<ShoppingItem> seems to contain the corresponding member that contains the last identifier.

So, I tried:

 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 the ShoppingItem records, their identifiers have values! = 0.

So my question is: how can I recover 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 identifier with SQLite.NET in C # do not compile, apparently an earlier version of SQLite is used with a completely different API

Answer 1

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

In any case, you should just read the identifier from the inserted ShoppingItem object, for example:

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

Answer 2

This is what I do to get the id of the last inserted element. The provided code snippet works in my Windows 8 application using SQLite 3.7.XX ( SQLite ).

  public class ExpenseDataMapper { SQLiteConnection connection; /// <summary> /// Constructor /// </summary> public ExpenseDataMapper() { connection = new SQLiteConnection(StaticResources.DATABASE_PATH_NAME); connection.CreateTable<FinancialListBoxExpenseItem>(); } /// <summary> /// Method #1: Inserts an FinancialListBoxExpenseItem into Database /// </summary> /// <param name="item"></param> /// <returns>Primary key of inserted item</returns> public int insertExpenseItem(FinancialListBoxExpenseItem item) { int primaryKey = 0; connection.RunInTransaction(() => { connection.Insert(item); primaryKey = item.expenseID; }); return primaryKey; } /// <summary> /// Method #2: Inserts an FinancialListBoxExpenseItem into Database /// </summary> /// <param name="item"></param> /// <returns>Primary key of inserted item</returns> public int insertExpenseItem2(FinancialListBoxExpenseItem item) { int primaryKey = 0; connection.RunInTransaction(() => { connection.Insert(item); primaryKey = connection.ExecuteScalar<int>("SELECT last_insert_rowid()"); }); return primaryKey; } } 

The id property in the FinancialListBoxItem class is as follows:

  public class FinancialListBoxExpenseItem : Money.Common.BindableBase { private int _expenseID = 0; [AutoIncrement, PrimaryKey] public int expenseID { get { return _expenseID; } set { this.SetProperty<int>(ref _expenseID, value); } } } 

I would recommend you choose a different name for your "id" pk column. I don't know if Id is a bit of an internal keyword. EDIT: Well, that is not a SQLite keyword, but id is not a proper name in any way (Source: SQLite Keywords )

Answer 3

  public List<int[]> CreateSymbolByName(string SymbolName, bool AcceptDuplicates) { if (! AcceptDuplicates) // check if "AcceptDuplicates" flag is set { List<int[]> ExistentSymbols = GetSymbolsByName(SymbolName, 0, 10); // create a list of int arrays with existent records if (ExistentSymbols.Count > 0) return ExistentSymbols; //(1) return existent records because creation of duplicates is not allowed } List<int[]> ResultedSymbols = new List<int[]>(); // prepare a empty list int[] symbolPosition = { 0, 0, 0, 0 }; // prepare a neutral position for the new symbol try // If SQL will fail, the code will continue with catch statement { //DEFAULT und NULL sind nicht als explizite Identitätswerte zulässig string commandString = "INSERT INTO [simbs] ([En]) OUTPUT INSERTED.ID VALUES ('" + SymbolName + "') "; // Insert in table "simbs" on column "En" the value stored by variable "SymbolName" SqlCommand mySqlCommand = new SqlCommand(commandString, SqlServerConnection); // initialize the query environment SqlDataReader myReader = mySqlCommand.ExecuteReader(); // last inserted ID is recieved as any resultset on the first column of the first row int LastInsertedId = 0; // this value will be changed if insertion suceede while (myReader.Read()) // read from resultset { if (myReader.GetInt32(0) > -1) { int[] symbolID = new int[] { 0, 0, 0, 0 }; LastInsertedId = myReader.GetInt32(0); // (2) GET LAST INSERTED ID symbolID[0] = LastInsertedId ; // Use of last inserted id if (symbolID[0] != 0 || symbolID[1] != 0) // if last inserted id succeded { ResultedSymbols.Add(symbolID); } } } myReader.Close(); if (SqlTrace) SQLView.Log(mySqlCommand.CommandText); // Log the text of the command if (LastInsertedId > 0) // if insertion of the new row in the table was successful { string commandString2 = "UPDATE [simbs] SET [IR] = [ID] WHERE [ID] = " + LastInsertedId + " ;"; // update the table by giving to another row the value of the last inserted id SqlCommand mySqlCommand2 = new SqlCommand(commandString2, SqlServerConnection); mySqlCommand2.ExecuteNonQuery(); symbolPosition[0] = LastInsertedId; // mark the position of the new inserted symbol ResultedSymbols.Add(symbolPosition); // add the new record to the results collection } } catch (SqlException retrieveSymbolIndexException) // this is executed only if there were errors in the try block { Console.WriteLine("Error: {0}", retrieveSymbolIndexException.ToString()); // user is informed about the error } CreateSymbolTable(LastInsertedId); //(3) // Create new table based on the last inserted id if (MyResultsTrace) SQLView.LogResult(LastInsertedId); // log the action return ResultedSymbols; // return the list containing this new record }