Inserting and returning autoidentity in SQLite3

Question

I'm trying to figure out the best approach to getting the last ID when I insert a row into the database.

It's for a basic library which logs Exception and program state data into a SQLite database, using System.Data.SQLite.

The library could be called from console apps, forms apps, websites, services, single threaded, multi-threaded - so while this library is very small and simple, it's important that each call to it is stateless, stable and accurate.

In my main SQL libraries I use OUTPUT inserted.id, which keeps things very simple, but as far as I can tell, this is not available in SQLite. I did find one or two people mentioning it, but it doesn't seem to work for me.

If not, I see another approach is to create a cursor and use that to track the last inserted id. However, that's listed as an extension and I can't seem to find any sign of it in the .NET library.

The most relevant .NET example I could find used the second command approach which worries me.

My concern is that when exceptions are raised and logged, often a lot are raised very quickly, and I want to be 100% sure I'm linking up the InnerException chain correctly.

---

• Am I right in thinking INSERT INTO...OUTPUT inserted.id is not supported in SQLite?
• Assuming not, is the cursor the best way to get the last inserted id with no chance of problems with concurrent requests?
• If that is the best approach, is SELECT last_insert_rowid() limited to the current cursor?

Answer 1

Something like this is what you need to do:

using (var connection = new SQLiteConnection(ConnectionString))
{
    using (var command = new SQLiteCommand())
    {
        string sql = "INSERT INTO MyTable (Field1) VALUES (@val1); SELECT last_insert_rowid();";

        command.Connection=connection;
        command.CommandType = CommandType.Text;
        command.CommandText = sql;

        command.Parameters.Add(new SQLiteParameters("@val1", DbType.String) {value = "MyValue"});

        connection.Open();
        object obj = command.ExecuteScalar();
        long id = (long)obj; // Note regardless of data type, SQLite always returns autoincrement fields as long.
        // Do something with id
    }
}

Obviously you'll need to modify the SQL text to match what you need exactly.

Answer 2

As of version 3.35 SQLite supports RETURNING statement: see here

Answer 3

Using C# (.net 4.0) with SQLite, the SQLiteConnection class has a property LastInsertRowId that equals the Primary Integer Key of the most recently inserted (or updated) element.

The rowID is returned if the table doesn't have a primary integer key (in this case the rowID is column is automatically created).

Something like this will work:

long rowID;
using (SQLiteConnection con = new SQLiteConnection([datasource])
{
    SQLiteTransaction transaction = null;
    transaction = con.BeginTransaction();
    [execute insert statement]

    rowID = con.LastInsertRowId;

    transaction.Commit()
}

For more info, look at this link