System.Data.SQLite

Hi! I trying to use SQLite with TransactionScope and has problems with it.

I have a code

namespace ConsoleApplication1
{
    static class Program
    {
        static void Main(string[ args) 
        {
            try
            {
                using (var scope = new TransactionScope()) 
                {
                    using (var connection = new SQLiteConnection(@"Data Source = C:\tasks.db")) 
                    {
                        connection.Open();
                        using (var transaction = connection.BeginTransaction()) 
                        {
                            SomeFuncWithException();
                            transaction.Commit();
                        }
                    }
                    scope.Complete();
                }
            }
            catch (Exception ex) 
            {
                Console.WriteLine(ex); 
            }
        }

After run:

System.Data.SQLite.SQLiteException: Library used incorrectly
No transaction is active on this connection
   в System.Data.SQLite.SQLiteTransaction.IsValid(Boolean throwError)
   в System.Data.SQLite.SQLiteTransaction.Rollback()
   в System.Data.SQLite.SQLiteEnlistment.Rollback(Enlistment enlistment)
   в System.Transactions.VolatileEnlistmentAborting.EnterState(InternalEnlistment enlistment)
   в System.Transactions.VolatileEnlistmentActive.InternalAborted(InternalEnlistment enlistment)
   в System.Transactions.TransactionStateAborted.EnterState(InternalTransaction tx)
   в System.Transactions.TransactionStateActive.Rollback(InternalTransaction tx, Exception e)
   в System.Transactions.Transaction.Rollback()
   в System.Transactions.TransactionScope.InternalDispose()
   в System.Transactions.TransactionScope.Dispose()
   в ConsoleApplication1.Program.Main(String[ args) в C:\ConsoleApplication1\ConsoleApplication1\Program.cs:line25

What's wrong?

I'll need to know the specific error message, and if possible a stack trace.

System.Data.SQLite.SQLiteException: Library used incorrectly
No transaction is active on this connection
   в System.Data.SQLite.SQLiteTransaction.IsValid(Boolean throwError)
   в System.Data.SQLite.SQLiteTransaction.Rollback()
   в System.Data.SQLite.SQLiteEnlistment.Rollback(Enlistment enlistment)
   в System.Transactions.VolatileEnlistmentAborting.EnterState(InternalEnlistment enlistment)
   в System.Transactions.VolatileEnlistmentActive.InternalAborted(InternalEnlistment enlistment)
   в System.Transactions.TransactionStateAborted.EnterState(InternalTransaction tx)
   в System.Transactions.TransactionStateActive.Rollback(InternalTransaction tx, Exception e)
   в System.Transactions.Transaction.Rollback()
   в System.Transactions.TransactionScope.InternalDispose()
   в System.Transactions.TransactionScope.Dispose()
   в ConsoleApplication1.Program.Main(String[ args) в C:\ConsoleApplication1\ConsoleApplication1\Program.cs:line25

Beat,

You are nesting transactions which I think are not supported by System.Data.SQLite

When you are within a transactionscope, don't start handling your own transactions with connection.BeginTransaction.
A connection that is opened within a transactionscope automatically enlists in the transaction of the transactionscope.
A BeginTransaction notices that there is already a transaction going on and doesn't do anything.

When you have an exception , the inner transaction is aborted and this aborts the whole transaction,
so when the transactionscope goes out-of-scope, there is no transaction going on anymore and thus you get the SQLiteException.

Your program should be like this :

    using (var scope = new TransactionScope()) 
     {
         connection.Open();
        SomeFuncWithException();
        scope.Complete();
     }

If you want to explicitly start a new transaction within a transactionscope,
you can do so by starting a new transactionscope with TransactionScopeOption.RequiresNew
before opening the connection like this :

    using (var scope = new TransactionScope()) 
     {
        using (var scope2 = new TransactionScope(TransactionScopeOption.RequiresNew)) 
         {
             connection.Open();
           SomeFuncWithException();
           scope2.Complete();
        }
        scope.Complete();
     }

Hope this helps.