How to get last autoincremented id with SQLite in Mono

It is a bit strange to me that every database has its own way to get the last inserted (@@IDENTITY) value.
It is actually even stranger to me that the ADO.Net connector packages do not unify this but I guess there are good reasons for this.

Anyway, if you are wondering on how to to get the just inserted autoincrement value after your insert using the Mono.Data.SqliteClient extensions, here is the way to go.
The Mono.Data.SqliteConnection class includes the LastInsertRowId property, which contains the desired value.

Which will give something like (in BOO):

import Mono.Data.SqliteClient
 
cnx = SqliteConnection("URI=file:sqliteDb.db")
cmd = SqliteCommand("INSERT INTO my_table (field) VALUES ('value')", cnx)
 
cnx.Open()
cmd.ExecuteNonQuery()
inserted_id = cnx.LastInsertRowId
cnx.Close()
    • Julien
    • August 4th, 2009

    In my opinion, the reason why ADO.Net does not allow to retrieve the last inserted id through the same way is to avoid data corruption by working on the wrong row. Of course, it depends the needs but I would tend to work on the returned @@IDENTITY by a simple ExecuteScalar. Something like this in C# for MsSql:

    cmd = new SqlCommand(“INSERT INTO my_table (field) VALUES (‘value’);SELECT @@IDENTITY;”, cnx);
    [...]
    inserted_id = (int)cmd.ExecuteScalar();

    In BOO and for SQlite, it should be like this:

    cmd = SqliteCommand(“INSERT INTO my_table (field) VALUES (‘value’);SELECT last_insert_rowid();”, cnx)
    [...]
    inserted_id = cmd.ExecuteScalar() as (int)

  1. No trackbacks yet.