.NET / SQLite。插入父/子记录;我如何获得外键?

发布于 2024-08-19 21:51:19 字数 2893 浏览 7 评论 0原文

我通过 ADO.NET 2.0 Provider(SourceForce 项目)使用 VS 2008 (C#) 和 SQLite。

应用程序使用的数据库包含“employees”(父级)和“employmentHistory”(子级)数据表。 “eploymentHistory”数据表应该包含任何给定员工从第一天开始的所有工作合同(例如,晋升将生成新合同)。

不幸的是 SQLite 不支持外键,所以我需要自己处理数据一致性。

雇佣历史数据库包含“id INTEGER PRIMARY KEY NOT NULL,employeeID INTEGER...”列 显然,employeeID 将引用employees.ID,即employees 表中的主键。它标识了合同是谁的。

我正在编写一种添加新员工的方法。每个人都至少有一份合同(第一份),因此添加新员工与添加合同相关。

方法如下:

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


    }

所以我想我可以通过快速更新“手动”设置employeeID。但是 - 我如何知道分配给我刚刚添加的员​​工的 ID 是什么???

令人惊讶的是(对我来说),即使在 SQLiteDataAdapter 更新数据之后(我在方法的最后设置了一个断点),此信息仍然不在数据集中。该字段仍然为空。然而,SQLite 确实在某个时刻赋予了唯一的编号,因为没有抛出异常,并且员工确实获得了主键(我在 SQLite 数据库浏览器中看到了数据库)。

所以我不能自动执行,也不能手动执行 - 我应该如何强制数据库一致性? :(

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? :(

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

我不在是我 2024-08-26 21:51:19

请参阅 last_insert_rowid

您可以在一行中实现它:

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

更新:

希望将两个插入包含在单个事务中,因此如果其中一个失败,您可以回滚整个插入,并防止数据库中的数据损坏。

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.

淡忘如思 2024-08-26 21:51:19
select last_insert_rowid(); 
select last_insert_rowid(); 
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文