Run SET Statement in Dapper SqlBuilder or sp_exectuesql

Question

I'm trying to set transaction isolation level in Dapper using SqlBuilder, but didn't seem to work.

var builder = new SqlBuilder();
var sqlStatement = builder.AddTemplate(@"
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT * FROM Users
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
conn.Query<User>(sqlStatement.RawSql);

But this works:

conn.Execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
var result = conn.Query<User>(sqlStatement);
conn.Execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

I figured this maybe because with SqlBuilder, Dapper builds a dynamic SQL and execute with sp_executesql stored procedure.

To prove my hypothesis, I did try the following SQL statements:

exec sp_executesql N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED'

After that statement, I queried sys.dm_exec_sessions to check my connection and it's still showing ReadCommitted which is the default isolation level in my database.

Any way to get around setting transaction level (or any other SET statement) in a separate .Execute? Or maybe a special way to use SET statement in sp_executesql?

Answer 1

After another testing, the example I gave here seem to be working. I think it has to do our production SQL query.

We did find couple other ways to run this.

Specifying isolation level in transaction:

using (var trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
    conn.Query<User>(sqlStatement.RawSql, transaction: trans).Dump();
}

Add isolation level in builder template:

var readUncommitted = builder.AddTemplate("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
var readCommitted = builder.AddTemplate("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");