Transaction Object in ADO.NET
Synopsis
The word transact stands for the meaning of to conduct
or to do, thus the word transaction in the real-world scenario
means the conductance or the exchange of something. Normally in a transaction
one pays for something he is obtaining. A transaction is always complete and
cannot be incomplete. For example, you would have to pay the price for something
you buy at a store (the transaction is said to have occurred in this case),
otherwise you won't be able to buy it ( no transaction is said to take place
in this case ). Similarly in online transactions that occur through
the internet, the completeness of a transaction must be ensured. It means that
if you have passed on some information on the internet then it should only be
saved if it has been obtained completely. For purpose of ensuring and saving a
transaction if it is successful the ADO.NET model provides us with
a Transaction object.
Defining the Problem
Consider a situation in which some amount is being
transferred from one bank account to another and the process is taking place
online. Something goes wrong and the transaction is unable to complete itself.
Under this situation what would be the status of the two accounts? Would it be
that the amount has been completely transferred or not transferred or an
intermediate situation in which some has been transferred and the rest has
failed to do so?
Incase of an intermediate result, the situation would become
complex. The use of transactions in this type of situations ensures that
either the exchange taking place is completed or not completed and there is
never an intermediate result.
Defining the Solution
The Transaction Object
ADO.NET provides two objects for implementing transactions. The namespaces
and description for each of these two objects is as follows;
| Object Names |
Namespace |
Description |
| SqlTransaction |
System.Data.SqlClient |
For use with Sql Server. |
| OleDbTransaction |
System.Data.OleDb |
For use with an OLE DB provider. |
The transaction object provides the following methods;
| Method |
Description |
| BeginTransaction |
This method starts a new transaction. It requires an open
connection. The method utilizes the open connection and changes made to the
data then are tracked as a transaction, until the changes are committed or
rolled back. |
| Commit (Transaction) |
Saves all the changes that have been made, permanently to
the data store. The method is called incase of a successful transaction. |
| Rollback (Transaction) |
Returns the source to its previous state, by abandoning the
changes that were made. This method is normally called incase of an
unsuccessful transaction. |
Coding the Transaction
Let us now have a look at how the Transaction object is coded or how
it can be used. Please note that the given example
is only to give you an idea how the transaction object can be
implemented, it is not a practical one and would not work, if implemented as
it is.
<%@ Import
Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Page Language="VB" %>
<Script Language="VB" Runat="Server">
Private Sub Page_Load(Source As Object, Sender As EventArgs)
'Variable Declarations
Dim strConnectionString = "Server=alpha; Database=beta; User Id=sa;
Password=;"
'Object Declarations and
Initialization
'Connection Object
Dim objConn As New OledbConnection(strConnectionString)
'Transaction Object
Dim objTransact As OledbTransaction
'Command Object
Dim objCmd As OledbCommand
Try
'Open the connection
objConn.Open()
'Start a transaction
objTransact = objConn.BeginTransaction()
'Set the command object properties
objCmd.Connection = objConn
objCmd.CommandText = "Statement goes here..."
'Connect the
transaction object with the command object
objCmd.Transaction = objTransact
'Execute the Sql
Statement
objCmd.ExecuteNonQuery()
'Transaction has
completed successfully.
'Save changes to the data store.
objTransact.Commit()
Catch Err As Exception
'Incase if an error was raised, cancel the transaction.
'All changes are to be dropped as the transaction
has failed.
objTransact.Rollback()
'Display the
error
Trace.Write(Err.ToString())
Finally
'Close Connection object
objConn.Close()
End Try
End Sub
</Script> |
As the comments with each of the step have been provided, therefore I will
not be explaining the complete code over here.
Conclusion
In this article we saw what transaction objects are,
and how to implement the transaction objects. The transaction
objects provide us with another mean of updating the underlying data source, but
with more security as it is confirmed that the information being entered or
being updated has been received completely or not.
Remember that the transaction objects are not to be used
every where when implementing databases behind our web applications. A
transaction should be implemented only in such cases where critical information
is to be transferred, such as in the case of online shopping, online banking,
etc. where monetary transfers have to take place.
|