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(); } } 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.
BeginTransaction returns a DbTransaction which is IDisposable. Where do you call Dispose on it if you discard the reference?
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.
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.
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(); } 
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 
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.
