There are different Isolation Levels in Transactions. The
one that we demonstrated in the example above is known as ReadOnCommited, which
means that you can read the newly inserted data only after the transaction is
committed. Of course, if you rollback, you will not see the new record. The
other important Isolation Level is ReadUnCommited, which is also known as
“Dirty Reads”. Let’s see what “Dirty Reads” means.
Dirty Reads
Dirty Reads means that you are able to read the data that is
being processed within the transaction and not yet committed or rolled back.
This means that you might be able to view the information of a user who was
never inserted in the database. Let’s see how we can implement "Dirty
Reads" using ADO.NET 2.0 Transactions.
Implementing Dirty Reads
In this example, I will create two SqlConnections. I am
creating two connections since in SQL SERVER 2000 you cannot have parallel
transactions. Apart from making two connections, I will also make two
SqlCommand objects. One command object will be used to insert a new user; the
second command object will be used to read the inserted user. Each of the
command objects will be accompanied with their own SqlTransaction objects.
Listing 4: AddUserDirtyRead() Method Implementation
public static bool AddUserDirtyRead(stringfirstName, string lastName)
{
bool result = false;
SqlTransaction myTransaction1 = null;
SqlTransaction myTransaction2 = null;
SqlConnection myConnection1 = newSqlConnection(ConnectionString);
SqlConnection myConnection2 = new SqlConnection(ConnectionString);
SqlCommand myCommand1 = newSqlCommand(SP_INSERT_USER, myConnection1);
myCommand1.CommandType =CommandType.StoredProcedure;
myCommand1.Parameters.AddWithValue("@FirstName", firstName);
myCommand1.Parameters.AddWithValue("@LastName",lastName);
SqlCommand myCommand2 = newSqlCommand("SELECT * FROM Users", myConnection2);
try
{
myConnection1.Open();
myConnection2.Open();
myTransaction1 =myConnection1.BeginTransaction();
myTransaction2 = myConnection2.BeginTransaction
(IsolationLevel.ReadUncommitted);
myCommand1.Transaction = myTransaction1;
myCommand2.Transaction = myTransaction2;
Console.WriteLine("Inserting newrecords into the database.");
myCommand1.ExecuteNonQuery();
SqlDataReader reader =myCommand2.ExecuteReader();
Console.WriteLine("Reading uncommiteddata..");
while (reader.Read())
{
if (reader.HasRows)
{
Console.WriteLine("FirstName:" + reader[1] + "," + "LastName:" +
reader[2]);
}
}
reader.Close();
result = true;
}
catch (Exception ex)
{
string exception = ex.Message;
myTransaction1.Rollback();
myTransaction2.Rollback();
}
finally
{
myTransaction1.Commit();
myTransaction2.Commit();
myConnection1.Close();
myConnection2.Close();
}
return result;
}
Analysis
As I mentioned in the example above, I created two
SqlConnection, SqlCommand, and SqlTransaction objects. myCommand1 is used to
insert a new user into the database table and myCommand2 is used to select all
records from the database table. Both of the connections are open and the transactions
are started. You will notice that I have set the transaction isolation level
for the myTransaction2 object. Check out the code below.
Listing 5 – Setting the Transaction Isolation Level
myTransaction2 =myConnection2.BeginTransaction(IsolationLevel.ReadUncommitted);
Notice that I have executed the myCommand object but not
committed the transaction. At this point I used the myCommand2 to run a
SqlDataReader and return all the records in the table. Since I had set the
transaction level to ReadUnCommitted, a newly inserted and uncommitted user
will be returned and displayed on the screen. This is called a “Dirty Read”
since we are reading data that is not yet committed.