Discussion Forum

Thursday, November 22 2018 11:42 AM

I am trying to set set transaction isolation level read committed by using the System.Transactions.IsolationLevel.ReadUncommitted option in razor pages. The code executes fine but when I look into the sql profiler I see “set transaction isolation level read committed” instead of “set transaction isolation level read uncommitted”
Basically I am trying to replicate with (nolock) for a report query.
I tried several things using the movie demo that Microsoft uses in their tutorial on razor pages “RazorPagesMovie”.
I did several things:
Through context
////////////////
using (var t = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, new System.Transactions.TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}, System.Transactions.TransactionScopeAsyncFlowOption.Enabled))
{
Movie = _context.Movie.ToList();
//Movie = await _context.Movie.ToListAsync();
t.Complete();
t.Dispose();
}

///////////////////
Through direct connection
******************************
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{

SqlConnection cnn;
string connetionString = "Server=(localdb)\\mssqllocaldb;Database=RazorPagesMovieContext-bea184b4-d217-450a-997b-1b9c8c9f15b2;Trusted_Connection=True;MultipleActiveResultSets=true";
cnn = new SqlConnection(connetionString);

cnn.Open();
SqlCommand command;
string sql = null;
SqlDataReader dataReader;
sql = "set transaction isolation level read uncommitted";
command = new SqlCommand(sql, cnn);
command.ExecuteNonQuery();
sql = "SELECT * FROM dbo.Movie";
command = new SqlCommand(sql, cnn);
dataReader = command.ExecuteReader();

dataReader.Close();
command.Dispose();

cnn.Close();

}
************
Also I tried in VB.net
*******************
Dim top As New TransactionOptions
top.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
Dim ts As New TransactionScope(TransactionScopeOption.Required, top)
With ts
Dim objConn As SqlConnection = Nothing
objConn = New SqlConnection("Server=(localdb)\mssqllocaldb;Database=RazorPagesMovieContext-bea184b4-d217-450a-997b-1b9c8c9f15b2;Trusted_Connection=True;MultipleActiveResultSets=true")
objConn.Open()
Dim objComm As New SqlCommand("SELECT * FROM dbo.Movie", objConn)
objComm.ExecuteNonQuery()
End With


*************
I wonder if it happens in the background somehow, but would not take chances and will use stored procedure for that I guess.

reply