How to get LastInsertRowId?

Question

SQLite

Greetings!
Here is the code:

public class Database
    {
        public SQLiteConnection Connection 
        {
            get
            {
                var connectionString = "Data Source=" + Settings.Default.database_filepath + ";Version=3;";
                var value = new SQLiteConnection(connectionString);
                return value.OpenAndReturn();
            }
        }

        public void Close()
        {
            if (Connection.State == ConnectionState.Open)
            {
                Connection.Close();
            }
        }
    }
.....
        public void Add(out long lastInsertedId)
        {
            var database = new Database();

            using (var dataAdapter = new SQLiteDataAdapter
            {
                InsertCommand = new SQLiteCommand("INSERT INTO hardware (title, component_parts, serial_number, inventory_number, category_id) VALUES (@title, @component_parts, @serial_number, @inventory_number, @category_id)", database.Connection)
            })
            {
                dataAdapter.InsertCommand.Parameters.AddWithValue("@title", Title);
                dataAdapter.InsertCommand.Parameters.AddWithValue("@component_parts", ComponentParts);
                dataAdapter.InsertCommand.Parameters.AddWithValue("@serial_number", SerialNumber);
                dataAdapter.InsertCommand.Parameters.AddWithValue("@inventory_number", InventoryNumber);
                dataAdapter.InsertCommand.Parameters.AddWithValue("@category_id", CategoryId);
                dataAdapter.InsertCommand.ExecuteNonQuery();

                lastInsertedId = database.Connection.LastInsertRowId;
            }

            database.Close();
        }

Everything is fine, everything is added, but only LastInsertRowId is always equal to 0. Please tell me how to fix it and why it doesn't work?
Thank you!

Answer 1

try to run query after insert:
see dbfiddle example