我使用Microsoft.Data.Sqlite库创建了一个带有SQLite数据库的UWP应用程序。在插入新行后,我需要知道分配给新行的自增值。下面的代码使用附加查询"select last_insert_rowid()",但是否有更好的方法来获取此值?
public static int AddMovie(Movie movie)
{
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DB_FILENAME);
using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand cmd = new SqliteCommand();
cmd.Connection = db;
cmd.CommandText = "INSERT INTO Movie VALUES (NULL, @Title, @Rating)";
cmd.Parameters.AddWithValue("@Title", movie.Title);
cmd.Parameters.AddWithValue("@Rating", movie.Rating);
cmd.ExecuteReader();
// Is there a better way to get this value?
cmd = new SqliteCommand("SELECT last_insert_rowid()", db);
SqliteDataReader query = cmd.ExecuteReader();
var newId = 0;
if (query.Read())
{
newId = query.GetInt32(0);
}
db.Close();
return newId;
}
}