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?

I usually do this with a call to `SqlConnection.BeginTransaction()` before the insert/update/delete, followed by `SqlTransaction.Commit()` afterward. — anu start, May 08 '18 at 17:43
We do what you have written in your original statement all over and it works just fine. Not sure why yours isn't working. Debug on 'RawSql' or just leave out the builder and pass in a string to help in the debug process. — BlackjacketMack, May 09 '18 at 16:58

score 4 · Accepted Answer · answered May 15 '18 at 04:09

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");

score 3 · Answer 2 · answered May 09 '18 at 09:08

3

Try this:

SELECT * FROM Users with (nolock)

It is same as using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before executing select statement.

More about : WITH(NOLOCK)

answered May 09 '18 at 09:08

Udit Solanki

531
5
12

Run SET Statement in Dapper SqlBuilder or sp_exectuesql

2 Answers2