Monday, July 21, 2008

Manually Setting Transaction in ASP.Net

Transactions ensures the integrity of the data by making sure that either all the database occur or none of them do as in updating an account balance and inserting a account history.

To manually set transaction, use BeginTransaction on the database connection to create the Transaction Object. However, it is important to note that when using this approach, the transaction only applies to the connection. If you have more than one database connection opened, the transaction only works within the connection where the transaction was started.

Also, all database command that is created from the connection that has started the transaction need to be assigned the transaction otherwise an error will be triggered. If you want to update multiple databases, it needs to be done using dblink (for Oracle) since the transaction is isolated to the single database connection.

The following is a sample code:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="transPage2.aspx.vb" Inherits="testTrans.transPage2"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<title>Transaction Page</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="" name="vs_targetSchema">
<form id="Form1" method="post" runat="server">
<asp:Label id="lblMsg" runat="server"></asp:Label>
<asp:Label id="lblErrMsg" runat="server" ForeColor="Red"></asp:Label>
<TABLE id="Table1" cellSpacing="1" cellPadding="1" width="300" border="1">
<TD style="WIDTH: 115px">Name</TD>
<asp:TextBox id="txtName" runat="server"></asp:TextBox></TD>
<TD style="WIDTH: 115px">Dept</TD>
<asp:TextBox id="txtDept" runat="server"></asp:TextBox></TD>
<TD style="WIDTH: 115px"></TD>
<asp:CheckBox id="cbFail" runat="server" Text="Fail Transaction"></asp:CheckBox></TD>
<TD style="WIDTH: 115px" vAlign="middle" height="40">
<asp:Button id="Button1" runat="server" Text="Submit"></asp:Button></TD>
<TD height="40"></TD>

Code behind file (the following code works in Framework 1.1 and 2.0):

' Transaction Page - uses manual transaction at the Oracle connection level
' Author Strovek
' Date Feb 16, 2006
' Revised

Imports System.Data.OracleClient
Imports TranLib

Public Class transPage2
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub
Protected WithEvents lblMsg As System.Web.UI.WebControls.Label
Protected WithEvents lblErrMsg As System.Web.UI.WebControls.Label
Protected WithEvents txtName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtDept As System.Web.UI.WebControls.TextBox
Protected WithEvents cbFail As System.Web.UI.WebControls.CheckBox
Protected WithEvents Button1 As System.Web.UI.WebControls.Button

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
End Sub

#End Region
Dim Conn As New _
Dim dbTrans As OracleTransaction

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click
lblMsg.Text = ""
lblErrMsg.Text = ""

dbTrans = Conn.BeginTransaction

lblMsg.Text += "<br>Connection Opened"

Dim dbCmd As OracleCommand = Conn.CreateCommand
dbCmd.Transaction = dbTrans
dbCmd.CommandText = "insert into id_lookup (name, deptid) values ('" _
& txtName.Text & "','" & txtDept.Text & "')"

If cbFail.Checked Then
lblMsg.Text += "<br>Transaction aborted"
lblMsg.Text += "<br>Transaction Completed"
End If

Catch ex As Exception
lblErrMsg.Text += "<br>Error occurred " & ex.Message
If Conn.State = ConnectionState.Open Then
End If
End Try

End Sub
End Class

As can be seen in the above sample, create an OracleTransaction Object using the BeginTransaction method in the connection. This object will need to be assigned to the properties of any subsequent OracleCommand created. Consequently, we will not be able to use the Tranlib library in the first sample without modification.

The same approach can be used with OleDB data provider by changing all the OracleClient object to the OleDB counterparts (OracleConnection with OleDBConnection, OracleCommand with OleDBCommand and OracleTransaction with OleDBTransaction).

The commit and rollback uses the commit and rollback method of the transaction object.

No additional components are needed.

Need to assign the transaction object to all objects derived from the database connection which has a transaction started.
Transaction isolated within one database connection.

No comments: