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:
ASPX:
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="transPage2.aspx.vb" Inherits="testTrans.transPage2"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<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="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:Label id="lblMsg" runat="server"></asp:Label>
<br>
<asp:Label id="lblErrMsg" runat="server" ForeColor="Red"></asp:Label>
<br>
<TABLE id="Table1" cellSpacing="1" cellPadding="1" width="300" border="1">
<TR>
<TD style="WIDTH: 115px">Name</TD>
<TD>
<asp:TextBox id="txtName" runat="server"></asp:TextBox></TD>
</TR>
<TR>
<TD style="WIDTH: 115px">Dept</TD>
<TD>
<asp:TextBox id="txtDept" runat="server"></asp:TextBox></TD>
</TR>
<TR>
<TD style="WIDTH: 115px"></TD>
<TD>
<asp:CheckBox id="cbFail" runat="server" Text="Fail Transaction"></asp:CheckBox></TD>
</TR>
<TR>
<TD style="WIDTH: 115px" vAlign="middle" height="40">
<asp:Button id="Button1" runat="server" Text="Submit"></asp:Button></TD>
<TD height="40"></TD>
</TR>
</TABLE>
</form>
</body>
</HTML>
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.
InitializeComponent()
End Sub
#End Region
Dim Conn As New _
OracleConnection(System.Configuration.ConfigurationSettings.AppSettings("OrclConn"))
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 = ""
Try
Conn.Open()
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 & "')"
dbCmd.ExecuteNonQuery()
If cbFail.Checked Then
dbTrans.Rollback()
lblMsg.Text += "<br>Transaction aborted"
Else
dbTrans.Commit()
lblMsg.Text += "<br>Transaction Completed"
End If
Catch ex As Exception
dbTrans.Rollback()
lblErrMsg.Text += "<br>Error occurred " & ex.Message
Finally
If Conn.State = ConnectionState.Open Then
Conn.Close()
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.
Pro
No additional components are needed.
Con
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:
Post a Comment