Mono.Data.Sqlite on Monotouch: How to get the value of an autoincrement field after inserting a row?

Question

OK, this is probably pretty basic stuff, but it took me quite some time to figure it out. And I guess there are a lot more .NET programmers like me, new to Monotouch and SQLite who don't know this.

I use Ado.NET (System.Data) with Monotouch and SQLite. In SQLite, every row of every table has an 64-bit signed integer called ROWID. You can use this, or if you prefer you can specify a field with INTEGER PRIMARY KEY AUTOINCREMENT, which SQLite will link to ROWID.

But how do you retrieve the value of this field after inserting a new record? Something like the @@identity keyword in Sql Server?

Searching around I found that the c-library for iOS of SQLite has a method sqlite3_last_insert_rowid() to retrieve this, but there is no equivalent of that in Mono.Data.Sqlite. In an older implementation (Mono.Data.SqliteClient) there was a LastInsertRowID() method, but that method disappeared in Mono.Data.Sqlite. Why?

Answer 1

SQLite has some internal core functions. One of these functions is last_insert_rowid(). So all you have to do is to issue a command "SELECT last_insert_rowid()". Example in Monotouch:

    public long GetLastInsertRowId(SqliteConnection connection)
    {
        // Assuming connection is an open connection from your INSERT
        using (SqliteCommand command = new SqliteCommand("SELECT last_insert_rowid()", connection))
        {
            return (long)command.ExecuteScalar();
        }
    }

Or you can combine the select with your insert, example:

string SqlCommand = "INSERT into Customers ([Name], .... [City]) VALUES (@Name, ... @City);SELECT last_insert_rowid();";