Get id from the last entry

Question

I need to insert a product into a ProductDB table and at the same time get the id from the product I just inserted, so I can use it in the next query as a Foreign Key I have been looking at different methods like "select last_insert_rowid()" and "SCOPE_IDENTITY()" but I can't get it to work, how do I get it to work

public static void SaveProduct(ProductModel product)
{  
   using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
   {
      cnn.Execute("INSERT INTRO ProductDB (Name, Price) VALUES (@Name, @Price);", 
      product);

      string ForeignKey = "the id from the last entry from the query above";

      cnn.execute("INSERT INTO ImageDB (Filename, Path, FK_Product) VALUES (@Filename, @Path," + ForeignKey + " )");
   }
}

Answer 1

In SQL Server has 3 functions (methods) for getting the last inserted id from the table.

IDENT_CURRENT() - returns the last-inserted identity value for a given table.

SCOPE_IDENTITY() - returns the last identity value inserted into an identity column in any table in the current session and current scope.

@@IDENTITY - returns the last inserted identity value in any table in the current session, regardless of scope.

We need SCOPE_IDENTITY(), so ATTENTION!!! This function must be used in the current scope, which located insert command.

Example:

declare 
    @new_id integer;
INSERT INTRO ProductDB (Name, Price) VALUES (@Name, @Price);
SET @new_id = SCOPE_IDENTITY();

Answer 2

If you would use System.Data.SQLite you could use the LastInsertRowId property.

Example:

    using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=.\Test.db"))
    {
        conn.Open();

        SQLiteCommand cmd = conn.CreateCommand();
        cmd.CommandText = $"INSERT INTO Test (name) values ('{Guid.NewGuid().ToString()}');";

        cmd.ExecuteNonQuery();
        id = conn.LastInsertRowId;

        conn.Close();
    }