我使用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;
}
}