AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=789&pId=-1
A Look into Transactions in ADO.NET 2.0
page
by Mohammad Azam
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24355/ 56

Introduction

We all have used transactions in one way or the other. In this article, I will show you how you can make use of the SqlTransaction class, which is introduced in ADO.NET 2.0.

What are Transactions?

A transaction is a set of operations in which either all of them are successful or all of them fail to ensure consistency. A simple example of a transaction is when you go to the bank and deposit some amount of money and then transfer your deposited amount to another bank account. In this case, all the operations must be performed for the transaction to be valid. This means that if your amount is not deposited and there is not enough money in your bank account, then there will be an error in transferring the funds. For this reason, we use transactions so that either all the operations are performed or none of them are.

Creating a Simple Transaction

Since we now have the basic idea of what a transaction is, let’s take a look at how we can use transactions using ADO.NET 2.0. We are going to implement the AddUser method, which will add a new user to the database, and if the insertion operation is successful, then we will assign the role to the newly added user and add him in the role database. Below you can see a screen shot of the database diagram.

 

As you can see, the above database is pretty simple and only consists of two tables, Users and UserRoles. Now let’s see the code that inserts data into the tables.

Listing 1 – AddUser() Method

public static bool AddUser(string firstName,string lastName)
{
  bool result = false;
 
  SqlConnection myConnection = newSqlConnection(ConnectionString);
  SqlTransaction myTransaction = null;
 
  SqlCommand myCommand = newSqlCommand(SP_INSERT_USER, myConnection);
  myCommand.CommandType =CommandType.StoredProcedure;
 
 myCommand.Parameters.AddWithValue("@FirstName", firstName);
 myCommand.Parameters.AddWithValue("@LastName", lastName);
  myCommand.Parameters.Add("@ReturnValue",SqlDbType.Int, 4);
 myCommand.Parameters["@ReturnValue"].Direction =
    ParameterDirection.ReturnValue;
 
  try
  {
    myConnection.Open();
    myTransaction =myConnection.BeginTransaction();
    myCommand.Transaction = myTransaction;
 
    myCommand.ExecuteNonQuery();
    int returnValue =(int)myCommand.Parameters["@ReturnValue"].Value;
 
    if (returnValue <= 0)
      throw newArgumentOutOfRangeException("Value not inserted.");
 
    myCommand.Parameters.Clear();
    myCommand.CommandText = SP_INSERT_USER_ROLE;
    myCommand.CommandType =CommandType.StoredProcedure;
   myCommand.Parameters.AddWithValue("@UserID", returnValue);
    myCommand.ExecuteNonQuery();
 
    result = true;
 
  }
  catch (Exception ex)
  {
    string exception = ex.Message;
    myTransaction.Rollback();
  }
 
  finally
  {
    myTransaction.Commit();
    myConnection.Close();
    myCommand.Dispose();
  }
 
  return result;
 
}

Analysis

Let’s see what is going on inside the AddUser() method. As you can see, the method takes two parameters, which are FirstName and LastName. I have created the SqlConnection object, SqlCommand object, and the SqlTransaction object. Later, I attached the parameters to the command object. The important part is when the connection opens and we begin our transaction. Take a look at the code below.

Listing 2 – Starting the Transaction

myConnection.Open();
myTransaction = myConnection.BeginTransaction();
myCommand.Transaction = myTransaction;

In the above code, I have first opened the database connection and then started the transaction using the BeginTransaction method of the connection object. After starting the transaction, I assigned the transaction to the command object. If the insertion in the Users table is successful, it will return the UserID of the newly inserted user. If that UserID is greater than 0, it means that the new user has been created successfully and we can proceed to our next task, which is to insert the role of that user into the UserRoles table. Check out the following code, which attaches the returnValue (returnValue contains the UserID) to the SqlCommand object.

Listing 3 – Inserting User Role in the UserRoles Table

myCommand.Parameters.Clear();
myCommand.CommandText = SP_INSERT_USER_ROLE;
myCommand.CommandType =CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@UserID",returnValue);
myCommand.ExecuteNonQuery();

As you can see in the code above, first I clear the old parameters from the SqlCommand object, and then attach the new parameters.

If any exception is thrown, then we do a rollback using the myTransaction.Rollback(); method; else we commit the transaction using myTransaction.Commit();. This will ensure that the transaction is only committed when there is no exception thrown.

So, you see in the above example that we used Transactions, which I am sure you found simple. Now let’s take a look at some other stuff that comes along with Transactions.

Transaction Isolation Levels

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.

Save Points in Transactions

Whenever you rollback a transaction, it rolls back all the operations that you have performed before. Sometimes you need to undo the transaction to a certain point. For this scenario, you can use the Save Points functionality. Save Points allows you to rollback a transaction to a certain point. Let’s take a look at code below, which uses a Save Point.

Listing 6 – Using Save Points in Transaction

public static void AddUserSavePointTransaction()
{
  SqlConnection myConnection = newSqlConnection(ConnectionString);
  SqlCommand myCommand = new SqlCommand();
  myCommand.CommandText = @
    "INSERT INTOUsers(FirstName,LastName)VALUES('John', 'Doe')
    ";myCommand.Connection = myConnection;
 
  SqlTransaction myTransaction = null;
 
  try
  {
    myConnection.Open();
    myTransaction =myConnection.BeginTransaction();
    myCommand.Transaction = myTransaction;
 
    myCommand.ExecuteNonQuery();
 
    myTransaction.Save("firstInsertUser");
 
    myCommand.CommandText = @
      "INSERT INTOUsers(FirstName,LastName)VALUES('Azam', 'Sharp')";
 
    myCommand.ExecuteNonQuery();
   myTransaction.Rollback("firstInsertUser");
  }
  catch (Exception ex)
  {
    string exception = ex.Message;
    myTransaction.Rollback();
  }
 
  finally
  {
    myTransaction.Commit();
    myConnection.Close();
 
  }
}

Analysis

As you can see in the code above, I am using the command object to insert a new user. After executing the command object for the first time, I create a save point and insert another user. Immediately after inserting the second user, I rollback the transaction to the save point. Since the save point is declared after first user is inserted, only the insertion of first user will succeed, and the insertion of the second user will be discarded.

[Download Sample]

Conclusion

In this article, I demonstrated how you can make use of the SqlTransaction class to support transactions in your applications. There are many different options and features that cannot be covered in one article; for this reason, I recommend that you check out www.msdn.microsoft.com for more details about the SqlTransaction class. 



©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-04-13 12:49:47 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search