CODEWITHSUNDEEP
c#transactionspetapoco
antinescience
antinescience

Reputation: 2359

PetaPoco - setting transaction isolation level

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.

Upvotes: 9

Views: 5007

Answers (3)

Plebsori
Plebsori

Reputation: 1085

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

Upvotes: 3

David
David

Reputation: 1774

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.

Upvotes: 0

David L
David L

Reputation: 33833

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"

Upvotes: 4

Related Questions