Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

Using ADO.Net Transaction in Visual Basic.Net

Author: Faraz

In this article we will look at the steps that are involved with creating a database transaction. Here I will describe you with the help of sample code. Read on to learn more!

Updating a database can be problematic for many reasons, means; problem arises if a user attempts to insert the invalid data into a field, or into a database that is not presently online etc. These problems are not the type of update conflicts but are the types of general errors.

 

Transaction is the key to handle such kind of problems. A transaction is a sequence of tasks in which, if any one of the individual tasks fails, the whole sequence fails and the state of the system is returned to the its state before the transaction began. The transaction can only succeed if every individual task is succeeds, in which case the transaction is committed.

 

This concept is very important for database application. Imagine the situation where the user changes hundreds of records in his/her local DataSet and then hit the Save button. Now imagine the update process crashes halfway through. The user might be aware of the error, but might not exactly know how many records were saved before the error occurred. This nasty situation can be avoided by making the update process into a transaction.  If an error now occurs midway through updating the records, then the whole process is classed as failed and the updates that have been saved are undone or roll back, that is, the updated records are returned to their original values before the transaction began. The user can be notified that the update failed, and can be safe in a knowledge that the database is exactly as it was before the transaction began. The user can be notified that the update failed, and can be safe in the knowledge that the database is exactly as it was before the transaction began.

 

Use the following steps and see how this works:

 

  1. Place the make transaction button on the windows form and add the following code in the Button’s Click event.

    Private Sub cmdTrans_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdTrans.Click

  

        do_Transaction(strConn)

 

 End Sub

 

  1. Now add the do_Transaction()  procedure to the form:

    Sub do_Transaction(ByVal strConn As String)

        Dim strQuery As String

        Dim SQLConn As New SqlClient.SqlConnection(strConn)

        Dim SQLCmd As New SqlClient.SqlCommand(strQuery, SQLConn)

        SQLCmd.Connection.Open()

        Dim doTrans As SqlClient.SqlTransaction = SQLConn.BeginTransaction()

 

        Try

            strQuery = "INSERT INTO [Order] (CustomerID, OrderDate, OrderAmount) VALUES(1, '10/02/06', 1000)"

            SQLCmd.CommandText = strQuery

            SQLCmd.Transaction = doTrans

            SQLCmd.ExecuteNonQuery()

 

            strQuery = "INSERT INTO [Order] (CustomerID, OrderDate, OrderAmount) VALUES(2, '10/12/05', 2000)"

            SQLCmd.CommandText = strQuery

            SQLCmd.Transaction = doTrans

            SQLCmd.ExecuteNonQuery()

 

            doTrans.Commit()

 

        Catch exp As Exception

 

            doTrans.Rollback()

            MsgBox(exp.Message, MsgBoxStyle.OKCancel)

 

        Finally

            SQLConn.Close()

            SQLConn = Nothing

 

        End Try

    End Sub

  

  1. Run the code and test all kinds of scenarios, means successful scenarios as well as unsuccessful scenarios and check whether the error is reported on failure of scenario.
  2. Also verify that neither record was added to the database.

 

Inline error handling is used to roll back the changes if an error occurs (doTrans.Rollback()) if an error does not occur, then the changes are committed to the database with the Commit method of the transaction object (doTrans.Commit()). Either way, the Finally statement will close the connection to the database.   

 

Summary:

 

  • Create a local Transaction object and call the do_Transaction() method of the connection object.
  • Run the set of SQL Statements.
  • Call the Commit method of the Transaction object if everything succeeded, or call the Rollback method to cancel the transaction if error occurred. You place the Commit at the end of the function and the Rollback in error handler.
Article Comments
You have done a great job Faraz!

Your article is really fantastic. I really appreciate your work!

Umer Saleem
Lahore, Pakistan

Posted on 1/18/2007 7:40:38 AM by Umer Saleem

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< Exception Handling in .Net Framework

Disclaimer - Privacy
© 2002-2017 DevASP.net