SQLite under ORMLite doesn't allow any action after transaction if finished

Question

After I create and commit a transaction in SQLite through ServiceStack's OrmLite I cannot go on and issue any queries.

For example, the following test fails:

        [Test, Explicit]
        public void Can_query_after_transaction_is_committed()
        {
            var connection = new OrmLiteConnectionFactory(":memory:", false, SqliteDialect.Provider, true);
            using (var db = connection.OpenDbConnection())
            {
                db.DropAndCreateTable<SimpleObject>();
                var trans = db.OpenTransaction();
                db.Insert(new SimpleObject{test="test"});
                trans.Commit();
                Assert.DoesNotThrow(()=> db.Select<SimpleObject>()); //throws
            }
        }

class SimpleObject{public string test { get; set; }}

The exception I get is: "Transaction is not associated with the command's connection" failing around that line of OrmLite. However, I should not be in the transaction at all.

When I use SQL Server as the provider with the code like

new OrmLiteConnectionFactory(
                    @"Data Source=.\SQLEXPRESS;Initial Catalog=TestEmpty;Persist Security Info=True;User ID=db;Password=db;",
                     false, SqlServerDialect.Provider, true);*/

this test works fine.

Am I ending transaction incorrectly? Is it a bug in ServiceStack.OrmLite?

Answer 1

Turns out a similar problem has already been reported and fixed in the version I'm currently using. After comparing my test with the passing one I found out I dindn't Dispose() my transaction.

In the end the answer is: the transaction must be disposed. If it isn't the code will fail when using SQLite.

The following test passes:

        public void Can_query_after_transaction_is_committed()
        {
            var connection = new OrmLiteConnectionFactory(":memory:", true, SqliteDialect.Provider, true);
            using (var db = connection.OpenDbConnection())
            {
                db.DropAndCreateTable<SimpleObject>();
                using (var trans = db.OpenTransaction()) 
                {
                   db.Insert(new SimpleObject {test = "test"});
                   trans.Commit();
                }
                Assert.DoesNotThrow(()=> db.Select<SimpleObject>());
            }
        }