SQLite transaction is not succeeded although I do not rollback my transaction?

Question

Create the 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 same Primary Key value to raise 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 its best practice to use the SQLiteTransaction class for every executed sql command. The connection from the transaction needs to be shared among the dataprovider methods.

I am asking YOU now multiple questions:

1.) When a SQLiteException occurs because of inserting same primary keys "John" there is not inserted any of the "John" values. That is ok because I used a transaction and the .Commit() must be executed. What bothers me is WHY does it not make any difference wether OR NOT I use trans.Rollback() in the catch-block.

2.) I am using the "using(resource)"-statement so what will happen if the transaction succeeds/commits to the state of the connection ? Will it be closed? Just concern that I do not do use the `using(var trans = new SQLiteTransaction()){...}

Answer 1

To answer your questions:

1. Transactions must be committed explicitly as Daniel said. In an unexpected error condition I would rather my data be left as is and not in a half-committed state, which is the point of a transaction. In this case, the catch block could be used to retry an operation with different parameters and such. In many cases with my work, if the transaction hits the end of a using statement without a commit it will roll it back without me coding an explicit try/catch. Remember, in almost all exception cases the objects in a using block will still be disposed, even if you don't catch the exception. (I like this method because the code is cleaner without try/catches everywhere - I only use try/catch when I can react accordingly)
2. The using statement is fine. If the transaction has been committed, nothing will be rolled back. If the transaction has not been committed the transaction will be rolled back. Keep in mind though, disposing a transaction object will not explicitly close the underlying database connection.

One thing I noticed, though, is that your command objects you've created are not associated with the transaction. If this code were to be executed against SQL server or Oracle an exception would be thrown stating that all commands must be assigned the active transaction (if there is one).

To associate the command with the transaction you'll need the following piece of code after each new command object created:

cmd.Transaction = trans;

Typically my database code follows the format of:

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 flow of the connections above will ensure that all related resources with the connection, the transaction, and the command object are cleaned up in the event of an exception. If an exception is thrown, the error is on the line that threw it, not the catch block that caught and threw it again. In addition, the transaction would be rolled back and the underlying database connection would be closed (or returned to the pool, if one existed).

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

Answer 2

Because there is an implicit rollback with transactions. Commits have to be explicit.

The connection will be closed eventually by the runtime.