ServiceStack Ormlite transactions broken?

Question

I am using ServiceStack.Ormlite for SQL Server and just updated from 3.9.71 to 4.0.33.0 and now transactions for direct commands are failing. I can get ORMlite transactions working or direct commands, but not both.

The complication is I am doing some very complicated DB commands and since Sql.In() for a large list of GUIDs is massively slow I have a workaround which uses db.CreateCommand() and then passes the GUID list in as a custom table type.

Thus I need a single transaction to span across ORMLite commands and direct db commands. The following code used to work and now it fails.

For instance the following code used to work. I now get errors saying that the CreateCommand() should use the transaction. When I directly try then I get the indicated cast exception:

            using (var db = DB.Connection.OpenDbConnection())
            {
                using (var transaction = db.OpenTransaction())
                {
                    // Some ORMLite code
                    db.Delete<SomeType>();

                    using (var command = db.CreateCommand())
                    {
                        // Direct DB command
                        command.CommandText = "Delete from SomeTable where ...";
                        command.Parameters.Add(GUIDList)

                        command.ExecuteNonQuery();
                    }
                }
            }

Clarification: In the code OpenTransaction() will work for the OrmLite code, but fail on the CreateCommand code. BeginTransaction() will fail for the OrmLite code.

The actual error is at command.ExecuteNonQuery(): ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

Answer 1

To use Transactions in OrmLite you should use the OpenTransaction() API, e.g:

using (var trans = db.OpenTransaction()) 
{
    //...
}

I've added a couple new API's to be able to use an OrmLite transaction with a raw ADO.NET IDbCommand in this commit.

Use a managed OrmLite DB Command

Use a managed OrmLite command with OpenCommand() which will automatically prepopulate the DB command with the current transaction, e.g:

using (var trans = db.OpenTransaction()) 
using (var command = db.OpenCommand())
{
    command.CommandText = "Delete from SomeTable where ...";
}

Manually assign underlying DB Transaction

When using the underlying ADO.NET IDbCommand you will need to also manually assign the Transaction to the command yourself, i.e:

using (var trans = db.OpenTransaction()) 
using (var command = db.CreateCommand())
{
    command.Transaction = trans.ToDbTransaction();
    command.CommandText = "Delete from SomeTable where ...";
}

The ToDbTransaction() extension method lets you access the underlying ADO.NET IDbTransaction which is required when using the underlying ADO.NET IDbCommand.

Both of these new API's are available from v4.0.34+ that's now available on MyGet.

Answer 2

Here is my suggestion that works.It is based on previous answers

IDbConnection conn = DB.Connection;
IDbCommand cmd = conn.CreateCommand();
using (IDbTransaction transaction = conn.OpenTransaction())
{
    //ADO.NET code
    cmd.Transaction = transaction.ToDbTransaction();
    cmd.CommandText = "...Some sql text";
    cmd.executeNonQuery();
    // Some ORMLite code
    conn.Delete<SomeType>();
}