How to get the last auto-increment id from a SQLite table?

Question

I have a Messages table with column identifiers (primary key, auto-increment) and Content (text).
I have a Users table with column names (primary key, text) and Hash.
A message is sent by one sender (user) to many recipients (user), and the recipient (user) can have many messages.
I created a Messages_Recipients table with two columns: MessageID (referring to the column identifier of the message table and the recipient (referring to the username column in the "Users" table). This table represents many, many relationships between recipients and messages.

So, I have a question. The identifier of the new message will be created after it is stored in the database. But how can I use the MessageRow link I just added to get this new MessageID?
I can always find the database for the last row added, but is it possible that it can return another row in a multi-threaded environment?

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

My Persistence code (Messages and Recipients 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

With SQL Server, you would select SCOPE_IDENTITY () to get the latest authentication value for the current process.

With SQlite, it is like the auto-increment you would do

 SELECT last_insert_rowid() 

immediately after your insertion.

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

In response to your comment, to get this value, you would like to use SQL or OleDb code, for example:

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

Answer 2

Another option is to look at the sqlite_sequence system table. Your sqlite database will have this table automatically if you created any table with a primary auto-increment key. This table is intended for sqlite to track the auto-increment field so that it does not repeat the primary key even after you delete several rows or after some insertion fails (more on this here http://www.sqlite.org/ autoinc.html ).

So there is an added benefit to this table that you can learn about your first element primary key added even after you inserted something else (in other tables, of course!). After making sure your insert is successful (otherwise you get a false number), you just need to do:

 select seq from sqlite_sequence where name="table_name" 

Answer 3

I am having problems using SELECT last_insert_rowid() in a multi-threaded environment. If another thread is inserted into another table that has autoinc, last_insert_rowid will return the value of autoinc from the new table.

Here, where they claim in doco:

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

What from sqlite.org doco

Answer 4

Here is a sample code from @polyglot's solution

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