VB.NET & System.Data.OracleClient - Transaction()

Author: admin | Posted: 13-06-2010 | Category: VB.NET & System.Data.OracleClient

VB.NET & System.Data.OracleClient - Transaction() - How to learn Connector/NET ADO.NET component System.Data.OracleClient namespace is the .NET Framework Data Provider for Oracle data source, Using the OracleTransaction class for makes sure that changes that were made to the store are treated as a group that can be committed or rolled back (Oracle Server Database)

ShotDev Focus:
- VB.NET & System.Data.OracleClient - Transaction()

Example

Transaction.aspx

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OracleClient"%>
<%@ Page Language="VB" %>
<script runat="server">

Sub Page_Load(sender As Object, e As EventArgs)
SampleTransaction()
End Sub

Sub SampleTransaction()

Dim objConn As System.Data.OracleClient.OracleConnection
Dim objCmd As System.Data.OracleClient.OracleCommand
Dim strConnString,strSQL As String
Dim Trans As OracleTransaction

strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;"
objConn = New System.Data.OracleClient.OracleConnection(strConnString)
objConn.Open()

'*** Start Transaction ***'
Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted)

Try

'*** Query 1 ***'
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('C005','Weerachai Nukitram','[email protected]','TH','2000000','1000000')"
objCmd = New System.Data.OracleClient.OracleCommand()
With objCmd
.Connection = objConn
.Transaction = Trans
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()

'*** Query 2 ***'
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('C005','Weerachai Nukitram','[email protected]','TH','2000000','1000000')"
objCmd = New System.Data.OracleClient.OracleCommand()
With objCmd
.Connection = objConn
.Transaction = Trans
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()

Trans.Commit()  '*** Commit Transaction ***'
Me.lblText.Text = "Record is commit"

Catch ex As Exception
Trans.Rollback() '*** RollBack Transaction ***'

Me.lblText.Text = "Record is rollback ("& ex.Message &")"
End Try

objCmd = Nothing
objConn.Close()
objConn = Nothing

End Sub

</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label id="lblText" runat="Server"></asp:Label>
</form>
</body>
</html>

Error Case
Record is rollback (ORA-0001: unique constraint (MYUSER.PK_CUSTOMERID) violated)

Screenshot

.
.
.
Download this script.