Getting the Last Insert ID with SQLite.NET in C#

Question

I have a simple problem with a not so simple solution... I am currently inserting some data into a database like this:

kompenzacijeDataSet.KompenzacijeRow kompenzacija = kompenzacijeDataSet.Kompenzacije.NewKompenzacijeRow();
kompenzacija.Datum = DateTime.Now;
kompenzacija.PodjetjeID = stranka.id;
kompenzacija.Znesek = Decimal.Parse(tbZnesek.Text);

kompenzacijeDataSet.Kompenzacije.Rows.Add(kompenzacija);

kompenzacijeDataSetTableAdapters.KompenzacijeTableAdapter kompTA = new kompenzacijeDataSetTableAdapters.KompenzacijeTableAdapter();
kompTA.Update(this.kompenzacijeDataSet.Kompenzacije);

this.currentKompenzacijaID = LastInsertID(kompTA.Connection);

The last line is important. Why do I supply a connection? Well there is a SQLite function called last_insert_rowid() that you can call and get the last insert ID. Problem is it is bound to a connection and .NET seems to be reopening and closing connections for every dataset operation. I thought getting the connection from a table adapter would change things. But it doesn't.

Would anyone know how to solve this? Maybe where to get a constant connection from? Or maybe something more elegant?

Thank you.

EDIT:

This is also a problem with transactions, I would need the same connection if I would want to use transactions, so that is also a problem...

Alex Smith · Accepted Answer · 2016-03-08T15:40:11.287

88

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

See https://www.sqlite.org/c3ref/last_insert_rowid.html for more.

As for wrapping multiple commands in a single transaction, any commands entered after the transaction begins and before it is committed are part of one transaction.

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

    ... [execute insert statement]

    rowID = con.LastInsertRowId;

    transaction.Commit()
}

edited Mar 08 '16 at 15:40

answered Feb 10 '16 at 19:33

Alex Smith

1,096
8
12

6

This needs more upvotes. It's the way to go for newer applications as far as I see. – Manuel Hoffmann May 16 '16 at 14:05
`SQLiteAsyncConnection` doesn't have that property, but if you pass in an object to be saved, the object will have it's primary ID property updated. – Ted Nyberg Oct 10 '22 at 10:34

score 55 · Answer 2 · edited Aug 21 '13 at 09:09

55

select last_insert_rowid();

And you will need to execute it as a scalar query.

string sql = @"select last_insert_rowid()";
long lastId = (long)command.ExecuteScalar(sql); // Need to type-cast since `ExecuteScalar` returns an object.

edited Aug 21 '13 at 09:09

gnobal

18,309
4
30
35

answered Dec 03 '10 at 00:02

Will Marcouiller

23,773
22
96
162

2

rowid should be cast to Int64: `var result = (Int64)command.ExecuteScalar(sql);` – Johnny Jul 10 '13 at 10:07
I found that this answer does not work. The following answer (by Alex Smith) does work. – ttom Feb 07 '17 at 23:30

score 9 · Answer 3 · edited Dec 12 '20 at 05:05

9

I'm using Microsoft.Data.Sqlite package and I do not see a LastInsertRowId property. But you don't have to create a second trip to database to get the last id. Instead, combine both sql statements into a single string.

string sql = @"
    insert into MyTable values (null, @name);  
    select last_insert_rowid();";

using (var cmd = conn.CreateCommand()) {
    cmd.CommandText = sql;
    cmd.Parameters.Add("@name", SqliteType.Text).Value = "John";
    int lastId = Convert.ToInt32(cmd.ExecuteScalar());
}

edited Dec 12 '20 at 05:05

Daniel Szabo

7,181
6
48
65

answered Jun 06 '20 at 15:44

Ernie Thomason

1,579
17
20

2

Dotnet 5.0 w Microsoft.Data.Sqlite pkg 5.0.1. This is the only solution that worked for me, as the "LastInsertRowId" property did not exist on the SqliteConnection class. – Daniel Szabo Dec 12 '20 at 05:15
This question was also very helpful in explaining why this approach works: https://www.codeproject.com/Questions/830792/sqlite-Last-Insert-Rowid-returns-always – Daniel Szabo Dec 12 '20 at 05:30

score 8 · Answer 4 · answered Sep 11 '15 at 16:07

8

last_insert_rowid() is part of the solution. It returns a row number, not the actual ID.

cmd = CNN.CreateCommand();
cmd.CommandText = "SELECT last_insert_rowid()";
object i = cmd.ExecuteScalar();

cmd.CommandText = "SELECT " + ID_Name + " FROM " + TableName + " WHERE rowid=" + i.ToString();
i = cmd.ExecuteScalar();

answered Sep 11 '15 at 16:07

Greg Carrier

445
5
3

1

This doesn't seem to be true. I created a table with a `id INTEGER PRIMARY KEY`, and it always returns the generated ID of the last inserted row, not the row number. – Jez Feb 16 '19 at 16:47
I agree with above comment. No need for a second query. – Ernie Thomason Jun 06 '20 at 15:40

score 3 · Answer 5 · answered Sep 16 '19 at 19:52

There seems to be answers to both Microsoft's reference and SQLite's reference and that is the reason some people are getting LastInsertRowId property to work and others aren't.

Personally I don't use an PK as it's just an alias for the rowid column. Using the rowid is around twice as fast as one that you create. If I have a TEXT column for a PK I still use rowid and just make the text column unique. (for SQLite 3 only. You need your own for v1 & v2 as vacuum will alter rowid numbers)

That said, the way to get the information from a record in the last insert is the code below. Since the function does a left join to itself I LIMIT it to 1 just for speed, even if you don't there will only be 1 record from the main SELECT statement.

SELECT my_primary_key_column FROM my_table
WHERE rowid in (SELECT last_insert_rowid() LIMIT 1);

score 2 · Answer 6 · answered Aug 02 '17 at 10:22

2

The SQLiteConnection object has a property for that, so there is not need for additional query. After INSERT you just my use LastInsertRowId property of your SQLiteConnection object that was used for INSERT command. Type of LastInsertRowId property is Int64. Off course, as you already now, for auto increment to work the primary key on table must be set to be AUTOINCREMENT field, which is another topic.

answered Aug 02 '17 at 10:22

stonito

169
1
4

Actually, this isn't exactly correct. *AUTOINCREMENT* only guarantees that an id doesn't get reused. An INTEGER PRIMARY KEY field will return the RowId which auto increments as well, but a value may get reused if it has been previously deleted. For more info: https://www.sqlite.org/autoinc.html – Michael Silver Feb 22 '20 at 05:02
1

Thanks, helpful in the context of using the net core version of the library. The SQLiteAsyncConnection.InsertAsync method call returns the primary id as a Task. – Timothy Lee Russell Sep 02 '20 at 03:07
Not sure that's correct, as far as I can see, it still returns the number of affected rows. – Ted Nyberg Oct 10 '22 at 10:33

score 1 · Answer 7 · answered Oct 28 '17 at 14:14

1

    database = new SQLiteConnection(databasePath);

    public int GetLastInsertId()
    {
        return (int)SQLite3.LastInsertRowid(database.Handle);
    }

answered Oct 28 '17 at 14:14

Олег Ладыко

112
4

score 0 · Answer 8 · answered Jun 09 '20 at 23:14

# How about just running 2x SQL statements together using Execute Scalar?
# Person is a object that has an Id and Name property

var connString = LoadConnectionString(); // get connection string
using (var conn = new SQLiteConnection(connString)) // connect to sqlite
{

    // insert new record and get Id of inserted record
    var sql = @"INSERT INTO People (Name) VALUES (@Name);
                SELECT Id FROM People
                ORDER BY Id DESC";

    var lastId = conn.ExecuteScalar(sql, person);
}

Actually that isn't a bad solution but is much more work for the database depending on the implementation. But might be actually quite useful when inserting multiple items. — Felix K., Apr 07 '21 at 15:38

score 0 · Answer 9 · answered Oct 29 '21 at 16:40

In EF Core 5 you can get ID in the object itself without using any "last inserted".

For example:

var r = new SomeData() { Name = "New Row", ...};

dbContext.Add(r);

dbContext.SaveChanges();

Console.WriteLine(r.ID);

you would get new ID without thinking of using correct connection or thread-safety etc.

score 0 · Answer 10 · answered Feb 17 '22 at 21:26

If you're using the Microsoft.Data.Sqlite package, it doesn't include a LastInsertRowId property in the SqliteConnection class, but you can still call the last_insert_rowid function by using the underlying SQLitePCL library. Here's an extension method:

using Microsoft.Data.Sqlite;
using SQLitePCL;

public static long GetLastInsertRowId(this SqliteConnection connection)
{
    var handle = connection.Handle ?? throw new NullReferenceException("The connection is not open.");
    return raw.sqlite3_last_insert_rowid(handle);
}

Getting the Last Insert ID with SQLite.NET in C#

10 Answers10

Linked

Related