A Look into Transactions in ADO.NET 2.0
page 2 of 5
by Mohammad Azam
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 25594/ 83

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.


View Entire Article

User Comments

Title: feedback   
Name: programacion web
Date: 2010-11-22 11:07:36 AM
Comment:
excellent article, thanks
Title: feedback   
Name: suleman
Date: 2010-06-11 2:20:56 AM
Comment:
myTransaction.Commit();
is in finally block in listing 1 , that is wrong , it there is exception then transaction already rollback , and myTransaction.Commit() will also execute in finally block.

Can you Please verify.
Title: Where are the SP of this article   
Name: Rameez
Date: 2009-05-25 3:49:45 AM
Comment:
Hi,
Excellet work keep it up but where are the stored Procedure of this article let me know plz
Best Regards
Title: Very Nice Article   
Name: Krunal.Shaholia
Date: 2008-09-04 4:24:31 AM
Comment:
A very nice article .would like to see more interesting articles of this kind.
Title: easy understandable to any person   
Name: pralay mitra
Date: 2008-05-23 12:35:26 PM
Comment:
it' good. keep going
Title: keep it up   
Name: peddakapu
Date: 2007-12-04 3:02:24 AM
Comment:
Hi
Mohammad Azam......How r u? The way u r done is very nice.

keep it up ..young man..takecare...
Title: good explain   
Name: naidu
Date: 2007-12-04 2:59:24 AM
Comment:
Thanks...a lot....
Title: nice coding work   
Name: vijay
Date: 2007-12-03 5:13:36 AM
Comment:
the codeing work done by mr azam is very nice
Title: Very Nice   
Name: Irina
Date: 2007-10-30 12:52:12 PM
Comment:
Very clear and simple idea to demonstrate transaction in use. Can be easily modified to see results for other isolation level settings. Nice!
Title: Excellent   
Name: abc
Date: 2007-10-01 8:23:32 AM
Comment:
Excellent way of understanding the Transaction.
Title: Nice (Y)   
Name: Faraz Siddiqui
Date: 2007-09-26 1:38:55 PM
Comment:
Very easy way to understand the Transaction.
Title: SQL Transaction   
Name: Nitin Sharma(.Net Developer)
Date: 2007-07-02 6:33:23 AM
Comment:
The article is very very easy to understand...Keep it up..always...!!
Title: very good   
Name: Lalji Mer
Date: 2007-05-01 3:26:24 AM
Comment:
It is very nice to understand transaction process too.
Title: Very Good   
Name: Sivanthinathan
Date: 2007-03-16 4:42:52 AM
Comment:
It is very nice to understand transaction process.
Title: Very very good   
Name: Paul Gonzales
Date: 2007-01-19 1:01:02 PM
Comment:
My english isn't so good, but i understand all.. this article is excelent.
I'd like get more similar articles.
thanks
Lima-Peru
Title: good   
Name: nitin
Date: 2006-12-07 2:51:01 PM
Comment:
good work. keep it up
Title: GOOD Article   
Name: Rajesh Varma
Date: 2006-11-24 8:59:05 AM
Comment:
ITs Very good Articles...and well presented....
Title: gr8 job   
Name: Rajeev Kumar Shukla
Date: 2006-08-18 8:59:34 AM
Comment:
problem with finally block otherwise article is very good specialy regarding SavePoint in transaction.
Title: Knowledge shareable Article   
Name: Mahendra singh
Date: 2006-07-20 7:43:41 AM
Comment:
wow
that's great Article.
specially the way of explanation is Interesting
Title: Software Engineer   
Name: Premanshu Mukherji
Date: 2006-07-20 7:33:18 AM
Comment:
Very nice article. Dirty Read was well explained. Examples are self explanatory. Good Job.
Title: Best one   
Name: Sudhakar rao
Date: 2006-07-04 1:41:21 AM
Comment:
This article is best one ,i didnt c this type of article .
Thanks keep it up my friends
Title: Problem with IsolationLevel   
Name: Ctut
Date: 2006-04-28 5:42:54 PM
Comment:
The one that you demonstrated in the example above is not known as ReadOnCommited, it's known as a ReadCommitted.
Title: Problem with commit   
Name: Simon Stewart
Date: 2006-04-05 2:58:05 AM
Comment:
Please check your finally block.

You should rather call commit directly after you have done the DB operation.
In your case if an error occurs, your finally block will fail because the transaction is not in a usable state at that point.
Title: Programer Analyst   
Name: Srinivas
Date: 2006-03-23 6:51:56 AM
Comment:
Nice One for novice users
Title: Very Nice Work   
Name: Rupesh kumar Sinha
Date: 2006-03-22 12:52:12 AM
Comment:
Its a great work for them who wants to be updated in latest technology!! Keep it Up
Title: Thanks   
Name: Mohammad Azam
Date: 2006-03-21 7:59:41 PM
Comment:
Thank you very much everyone for your kind comments.
Title: Nicely Done!   
Name: strahman
Date: 2006-03-21 12:10:14 PM
Comment:
Very well written!! Excellent job, Azam!!
Title: Very Good   
Name: sree
Date: 2006-03-21 4:47:41 AM
Comment:
This Artcile is so Informative and Nice.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-06-19 1:51:54 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search