How to set isolation level on SqlCommand/SqlConnection initialized with no transaction

Question

The following method is supposed to peroform a dirty read on an open connection. There are no transactions. Where do I set IsolationLevel?

public string DoDirtyRead(string storedProcName, SqlConnection connection)
{
    using (SqlCommand command = new SqlCommand(storedProcName, connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        // HOW TO SET IsolationLevel to READ_UNCOMMITTED here?
        command.ExecuteNonQuery();
    }
}

Conversable

Answer 1

On the BeginTransaction method: (MSDN link)

And if you just want to use hints in your SP at the table level, use WITH(NOLOCK) - but use at your own risk.

Afraid

(So you'd need to wrap this in a transaction to set isolation level, or just use hints in your SP). – Afraid

I dont want to use a transaction at all – Conversable

ok, have to do the transactions then and pass read_uncommitted. Thanks! – Conversable

Answer 2

If you don't want to do transactions, you can set it once when you open the connection and it will remain at that setting until you change it. So just do:

connection.BeginTransaction(IsolationLevel.ReadUncommitted).Commit();

Probably not the best for your specific case, since you are opening the connection, using it, and throwing it away, but I wanted to put this answer in for anyone with a longer-lived connection.

Glynda

BeginTransaction returns a DbTransaction which is IDisposable. Where do you call Dispose on it if you discard the reference? – Galloping

If you want to avoid the disposable return object another option is to just execute a non-query of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; after you open your connection. Then the connection will be in that state until it is closed or altered otherwise. – Alake

Watch out for connection pool... When you create your next "new" connection from the same connectionstring, it gives you back your old connection, with the same isolation level that you may have set. – Excogitate

Answer 3

Given you already have an existing connection (and possibly an existing transaction), I'd use a TransactionScope to control the isolation level of the child. This does a dirty read rowcount (I believe):

using (var command = connection.CreateCommand())
using(new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions{IsolationLevel = IsolationLevel.ReadUncommitted}))
{
    command.CommandText = string.Format("select count(*) from {0}", tableName);
    return (int)command.ExecuteScalar();
}

Hacksaw

How do I set the IsolationLevel in .NET5+? – Vc

Answer 4

In your Stored Procedure, for transact-sql use:

SET TRANSACTION ISOLATION LEVEL read uncommitted    -- 0
SET TRANSACTION ISOLATION LEVEL read committed     -- 1
SET TRANSACTION ISOLATION LEVEL repeatable read    -- 2
SET TRANSACTION ISOLATION LEVEL read serializable  -- 3

Kliber

Also, consider setting the DATAROWS Lock Scheme for your tables. – Kliber

thanks for your answer. This won't help because it will make the stored procedure do dirty reads all the time. Instead i want to control it in my code with the transaction or, if possible, without a transaction, so the stored proc can be called with either level of isolation. – Conversable

Answer 5

Add another parameter to your stored procedure to indicate the isolation level you want the stored procedure to run with.

IF @isolevel = 0 SET TRANSACTION ISOLATION LEVEL read uncommitted; ELSE

Also I believe uncommitted needs two "t's" in it.

Organo