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

User · Answer

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

User · Answer

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 sqlite-users sqlite org 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

User · Answer

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

User · Answer

According to Android Sqlite get last insert row id there is another query   SELECT rowid from your table name order by ROWID DESC limit 1

User · Answer

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

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

Examples related to database

Examples related to sqlite

Examples related to primary-key

Examples related to auto-increment

Examples related to junction-table