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

kateroh picture kateroh · Dec 3, 2010 · Viewed 24.4k times · Source

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();
    }
}

Answer

Eddie Deyo picture Eddie Deyo · Aug 25, 2011

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.