Get last insert rowid using Microsoft.Data.Sqlite C#

Question

I've created a UWP app with a sqlite database using the Microsoft.Data.Sqlite library. After inserting a new row, I need to know the autoincrement value assigned to the new row. The code below uses the additional query "select last_insert_rowid()", but is there a better way to obtain this value?

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;
    }
}

Answer 1

You could combine both queries into a single command, and instead of calling cmd.ExecuteReader() call cmd.ExecuteScalar() and convert the returned value to int:

public static long 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); SELECT last_insert_rowid();";
        cmd.Parameters.AddWithValue("@Title", movie.Title);
        cmd.Parameters.AddWithValue("@Rating", movie.Rating);

        return (long)cmd.ExecuteScalar();
    }
}

Note: Raycoon's answer points out some drawbacks of this solution.

Answer 2

Your initial way of retrieving the rowid (by separating the insert and select command) is more appropriate and more "secure". Combining the INSERT and SELECT commands will ignore unique constraint violations when inserting data in your table (in case you have a unique index). You will simply get "0" as new rowid - and no exception will be thrown!

Using separate commands results in SqliteExceptions when data cannot properly be inserted in your table. Therefore I wouldn't use the second, "correct" approach. Better use your initial code - but ExecuteNonQuery for INSERT and ExecuteScalar for SELECT last_insert_rowid().