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

Answer 1

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

Answer 2

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.