CODEWITHSUNDEEP
c#sqlsqlite
mishmash
mishmash

Reputation: 4458

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

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

Upvotes: 31

Views: 61325

Answers (11)

Dmitriy
Dmitriy

Reputation: 119

I believe using Returning clause on the insert statement is the simplest and most reliable way. You just do normal Insert and return your Id column wiht Returning. For tables that support RowId this can be standardized as below:

insert into MyTable (Column1, Column2) Values (Val1, Val2) Returning RowId;

In .Net you can just run it and return the value with ExecuteScalar.

Upvotes: 2

Ber'Zophus
Ber'Zophus

Reputation: 7697

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

Upvotes: 0

Boppity Bop
Boppity Bop

Reputation: 10463

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.

Upvotes: 0

Ernie Thomason
Ernie Thomason

Reputation: 1709

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

Upvotes: 12

user6723524
user6723524

Reputation: 19

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

Upvotes: 0

Jym
Jym

Reputation: 113

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

Upvotes: 3

Олег Ладыко
Олег Ладыко

Reputation: 112

    database = new SQLiteConnection(databasePath);

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

Upvotes: 1

stonito
stonito

Reputation: 179

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.

Upvotes: 2

Alex Smith
Alex Smith

Reputation: 1126

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

Upvotes: 91

Greg Carrier
Greg Carrier

Reputation: 465

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

Upvotes: 8

Will Marcouiller
Will Marcouiller

Reputation: 24132

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.

Upvotes: 56

Related Questions