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>());
            }
        }

Answer 2

My problem was similar and my search led here. Trying to write a unit test to test my service, I was getting the same "Transaction is not associated with the command's connection" exception. The difference in my situation is that the only transaction I was using (in the service I was testing) WAS correctly disposing of it's connection, so I didn't think this applied.

(I am using ServiceStack v3.9.71.)

My test code (which failed) looked like this:

[Test]
public void Test_Service_Delete() {
    var DatabaseFactory = new OrmLiteConnectionFactory(":memory:", false, SqliteDialect.Provider, true);

    using (var db = DatabaseFactory.OpenDbConnection()) {
        var parentId = db.InsertParam(new ParentObject { name = "Bob" }, true);
        db.Insert(new ChildObject { ParentId = parentId, name = "Sam" });

        var service = Container.Resolve<TestService>();
        var response = service.Delete(new DeleteRequestObject(parentId));

        Assert.That(db.Select<ParentObject>(parentId), Has.Count.EqualTo(0));
        Assert.That(db.Select<ChildObject>("ParentId = {0}", parentId), Has.Count.EqualTo(0));
    }
}

My TestService.Delete method had a transaction in it (because it deletes the object and any associated child objects), but it was wrapped in a using block like so:

using (var db = DatabaseFactory.OpenDbConnection()) {
    using (var transaction = db.BeginTransaction(IsolationLevel.ReadCommitted)) {
        // do stuff here
    }
}

Still, the "Transaction is not associated with the command's connection" exception was thrown on the first line after the call to service.Delete.

My first attempt to solve it (which did not work) was this:

[Test]
public void Test_Service_Delete() {
    var DatabaseFactory = new OrmLiteConnectionFactory(":memory:", false, SqliteDialect.Provider, true);

    int parentId;
    using (var db = DatabaseFactory.OpenDbConnection()) {
        parentId = db.InsertParam(new ParentObject { name = "Bob" }, true);
        db.Insert(new ChildObject { ParentId = parentId, name = "Sam" });
    }

    var service = Container.Resolve<TestService>();
    var response = service.Delete(new DeleteRequestObject(parentId));

    using (var db = DatabaseFactory.OpenDbConnection()) {    
        Assert.That(db.Select<ParentObject>(parentId), Has.Count.EqualTo(0));
        Assert.That(db.Select<ChildObject>("ParentId = {0}", parentId), Has.Count.EqualTo(0));
    }
}

What eventually worked was wrapping the db calls after the service call in a transaction.

[Test]
public void Test_Service_Delete() {
    var DatabaseFactory = new OrmLiteConnectionFactory(":memory:", false, SqliteDialect.Provider, true);

    int parentId;
    using (var db = DatabaseFactory.OpenDbConnection()) {
        parentId = db.InsertParam(new ParentObject { name = "Bob" }, true);
        db.Insert(new ChildObject { ParentId = parentId, name = "Sam" });
    }

    var service = Container.Resolve<TestService>();
    var response = service.Delete(new DeleteRequestObject(parentId));

    using (var db = DatabaseFactory.OpenDbConnection()) {
        using (var transaction = db.OpenTransaction()) {
            Assert.That(db.Select<ParentObject>(parentId), Has.Count.EqualTo(0));
            Assert.That(db.Select<ChildObject>("ParentId = {0}", parentId), Has.Count.EqualTo(0));
        }
    }
}

I'm still fuzzy on WHY this workaround worked, but I figured I'd document it for anyone else running into this.