PetaPoco - setting transaction isolation level

Question

Using PetaPoco, you are allowed to manage transactions by 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 management, when it is committed in transactional updates, it is a little lacking to control the transaction isolation level, similar to how you did it with a traditional SQL connection:

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

A new transaction is returned to PetaPoco GetTransaction , which, using this constructor , calls BeginTransaction . In this case, BeginTransaction uses the .NET IDbConnection.BeginTransaction () - which has an overload to provide a level of transaction isolation. As far as I can tell, PetaPoco provides no way to provide an isolation level for this method. Does anyone know if it is possible to really change the isolation level of PetaPoco without having to delve into the source code and add an overloaded constructor with an isolation level? I am happy to do this and send a transfer request, but I want to make sure that before I do this work, I will not miss something direct.

Answer 1

Taking a quick look at the source code for PetaPoco seems like you are absolutely right. There doesn't seem to be anything in the source that allows you to change the level of the transaction, and I have not found any documentation on the Internet to support this behavior. I will have to look for your request for traction along the road! That would be very helpful!

However, as another alternative, could you explicitly set the transaction isolation level in the request that you submit?

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

Answer 2

In the latest version of PetaPoco you can set the isolation level.

Use uninterrupted configuration

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

Or a traditional constructor

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

Answer 3

Or in my case, where I want to avoid blocking, and I did not fuss about dirty reads, I would use:

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

which in terms of PetaPoco can be shortened to:

 FROM tblNewObject with(nolock) WHERE Id = @0 

This is only useful for selecting and matching columns that exist in your DTO.