在 C# 中使用 SQLite.NET 获取最后一个插入 ID [英] Getting the Last Insert ID with SQLite.NET in C#
问题描述
我有一个不那么简单的解决方案的简单问题......我目前正在将一些数据插入这样的数据库:
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);
最后一行很重要.为什么要提供连接?好吧,有一个名为 last_insert_rowid() 的 SQLite 函数,您可以调用它并获取最后一个插入 ID.问题是它绑定到一个连接,.NET 似乎正在为每个数据集操作重新打开和关闭连接.我认为从表适配器获得连接会改变事情.但它没有.
有谁知道如何解决这个问题?也许从哪里获得持续连接?或者更优雅的东西?
谢谢.
这也是事务的问题,如果我想使用事务,我需要相同的连接,所以这也是一个问题...
在 SQLite 中使用 C# (.net 4.0),SQLiteConnection 类有一个属性 LastInsertRowId
,它等于最最近插入(或更新)的元素.
如果表没有主整数键,则返回 rowID(在这种情况下,rowID is column 是自动创建的).
有关更多信息,请参阅 https://www.sqlite.org/c3ref/last_insert_rowid.html.>
对于在单个事务中包装多个命令,在事务开始之后和提交之前输入的任何命令都是一个事务的一部分.
long rowID;使用 (SQLiteConnection con = new SQLiteConnection([datasource]){SQLiteTransaction 事务 = null;交易 = con.BeginTransaction();... [执行插入语句]rowID = con.LastInsertRowId;交易.提交()}
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...
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()
}
这篇关于在 C# 中使用 SQLite.NET 获取最后一个插入 ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!