29

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();
    }
}
kateroh
  • 4,382
  • 6
  • 43
  • 62

5 Answers5

26

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.

Eddie Deyo
  • 5,200
  • 8
  • 35
  • 35
  • 1
    `BeginTransaction` returns a `DbTransaction` which is `IDisposable`. Where do you call `Dispose` on it if you discard the reference? – ta.speot.is Jun 15 '13 at 00:40
  • 6
    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. – DarrenMB Sep 05 '14 at 19:02
  • 9
    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. – foxontherock Apr 27 '16 at 01:59
12

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.

Bob Palmer
  • 4,714
  • 2
  • 27
  • 31
9

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();
}
piers7
  • 4,174
  • 34
  • 47
7

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
fiat
  • 15,501
  • 9
  • 81
  • 103
ArBR
  • 4,032
  • 2
  • 23
  • 29
  • Also, consider setting the DATAROWS Lock Scheme for your tables. – ArBR Dec 03 '10 at 01:24
  • 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. – kateroh Dec 14 '10 at 19:15
0

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.

Gordon Prince
  • 142
  • 2
  • 9