可靠插入使用SQLite [英] Dependable insert with SQLite

问题描述

我在写一个程序在C＃中，需要在多个表中插入数据。第一插入件返回一个最后的插入的rowid，后者又用于在其他表执行插入。在伪code：

I'm writing a program in C# that needs to insert data in multiple tables. The first insert returns a last insert rowid, which is in turn used to perform an insert on the other tables. In pseudo code:

INSERT INTO TableFoo (Col2, Col3, Col4, etc.) VALUES (@Bla2, @bla3, @bla4);

这立即插入我所获得最后插入ID后：

Immediately after this insert I get the last insert id by:

SELECT last_insert_rowid() AS RowId;

我检索一个DataReader的ROWID列并将其存储在一个int的RowId。 最后，我喜欢插入更多的表：

I retrieve the RowId column in a datareader and store it in an int "RowId". Finally I insert some more tables like:

INSERT INTO TableBar (FooId, Col2) VALUES (RowId, @BSomeMoreBla)");

问题：如果可能的话，我想在1交易执行该一堆刀片（和选择的RowId），以prevent一些半保存的数据。是否有可能使用事务在这种情况下，什么将是preferred方式？

The question: If possible, I want to perform this bunch of inserts (and a select RowId) in 1 transaction to prevent some half-saved data. Is it possible to use a transaction in this scenario and what would be the preferred way?

一个SQLite的交易？ C＃的System.Transactions的空间？ 第三种方式来执行原子数据库的多INSERT ...？

A SQLite transaction? C#'s System.Transactions namespace? A third way to perform an atomic database multi-insert...?

推荐答案

ryber帮我挺对我的方式，但我发现我一直在寻找我自己的解决方案。对于那些谁可能会感兴趣，这是我的code（需要一点或许是细化和微调，但完全合法的和可用的code）：

ryber helped me quite on my way, but I've found the solution I was looking for by myself. For those who might be interested, here's my code (needs a little finetuning perhaps, but perfectly legit and usable code):

NB。 TBL1有一个自动递增的PK，TBL2和TBL3依赖于通过FK TBL 1的PK。 如果发生异常，没有任何疑问的承诺，就像一个体面的交易应该表现;-)。只有当提交（）没有错误执行时，一大堆插入，即使是在路中间的选择。

nb. tbl1 has an auto incrementing PK, tbl2 and tbl3 depends on tbl1's PK via an FK. If an exception occurs, none of the queries are committed, just like a decent transaction should behave ;-). Only if Commit() is executed without errors, the whole bunch is inserted, even though there's a SELECT in the middle of the road.

try {
    transaction = connection.BeginTransaction();

    command = new SQLiteCommand();
    command.Transaction = transaction;
    command.CommandText = "INSERT INTO tbl1 (data) VALUES ('blargh')";
    command.ExecuteNonQuery();

    command = new SQLiteCommand();
    command.Transaction = transaction;
    command.CommandText = "SELECT last_insert_rowid()";
    rowId = Convert.ToInt64(command.ExecuteScalar());

    command = new SQLiteCommand();
    command.Transaction = transaction;
    command.CommandText = "INSERT INTO tbl2 (id, tbl1Id) VALUES (2, @rowId)";
    command.Parameters.Add(new SQLiteParameter("@rowId", rowId));
    command.ExecuteNonQuery();

    command = new SQLiteCommand();
    command.Transaction = transaction;
    command.CommandText = "INSERT INTO tbl3 (id, tbl1Id) VALUES (3, @rowId)";
    command.Parameters.Add(new SQLiteParameter("@rowId", rowId));
    command.ExecuteNonQuery();

    transaction.Commit();
}
catch (Exception ex) {
    if(connection.State == ConnectionState.Open)
        transaction.Rollback();
    MessageBox.Show(ex.Message);
}
finally {
    connection.Close();
    transaction.Dispose();
    command.Dispose();
}

这篇关于可靠插入使用SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！