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

Question

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); } 

Answer 1

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" 

Answer 2

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(); }