如何从SQLite表中检索最后一个自动增量ID？ [英] How to retrieve the last autoincremented ID from a SQLite table?

问题描述

我有一个表ID为（主键，自动递增）和内容（文字）的消息。

我有一个表用户具​​有列（主键，文本）和哈希。 />
一个发件人（用户）向许多收件人（用户）发送一条消息，而收件人（用户）可以发送许多邮件。\\
我创建了一个包含两列的Messages_Recipients表： MessageID（指消息表和收件人的ID列（指用户表中的用户名列）。此表表示收件人和邮件之间的多对多关系。

所以，问题I这是一个新的消息的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);
}

推荐答案

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

immediately after your insert.

http://www.mail-archive.com/[e-mail]/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();
}

这篇关于如何从SQLite表中检索最后一个自动增量ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！