VB.NET とオラクルの接続（一つ簡単なサンプル)

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

Public Class BankTransactions
   
    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)))"
        Try
            Withdraw(connstr, amount)
            Try
                Deposit(connstr, amount)
            Catch ex As Exception
                'deposit failed
                Throw New ApplicationException("Error transfering money,deposit failed.", ex)
            End Try
        Catch ex As Exception
            'deposit failed
            Throw New ApplicationException("Error transfering money,deposit 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 TranAmount As Decimal)
        Dim conn As New OracleConnection(connectionString)
        Dim command As OracleCommand
        Dim command2 As OracleCommand
        Dim transaction As OracleTransaction

        conn.Open()
        command = New OracleCommand("", conn)
        command2 = New OracleCommand("", conn)
        transaction = conn.BeginTransaction()

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

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

        Catch ex As Exception
            transaction.Rollback()
            Throw New DataException("Error withdrawing", ex)
        Finally
            conn.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