VB.NET とオラクルの接続（com+ コンポーネント)-CSDN博客

本文链接：https://blog.csdn.net/gpz_dalian/article/details/1861476

'これは自分で作ったCOM+コンポーネントです。EnterpriseServicesのContextUtiを利用して、二つの更新操作は
'一緒に実行します。

Imports System.EnterpriseServices
Imports System.Data.SqlClient
Imports Oracle.DataAccess.Client
Imports System.Data.Common

<Assembly: ApplicationName("WorxTransactions")>
<Assembly: ApplicationAccessControl(True)>
<Transaction(TransactionOption.RequiresNew)> _
Public Class BankTransactions
Inherits ServicedComponent

    Public Sub transferMoney(ByVal amount As Decimal)
        Dim connstr As String = "User Id=appus; Password=app;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = bacdb)(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = TOCERA2)))"
        Dim connstr2 As String = "User Id=system; Password=manager;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DELL-716)(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = edfr)))"
        Try
            'お金足りるか足らないかチェック
            Withdraw(connstr, amount)
            Try
                '二つのACCOUNTの更新操作
                Deposit(connstr, connstr2, amount)
            Catch ex As Exception
                'deposit failed
                Throw New ApplicationException("Error transfering money,deposit failed.", ex)
            End Try
          Catch ex As Exception
            'withdraw failed
            Throw New ApplicationException("Error transfering money,withdrawl failed.", ex)

End Try
End Sub

Private Sub Withdraw(ByVal connectionString As String, ByVal TranAmount As Decimal)

        Dim currentValue As String
        Dim daBack As DataSet = New DataSet
        Dim conn As New OracleConnection
        Dim oraAdapter As OracleDataAdapter = Nothing
        Dim oraCommand As OracleCommand = conn.CreateCommand()
        conn.ConnectionString = connectionString
        oraCommand.Connection = DirectCast(conn, OracleConnection)

        Try
            conn.Open()
            oraCommand.CommandText = "SELECT AMOUNT FROM ACCOUNTS WHERE NAME='He'"
            oraAdapter = New OracleDataAdapter(oraCommand)
            oraAdapter.Fill(daBack)
            currentValue = daBack.Tables(0).Rows(0).Item(0).ToString

            If currentValue < TranAmount Then
                Throw New ArgumentException("Attempt to overdraft account")
            End If

        Catch ex As Exception
            Throw New DataException("Error withdrawing", ex)
        Finally
            If Not conn Is Nothing Then
                conn.Close()
            End If
        End Try

End Sub

    Private Sub Deposit(ByVal connectionString As String, ByVal connectionString2 As String, ByVal TranAmount As Decimal)
        Dim conn As New OracleConnection(connectionString)
        Dim conn2 As New OracleConnection(connectionString2)
        Dim command As OracleCommand
        Dim command2 As OracleCommand

conn.Open()
command = New OracleCommand("", conn)

conn2.Open()
command2 = New OracleCommand("", conn2)

        Try
            command.CommandText = "UPDATE ACCOUNTS SET AMOUNT = AMOUNT - " & TranAmount & " WHERE NAME ='He'"
            command.ExecuteNonQuery()

command2.CommandText = "UPDATE ACCOUNTS SET AMOUNT =" & TranAmount & " + AMOUNT WHERE NAME ='Me'"
command2.ExecuteNonQuery()

'二つの更新の操作は一緒にコミットする
ContextUtil.SetComplete()

        Catch ex As Exception
            ContextUtil.SetAbort()
            Throw New DataException("Error withdrawing", ex)
        Finally
            conn.Close()
            conn2.Close()
        End Try
    End Sub

End Class
===============下の部分はFORM1のコードです==================================

Imports Transactions

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim txn As New BankTransactions
        Try
            txn.transferMoney(CDec(Me.TransferField.Text))

            MessageBox.Show(String.Format("{0:C} transfered from {1} to {2}", _
            CDec(Me.TransferField.Text), "He", "Me"), _
            "Transfer Succeeded", MessageBoxButtons.OK, MessageBoxIcon.Information)

Catch ex As Exception
MessageBox.Show(ex.Message, "Transfer Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try
End Sub

End Class