CODEWITHSUNDEEP
sqlitexamarinandroid-sqlitesqlite-net
sprocket12
sprocket12

Reputation: 5488

Xamarin Sqlite Insert does not return last primary key id

I have a simple entity:

[Table("History")]
public class History
{
    [PrimaryKey, AutoIncrement, Column("_id")]
    public int Id { get; set; }

    [Indexed(Name = "IDX_History", Order = 1, Unique = true)]
    public int Prefix { get; set; }

    [Indexed(Name = "IDX_History", Order = 2, Unique = true)]
    public int Stem { get; set; }

    [Indexed(Name = "IDX_History", Order = 3, Unique = true)]
    public int Suffix { get; set; }

    [Indexed(Name = "IDX_Favourite")]
    public bool IsFavourite { get; set; }

    public DateTime LastViewed { get; set; } = DateTime.Now;
}

I am trying to do an insert if its new or get the last inserted id if it already exists:

public static int SaveSolutionToHistory(Solution sol)
{
    lock (_db)
    {
        var existingHistory = _db.Table<History>().FirstOrDefault(h => h.Prefix == sol.Prefix.Id 
            && h.Stem == sol.Stem.Id 
            && h.Suffix == sol.Suffix.Id);

        if (existingHistory != null)
        {
            existingHistory.LastViewed = DateTime.Now;
            _db.Update(existingHistory);
            return existingHistory.Id;
        }

         _db.Insert(new History
        {
            Prefix = sol.Prefix.Id,
            Stem = sol.Stem.Id,
            Suffix = sol.Suffix.Id
        });

        return _db.ExecuteScalar<int>("SELECT last_insert_rowid()");
    }
}

The top part works fine in returning an id for existing entries but for some reason the insert code always returns 1 when it should return the last inserted primary autoincrement id (as mentioned in the methods XML comment):

     _db.Insert(new History
    {
        Prefix = sol.Prefix.Id,
        Stem = sol.Stem.Id,
        Suffix = sol.Suffix.Id
    });

And this returns the correct id:

return _db.ExecuteScalar<int>("SELECT last_insert_rowid()");

It seems inefficient for me to do two hits like this or to do it as non strongly typed. Is there a reason why the _db.Insert is not returning the correct Id?

I am using VS 2015, with HAXM and Marshmallow x86_64 Google API Image.

Upvotes: 3

Views: 2264

Answers (2)

Evgeniy Dovgal
Evgeniy Dovgal

Reputation: 9

You can insert rows in the database using Insert. If the table contains an auto-incremented primary key, then the value for that key will be available to you after the insert:

public static void AddStock (SQLiteConnection db, string symbol) {
    var s = new Stock { Symbol = symbol };
    db.Insert (s);
    Console.WriteLine ("{0} == {1}", s.Symbol, s.Id);
}

This is mean that ID will be available in object field Id.

https://components.xamarin.com/gettingstarted/sqlite-net

Upvotes: 0

Daniel Spruce
Daniel Spruce

Reputation: 51

I had the same problem, just found the answer here, the full xml comment is:

//
// Summary:
//     /// Inserts the given object and retrieves its /// auto incremented primary key
//     if it has one. ///
//
// Parameters:
//   obj:
//     /// The object to insert. ///
//
// Returns:
//     /// The number of rows added to the table. ///

What the summary means by "retrieves" is that it updates the value of the PK field in the object which you passed to it, so if you want the last inserted id, you can use that

Upvotes: 5

Related Questions