On 2016/06/28 12:17 PM, William Drago wrote:
I am programming in c# and I have to insert into a .sqlite file a new
row
and get back the id of this last inserted row. This file "unfortunately"
might be located on an internal network and other users might try to add
other rows in the meantime.
I can't help with your locking issues, but if you are using the
System.Data.SQLite ADO.NET provider in your C# program you can get the
ID of the last row inserted with the LastInsertRowID property of your
connection like this:
long rowID = connection.LastInsertRowId;
I use it after I commit the transaction.
Oh that's dangerous (sometimes) - it is possible for a second
process/application/DB connector to insert a row in between you closing
the transaction and reading the rowID so that it is no longer the last
row id... which is fine if you simply use it to record a relationship to
the inserted data in another place, but problematic if you use it to
guess/force the id of the next row to be inserted (probably in some next
transaction that might be a mile away).
Of course, in 99% of applications (as in applied instances, not
programs), this is unlikely to ever be a bother, but it's that other 1%
that causes 80% of the fails in real life[1], so it pays being diligent.
Thus, I'll add my voice to those forewarning Martina - it is best to
retrieve the next row ID to be inserted (or whatever value/field you use
for primary keying) within a transaction before the next inserts (which
should all happen inside that same transaction). That way can never[2] fail.
Cheers!
Ryan
[1]: Probably not 80% exactly, this figure is intended to illustrate the
principle, and doesn't claim to be empirical.
[2]: Again, "never" is used here approximate to the principle. In real
life, bugs happen. :)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
