What is a Transaction?
Transaction is nothing but a group of operations either all of operations successfully executed or fail.

Why we need Transaction?
Transaction will provide the consistency and correct behavior with in the system.

For example, a company will need to pay the salaries for the employees at starting of the month. For that company Account manager will transfer the amount from the company bank account to the individual employees account.
Here there are two operations to be performed as a group
1) Amount deducted from the company account
2) That amount should be added to the individual employees account.

These two operations should be executed successfully or failed as group. Take for example, if first statement executed successfully i.e., salary amount deducted from company’s account but second statement not executed (salaries are not added to the individual account’s) due to the some crash in the system or some other reason, then the entire operation will be faulty. Transactions will avoid this. Operations in the same Transaction will make the changes to the database only after all statements in the same Transaction will execute successfully.

There are different types of Transactions like Database transactions, Transactions in ASP.NET.

There are several Commands in Database Transactions like BEGIN, COMMIT, and ROLLBACK. Normally we use these in stored procedures.

Here I am explaining about ADO.NET Transactions in Asp.Net. These Transactions will help you whenever you want to execute multiple SQL statements as group from the front end.

I take two tables, those are company_info and emp_info.

First table contains the whole company information like balance in the company bank account, each employee id … and emp_info contains the individual employee information like id, name of employee, salary of each employee..

At the end of the month company will pay the salaries for their employees, for that account will transfers the amount from company account to individual account.

Here is the sample code

 

         Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
                        Handles Me.Load
                Update()
            End Sub
            Sub Update()
                Dim sqlcom1 As SqlCommand, sqlcom2 As SqlCommand, strsql As String
                Dim emp_id As String, emp_salary As Decimal
                Dim con As New SqlConnection("uid=sa;pwd=dlog24;database=COMPANY")
                Dim i1, i2 As Integer
                'Transaction declaration
                Dim tr As SqlTransaction

                Try

                    sqlcom1 = New SqlCommand("update company_info set salary = " & _
                                emp_salary & " where emp_id= " & emp_id, con)
                    sqlcom2 = New SqlCommand("update employee_info set salary = " & _
                                emp_salary & " where emp_id= " & emp_id, con)

                    tr = con.BeginTransaction
                    con.Open()

                    sqlcom1.Transaction = tr
                    sqlcom2.Transaction = tr

                    i1 = sqlcom1.ExecuteNonQuery()
                    i2 = sqlcom2.ExecuteNonQuery()

                    If i1 = 1 And i2 = 1 Then
                        tr.Commit()
                    Else
                        tr.Rollback()
                    End If

                Catch ex As Exception
                    Response.Write(ex.Message)
                End Try