.NET / SQLite. Insert parent / child record; How to get a foreign key?

Question

I am using VS 2008 (C #) and SQLite through the ADO.NET 2.0 provider (SourceForce project).

The database used by the application contains data on “employees” (parents) and “jobsHistory” (children). It is assumed that the data "eploymentHistory" should indicate all work contracts for any given employee from the 1st day (for example, a new contract will be created in the advertising campaign).

Unfortunately, SQLite does not support foreign keys, so I need to take care of data consistency myself.

The jobsHistory database contains "id INTEGER PRIMARY KEY NOT NULL, employeeID INTEGER ..." columns Obviously, employeeID will refer to employeeess.ID, the primary key in the employee table. It defines a WHOSE contract.

I am writing a method for adding a new employee. Each will have at least one contract (first), so adding a new employee is associated with adding a 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 that I would set employeeID "manually" with a quick update. But - how do I know which identifier was assigned to the employee I just added?

( ), , SQLiteDataAdapter ( ), . - . SQLite - , , ( SQLite Database Browser).

, , - ?: (

Answer 1

. last_insert_rowid

:

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

Update:

, , , .

Answer 2

select last_insert_rowid();