Tuesday, July 22, 2008

Automatic Oracle Transaction in ASP.Net

In the previous article I covered how to manually create transaction to ensure all the transaction are either committed or rolledback. In this article, I will share how to do this automatically:

Automatic Transaction
To use automatic transaction in ASP.Net, add the page directive Transaction="Required" .

<%@ Page Transaction="Required" %>

With the above directive, all database connection will automatically have transaction enabled. However, some additional preparations are needed for this to work.

If you are using OracleClient data provider, "Oracle Services for Microsoft Transaction Server" which comes with the Oracle client installation CD will need to be installed. Otherwise, you will encounter error - finding oramts.dll.

If you are using OleDB data provider with Oracle, XACT_E_TMNOTAVAILABLE(0x8004D01B). This can only be fixed by running the webform that has privilege to HKLM\SOFTWARE\Microsoft\MSDTC\MTxOCI and also full access to the directory where Oracle client is installed. However, this is not recommended since it may lead to security hole for hackers.

You can then control whether the transactions in your page is committed or rolled back using the following command:

System.EnterpriseServices.ContextUtil.SetAbort() to rollback.
System.EnterpriseServices.ContextUtil.SetComplete() to commit.

You could also use System.EnterpriseServices.ContextUtil.Autocomplete but only on classes that is derived System.EnterpriseServices.ServicedComponent.

The following is a sample using the above concept:

ASPx page:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="TransPage.aspx.vb" Inherits="testTrans.WebForm1" Transaction="Required"%>
<!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>

As can be seen above the page directive includes the statement Transaction="Required".

The code behind is as follows (the following codes work in Framework 1.1 and 2.0):

' Transaction page. Uses automatic transaction, uses OracleClient.
' Author Strovek
' Date Feb 15, 2006
' Revised

Imports System.Data.OracleClient
Imports TranLib
'Imports System.Data.OleDb

Public Class WebForm1
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 txtName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtDept As System.Web.UI.WebControls.TextBox
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents lblMsg As System.Web.UI.WebControls.Label
Protected WithEvents lblErrMsg As System.Web.UI.WebControls.Label
Protected WithEvents cbFail As System.Web.UI.WebControls.CheckBox

'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 Conn As New _

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 = ""

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

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

Dim tLib As New TranLib.TranLib

tLib.addRec(txtName.Text & "_1", 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

The translib code is as follows:

' Test library to see if the automatic transaction can work with an unsigned dll
' Author Strovek
' Date Feb 17, 2006
' Revised
' ======================================================================

Imports System.Data.OracleClient

Public Class TranLib
Dim Conn As OracleConnection

Public Sub setConn(ByRef dbConn As OracleConnection)
Conn = dbConn
End Sub

Public Sub addRec(ByRef pName As String, ByRef pDeptid As String)

Dim dbCmd As OracleCommand = Conn.CreateCommand
dbCmd.CommandText = "insert into id_lookup (name, deptid) values ('" _
& pName & "','" & pDeptid & "')"
Catch ex As Exception
Throw New Exception("[TranLib.addRec01]" & ex.Message)
End Try
End Sub

End Class

In the above example, a simple class is called that called tranLib. The class has two methods, one to set the database connection and another to perform an insert record. As shown in the above, the transaction is automatically performed.

Support transaction across multiple database connections.
Do not have to assign the transaction object to every database command created.

Requires additional components.
If using OleDb, require additional access rights.

No comments: