.NET / SQLite. Inserting parent/child record; how do I get the foreign key?

Question

I use VS 2008 (C#) and SQLite via ADO.NET 2.0 Provider (SourceForce project).

The database used by application contains an "employees" (parent) and "employmentHistory" (children) datatables. "eploymentHistory" datatables is supposed to contain all the working contracts for any given employee from day 1 (e.g. a promotion will generate a new contract).

Unfortunately SQLite doesn't support foreign keys, so I need to take care of data consistency myself.

The employmentHistory database contains "id INTEGER PRIMARY KEY NOT NULL, employeeID INTEGER..." columns So obviously, employeeID will refer to employess.ID, the primary key in the employees table. It identifies WHOSE contract it is.

I'm writing a method for adding a new employee. Everybody will have at least one contract (the first one), so adding a new employee is connected with adding the contract.

Here's the method:

internal static void AddNewEmployee(Employee e, ContractChange c)
    {
        SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
        var insert = new SQLiteCommand(connection);
        insert.CommandText = @"INSERT INTO employees VALUES ( null, @firstName, @lastName, @phone, @mobile, null, null, ";
        insert.CommandText+= @"@birthDate, @sex, @address, @nin, null, null); ";
        insert.Parameters.AddWithValue("firstName", e.info.name);
        insert.Parameters.AddWithValue("lastName", e.info.surname);
        insert.Parameters.AddWithValue("phone", e.info.phone);
        insert.Parameters.AddWithValue("mobile", e.info.mobile);

        insert.Parameters.AddWithValue("birthDate", e.info.DOB);
        insert.Parameters.AddWithValue("sex", e.info.sex);
        insert.Parameters.AddWithValue("address", e.info.address);
        insert.Parameters.AddWithValue("nin", e.info.NIN);

        insert.CommandText += @"INSERT INTO employmentHistory VALUES ( null, null, @startDate, 'true', @position, @contractHrs, ";
        insert.CommandText += @"@holidayAllowance, @comments, null); ";
        insert.Parameters.AddWithValue("startDate", c.date);
        insert.Parameters.AddWithValue("position", (int)c.role);
        insert.Parameters.AddWithValue("contractHrs", (int)c.contractHrs);
        insert.Parameters.AddWithValue("holidayAllowance", c.holidayAllowance);
        insert.Parameters.AddWithValue("comments", c.comments);                        

        DataTable employees = dataset.Tables[0];
        var datarowEmp = employees.NewRow();
        datarowEmp = e.ToDataRow(datarowEmp);
        employees.Rows.Add(datarowEmp);

        DataTable employmentHistory = dataset.Tables[1];
        var datarowContract = employmentHistory.NewRow();
        datarowContract = c.ToDataRow(datarowContract);
        employmentHistory.Rows.Add(datarowContract);

        adapter.UpdateCommand = insert;
        adapter.InsertCommand = insert;
        adapter.SelectCommand = new SQLiteCommand("SELECT * FROM employees; SELECT * FROM employmentHistory;", connection);
        adapter.Update(dataset);


    }

So I thought I would set the employeeID "manually" with a quick update. But - how do I know what ID was assigned to the employee I just added???

Amazingly (to me), even after SQLiteDataAdapter updates the data (I set a breakpoint at the very end of the method), this information is still not in the dataset. The field is still null. And yet SQLite does attribute the unique number at some point, as no exception is thrown and the employees are indeed getting primary keys (I saw the database in SQLite Database Browser).

So I can't do it automatically, and I can't do it manually - how am I supposed to enforce database consistency? :(

Answer 1

select last_insert_rowid(); 

Answer 2

See last_insert_rowid

You can implement it in a single line:

cmd.CommandText = "INSERT INTO .. VALUES(..);SELECT last_insert_rowid() AS [ID]";
strId = cmd.ExecuteScalar().ToString()

Update:

You might also want to enclose the two inserts within a single transaction, so if one of them fails, you can rollback the entire insert, and prevent haveing corrupted data in your database.