In SQL server stored procedure, we can set the isolation
level using the SET TRANSACTION ISOLATION LEVEL command. In Listing 1 it is
described. In ADO.NET the isolation level can be set by creating the
transaction using an overload of the BeginTransaction() method of the Command
or by setting the Isolation Level property of an existing Transaction object.
In Listing 2 it is described.
Listing 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
SELECT * FROM emp
SELECT * FROM dept
...
COMMIT TRANSACTION
Listing 2
myTransaction =myConnection.BeginTransaction(IsolationLevel.ReadUncommitted);
In the above code listing we can also specify SERIALIZABLE,
READ COMMITTED and READ UNCOMMITTED like REPEATABLE READ. Only one of the
options can be set at a time and it remains set for that connection until we
change it explicitly.
