如何从 SQLite 表中检索最后一个自动递增的 ID?

2021-11-30 00:00:00 database sqlite auto-increment primary-key junction-table

我有一个表消息，其中包含列 ID(主键、自动增量)和内容(文本).
我有一个表用户，其中包含用户名(主键、文本)和哈希列.
一条消息由一个发送者(用户)发送给多个接收者(用户)，一个接收者(用户)可以有很多条消息.
我创建了一个包含两列的 Messages_Recipients 表:MessageID(指的是 Messages 表的 ID 列和 Recipient(指的是 Users 表中的 username 列).该表表示收件人和消息之间的多对多关系.

所以，我的问题是这个.新消息的 ID 将在它存储在数据库中后创建.但是我如何保存对刚刚添加的 MessageRow 的引用以检索这个新的 MessageID?
当然，我总是可以在数据库中搜索添加的最后一行，但这可能会在多线程环境中返回不同的行吗?

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?

据我了解，对于 SQLite，您可以使用 SELECT last_insert_rowid().但是我如何从 ADO.Net 调用这个语句?

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

推荐答案

使用 SQL Server，您将 SELECT SCOPE_IDENTITY() 获取当前进程的最后一个标识值.

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

使用 SQlite，它看起来就像你会做的自动增量

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

SELECT last_insert_rowid()

在您插入后立即.

http://www.mail-archive.com/sqlite-users@sqlite.org/msg09429.html

在回答您的评论以获得此值时，您需要使用 SQL 或 OleDb 代码，例如:

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

相关文章