OK, this is probably pret开发者_如何学Cty basic stuff, but it took me quite some time to figure it out. And I guess there are a lot more .NET programmers like me, new to Monotouch and SQLite who don't know this.
I use Ado.NET (System.Data) with Monotouch and SQLite. In SQLite, every row of every table has an 64-bit signed integer called ROWID. You can use this, or if you prefer you can specify a field with INTEGER PRIMARY KEY AUTOINCREMENT, which SQLite will link to ROWID.
But how do you retrieve the value of this field after inserting a new record? Something like the @@identity keyword in Sql Server?
Searching around I found that the c-library for iOS of SQLite has a method sqlite3_last_insert_rowid() to retrieve this, but there is no equivalent of that in Mono.Data.Sqlite. In an older implementation (Mono.Data.SqliteClient) there was a LastInsertRowID() method, but that method disappeared in Mono.Data.Sqlite. Why?
SQLite has some internal core functions. One of these functions is last_insert_rowid(). So all you have to do is to issue a command "SELECT last_insert_rowid()". Example in Monotouch:
public long GetLastInsertRowId(SqliteConnection connection)
{
// Assuming connection is an open connection from your INSERT
using (SqliteCommand command = new SqliteCommand("SELECT last_insert_rowid()", connection))
{
return (long)command.ExecuteScalar();
}
}
Or you can combine the select with your insert, example:
string SqlCommand = "INSERT into Customers ([Name], .... [City]) VALUES (@Name, ... @City);SELECT last_insert_rowid();";
精彩评论