How to retrieve the last autoincremented ID from a SQLite table?

I have a table Messages with columns ID (primary key, autoincrement) and Content (text).
I have a table Users with columns username (primary key, text) and Hash.
A message is sent by one Sender (user) to many recipients (user) and a recipient (user) can have many messages.
I created a table Messages_Recipients with two columns: MessageID (referring to the ID column of the Messages table and Recipient (referring to the username column in the Users table). This table represents the many to many relation between recipients and messages.

So, the question I have is this. The ID of a new message will be created after it has been stored in the database. But how can I hold a reference to the MessageRow I just added in order to retrieve this new MessageID?
I can always search the database for the last row added of course, but that could possibly return a different row in a multithreaded environment?

EDIT: As I understand it for SQLite you can use the SELECT last_insert_rowid(). But how do I call this statement from ADO.Net?

My Persistence code (messages and messagesRecipients are DataTables):

public void Persist(Message message)
{
    pm_databaseDataSet.MessagesRow messagerow;
    messagerow=messages.AddMessagesRow(message.Sender,
                            message.TimeSent.ToFileTime(),
                            message.Content,
                            message.TimeCreated.ToFileTime());
    UpdateMessages();
    var x = messagerow;//I hoped the messagerow would hold a
    //reference to the new row in the Messages table, but it does not.
    foreach (var recipient in message.Recipients)
    {
        var row = messagesRecipients.NewMessages_RecipientsRow();
        row.Recipient = recipient;
        //row.MessageID= How do I find this??
        messagesRecipients.AddMessages_RecipientsRow(row);
        UpdateMessagesRecipients();//method not shown
    } 

}

private void UpdateMessages()
{
    messagesAdapter.Update(messages);
    messagesAdapter.Fill(messages);
}

One other option is to look at the system table sqlite_sequence. Your sqlite database will have that table automatically if you created any table with autoincrement primary key. This table is for sqlite to keep track of the autoincrement field so that it won't repeat the primary key even after you delete some rows or after some insert failed (read more about this here http://www.sqlite.org/autoinc.html).

So with this table there is the added benefit that you can find out your newly inserted item's primary key even after you inserted something else (in other tables, of course!). After making sure that your insert is successful (otherwise you will get a false number), you simply need to do:

select seq from sqlite_sequence where name="table_name"

With SQL Server you'd SELECT SCOPE_IDENTITY() to get the last identity value for the current process.

With SQlite, it looks like for an autoincrement you would do

SELECT last_insert_rowid()

immediately after your insert.

http://www.mail-archive.com/[email protected]/msg09429.html

In answer to your comment to get this value you would want to use SQL or OleDb code like:

using (SqlConnection conn = new SqlConnection(connString))
{
    string sql = "SELECT last_insert_rowid()";
    SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    int lastID = (Int32) cmd.ExecuteScalar();
}

I've had issues with using SELECT last_insert_rowid() in a multithreaded environment. If another thread inserts into another table that has an autoinc, last_insert_rowid will return the autoinc value from the new table.

Here's where they state that in the doco:

> If a separate thread performs a new INSERT on the same database connection while the sqlite3_last_insert_rowid() function is running and thus changes the last insert rowid, then the value returned by sqlite3_last_insert_rowid() is unpredictable and might not equal either the old or the new last insert rowid.

That's from sqlite.org doco

Sample code from @polyglot solution

SQLiteCommand sql_cmd;
sql_cmd.CommandText = "select seq from sqlite_sequence where name='myTable'; ";
int newId = Convert.ToInt32( sql_cmd.ExecuteScalar( ) );

According to https://stackoverflow.com/questions/7575166/android-sqlite-get-last-insert-row-id there is another query:

SELECT rowid from your_table_name order by ROWID DESC limit 1

sqlite3_last_insert_rowid() is unsafe in a multithreaded environment (and documented as such on SQLite) However the good news is that you can play with the chance, see below

ID reservation is NOT implemented in SQLite, you can also avoid PK using your own UNIQUE Primary Key if you know something always variant in your data.

Note: See if the clause on RETURNING won't solve your issue https://www.sqlite.org/lang_returning.html As this is only available in recent version of SQLite and may have some overhead, consider Using the fact that it's really bad luck if you have an insertion in-between your requests to SQLite

see also if you absolutely need to fetch SQlite internal PK, can you design your own predict-able PK: https://sqlite.org/withoutrowid.html

If need traditional PK AUTOINCREMENT, yes there is a small risk that the id you fetch may belong to another insertion. Small but unacceptable risk.

A workaround is to call twice the sqlite3_last_insert_rowid()

#1 BEFORE my Insert, then #2 AFTER my insert

as in :

int IdLast = sqlite3_last_insert_rowid(m_db);	// Before (this id is already used)

const int rc = sqlite3_exec(m_db, sql,NULL, NULL, &m_zErrMsg);

int IdEnd = sqlite3_last_insert_rowid(m_db);	// After Insertion most probably the right one,

In the vast majority of cases IdEnd==IdLast+1. This the "happy path" and you can rely on IdEnd as being the ID you look for.

Else you have to need to do an extra SELECT where you can use criteria based on IdLast to IdEnd (any additional criteria in WHERE clause are good to add if any)

Use ROWID (which is an SQlite keyword) to SELECT the id range that is relevant.

"SELECT my_pk_id FROM Symbols WHERE ROWID>%d && ROWID<=%d;",IdLast,IdEnd); 

// notice the > in: ROWID>%zd, as we already know that IdLast is NOT the one we look for.

As second call to sqlite3_last_insert_rowid is done right away after INSERT, this SELECT generally only return 2 or 3 row max. Then search in result from SELECT for the data you Inserted to find the proper id.

Performance improvement: As the call to sqlite3_last_insert_rowid() is way faster than the INSERT, (Even if mutex may make that wrong it is statistically true) I bet on IdEnd to be the right one and unwind the SELECT results by the end. Nearly in every cases we tested the last ROW does contain the ID you look for).

Performance improvement: If you have an additional UNIQUE Key, then add it to the WHERE to get only one row.

I experimented using 3 threads doing heavy Insertions, it worked as expected, the preparation + DB handling take the vast majority of CPU cycles, then results is that the Odd of mixup ID is in the range of 1/1000 insertions (situation where IdEnd>IdLast+1)

So the penalty of an additional SELECT to resolve this is rather low.

Otherwise said the benefit to use the sqlite3_last_insert_rowid() is great in the vast majority of Insertion, and if using some care, can even safely be used in MT.

Caveat: Situation is slightly more awkward in transactional mode.

Also SQLite didn't explicitly guaranty that ID will be contiguous and growing (unless AUTOINCREMENT). (At least I didn't found information about that, but looking at the SQLite source code it preclude that)