Dependable insert with SQLite

Question

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

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

SELECT last_insert_rowid() AS 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)");

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?

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

Answer 1

It should absolutely be a SQLite transaction. Your best bet is to use a Sqlite data provider like this one: http://sqlite.phxsoftware.com/ Im not sure the basic ADO.Net on it's own will really work right.

EDIT FOR COMMENT:

Ok, so you just want to do a transaction like you would any other ADO transaction:

        SqliteCommand command = connection.CreateCommand();
        SqlTransaction transaction = null;

        connection.Open();

        transaction = connection.BeginTransaction();

        command.Transaction = transaction;

        command.CommandText = "Insert bla";
        command.ExecuteNonQuery();

        command.CommandText = "Update bla";
        command.ExecuteNonQuery();

        transaction.Commit();
        connection.Close();

you typically also want to put all that in a try/catch with a rollback

Note: that Im not 100% on that SQlLite command. Take a look at your documentation...but the rest should be right

Answer 2

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 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()";
    SQLiteDataReader reader = command.ExecuteReader();
    while (reader.Read()) {
        rowId = (long)reader[0];
    }

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