PetaPoco - setting transaction isolation level

Question

Using PetaPoco, you're allowed to do transaction management doing something like this:

var newObject = new NewObject();
var newObjectId = 1;
using (var scope = db.GetTransaction())
{
    newObject = db.SingleOrDefault<NewObject>("SELECT * FROM tblNewObject WHERE Id = @0", newObjectId);
    scope.Complete();
}

While this is great for managing when in a transaction updates get committed, it's a little lacking for controlling the isolation level of a transaction similar to how you'd do it with a traditional SQL connection:

TransactionOptions transOptions = new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted };
using (new TransactionScope(TransactionScopeOption.Required, transOptions))
{
    //here be transactions
}

In PetaPoco, GetTransaction returns a new Transaction, which, using that specific constructor, calls BeginTransaction. BeginTransaction in this case, uses .NET's IDbConnection.BeginTransaction() - which has an overload to provide a transaction isolation level. As far as I can tell, PetaPoco does not provide any way to provide an isolation level to that method. Does anyone know if it's possible to actually modify the isolation level of PetaPoco without having to dig into the source and add an overloaded constructor that takes isolation level in? I'm happy to do that and submit a pull request, but I want to make sure before I do the work, I'm not missing something straightforward.

Answer 1

With the latest version of PetaPoco, you can now set the isolation level.

Using fluent configuration

var db = config.Build()
         .UsingConnectionString("cs")
         .UsingProvider<SqlServerDatabaseProvider>()
         .UsingIsolationLevel(IsolationLevel.Chaos)
         .Create();

 db.IsolationLevel.ShouldBe(IsolationLevel.Chaos);

Or traditional constructor

var db = new Database("MyConnectionStringName") { IsolationLevel = IsolationLevel.Chaos };

Answer 2

Having taken a quick look at the source code for PetaPoco, it looks like you're absolutely correct. It does not look like there is anything in the source that allows you to modify transaction level, nor have I found any documentation online to support that behavior. I will have to look for your pull request down the road! It would be quite useful!

However, as another alternative, could you not set the Transaction Isolation level explicitly in the query that you pass?

"SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM tblNewObject WHERE Id = @0"

Answer 3

Or in my case, where I want to avoid locking and and I'm not fussed about dirty reads, I'd use:

SELECT * FROM tblNewObject with(nolock) WHERE Id = @0

which, in PetaPoco terms, can still be shortened to:

FROM tblNewObject with(nolock) WHERE Id = @0

This has the benefit of only selecting and mapping the columns that exist in your DTO.