SQLite transaction failed even though I'm not rolling back a transaction?

Question

Create a connection + transaction:

public SQLiteTransaction BeginTransaction() { var con = new SQLiteConnection(@"Data Source=A:\TransactionScopeTest\TransactionTest.db;Foreign Keys=ON"); con.Open(); var trans = con.BeginTransaction(); return trans; } 

Do 2 sqlite inserts with the same primary key value to throw an exception

 [TestMethod] public void TestMethod1() { using (var trans = BeginTransaction()) { try { SQLiteConnection con = trans.Connection; SQLiteCommand cmd1 = con.CreateCommand(); cmd1.CommandText = "INSERT INTO TEST(Name) VALUES('John')"; cmd1.ExecuteNonQuery(); SQLiteCommand cmd2 = con.CreateCommand(); cmd2.CommandText = "INSERT INTO TEST(Name) VALUES('John')"; cmd2.ExecuteNonQuery(); trans.Commit(); } catch (Exception) { trans.Rollback(); throw; } } } 

As I use SQLite, it is best to use the SQLiteTransaction class for each sql command executed. Transactional communication should be used in conjunction with the dataprovider methods.

I ask you some questions:

1.) When a SQLiteException occurs because the same primary John keys are inserted, no John values ​​are inserted. This is normal because I used a transaction and .Commit () must be executed. It bothers me why this doesn't make any difference. OR NOT I'm using trans.Rollback () in a catch block.

2.) I use the "using (resource)" statement, and what happens if the transaction succeeds / communicates with the connection state? Will it be closed? Just note that I am not using `using (var trans = new SQLiteTransaction ()) {...}

Answer 1

To answer your questions:

• Transactions must be made explicitly, as Daniel said. In the event of unforeseen errors, I would prefer that my data remain as it is, and not in a state of half perfection, which is the point of the transaction. In this case, the catch block can be used for repeated operations with various parameters, etc. In many cases with my work, if the transaction ends up using the statement without committing, it rolls back without encoding an explicit try / catch. Remember that in almost all cases of exceptions, objects in the used block will still be deleted, even if you do not catch the exception. (I like this method because the code is cleaner without try / catch all over the world - I use try / catch when I can respond accordingly)
• The using statement is fine. If the transaction was completed, nothing will be canceled. If the transaction was not completed, the transaction will be canceled. Keep in mind that deleting a transaction object will not explicitly close the underlying database connection.

However, I noticed that the command objects you create are not transaction related. If this code were to be run against an SQL server or Oracle, it would be out of the question that all teams should be assigned an active transaction (if any).

To associate a command with a transaction, you will need the following code fragment after creating each new command object:

 cmd.Transaction = trans; 

Usually my database code follows the format:

 using (SqlConnection connection = new SqlConnection("...")) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) using (SqlCommand command = connection.CreateCommand()) { command.Transaction = transaction; command.CommandText = "INSERT INTO ..."; // add parameters... command.ExecuteNonQuery(); transaction.Commit(); } // Reference to question 1: At this point in the code, assuming NO unhandled // exceptions occurred, the connection object is still open and can be used. // for example: using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT ..."; using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { // do awesome processing here. } } } } 

This connection flow above ensures that all associated resources with the connection, transaction, and command object are cleared if an exception occurs. If an exception is thrown, the error is on the line that throws it, and not the catch block that caught and threw again. In addition, the transaction will be canceled, and the underlying database connection will be closed (or returned to the pool, if one exists).

Remember that if something has a Dispose() method and implements the IDisposable interface, it is best to wrap it in a using statement, because even if the Dispose() call does nothing, there is no guarantee that it will be that way in the future.

Answer 2

Because there is an implicit rollback with transactions. Obligations must be explicit.

At the end of the session, the connection will be closed.