Reputation: 7282
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...?
Upvotes: 1
Views: 3693
Reputation: 7282
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()";
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();
}
Upvotes: 4
Reputation: 4555
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
Upvotes: 2