CODEWITHSUNDEEP
sqlite.net-core
PermanentNovice
PermanentNovice

Reputation: 67

Microsoft.Data.SQLite (.net core) get atomic last_insert_rowid

I want to share the SQLite connection across threads.

If I type the following in an SQLite shell:

sqlite>begin; insert into Person values('Tweety','Bird',80); select last_insert_rowid(); commit;

The shell prints the new rowid.

In a dotnet core console application using Microsoft.Data.SQLite

            SqliteCommand command = new SqliteCommand("begin; insert into Person values('Tweety','Bird',81); select last_insert_rowid(); commit;", connection);

Console.WriteLine(command.ExecuteScalar());

I hoped the console would print the new rowid, but ExecuteScalar() returns null. I have confirmed the new row is inserted.

  1. Is it possible to get the new rowid with one combined command?
  2. Is this an atomic transaction if I share the connection over multiple threads?

Upvotes: 1

Views: 652

Answers (1)

PermanentNovice
PermanentNovice

Reputation: 67

I'll quote the comment by Vitaliy Fedorchenko:

It is common practice to use separate DB connection for each thread; usage efficiency remains good because of connection pool. During one connection session (between open and close) "last_insert_rowid()" returns id of last insert performed in this session.

Upvotes: 0

Related Questions