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.
