【IT168 技术资源】

　　当需要从VB.NET下连接Oracle数据时，你可以参考如下代码，在实际过程中，可以灵活运用：

Option Strict Off
Option Explicit On
Imports System.ComponentModel
Imports System.Data.OracleClient
Imports System.Data.OleDb

Public Class clsOleDB

Private SQLConnect As OracleConnection
Private SQLComm As OracleCommand

Private component As component
Private FileNo As Integer
Private FileName As String
Private SQLConnectString As String

Public Sub New()
MyBase.New()
Call Initial_DB_Setting()
End Sub

Private Sub Initial_DB_Setting()
On Error GoTo _Error

Dim key As Microsoft.Win32.RegistryKey
key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("....\\.....\\.....")
Dim Hosts As String = CType(key.GetValue("Hosts"), String)
Dim DataSource As String = CType(key.GetValue("DataSource"), String)
Dim UserId As String = CType(key.GetValue("UserId"), String)
Dim Password As String = CType(key.GetValue("Password"), String)
Dim ErrorLog As String = CType(key.GetValue("ErrorLog"), String)
key.Close()

FileNo = FreeFile()
FileName = ErrorLog.Trim & Format(Now, "yyyyMMddhhmmss") & ".TXT"

SQLConnectString = "User ID=" & UserId.Trim & ";Password =" & Password.Trim & ";Data Source=" & DataSource.Trim

SQLConnect = New OracleConnection(SQLConnectString)
SQLComm = SQLConnect.CreateCommand
Exit Sub

_Error:
Me.WriteErrLog(Err.Description, Err.Source)
End Sub

Public Function SQL_ExecuteQuery(ByVal paraSQLComm As String, Optional ByRef ErrMsg As String = "", Optional ByVal paraTransBase As Boolean = False) As DataTable
On Error GoTo l_Error
Dim DataAdapter As New OracleDataAdapter
If ConnectionStatus() = ConnectionState.Broken Or ConnectionStatus() = ConnectionState.Closed Then
Call Open()
End If
If paraTransBase Then
DataAdapter.SelectCommand = New OracleCommand(paraSQLComm, SQLConnect, SQLComm.Transaction)
Else
DataAdapter.SelectCommand = New OracleCommand(paraSQLComm, SQLConnect)
End If
SQL_ExecuteQuery = New DataTable
SQL_ExecuteQuery.TableName = "ResultTb"
DataAdapter.Fill(SQL_ExecuteQuery)
Exit Function

l_Error:
ErrMsg = Err.Description
WriteErrLog(Err.Description, paraSQLComm)
End Function

Public Function SQL_ExecuteNonQuery(ByVal paraSQLComm As String, Optional ByVal paraBLOBValue() As Byte = Nothing) As String
On Error GoTo l_Error
SQLComm = New OracleCommand(paraSQLComm, SQLConnect, SQLComm.Transaction)
If Not paraBLOBValue Is Nothing Then
SQLComm.Parameters.Add("@para1", paraBLOBValue)
End If
SQL_ExecuteNonQuery = SQLComm.ExecuteNonQuery()
SQL_ExecuteNonQuery = ""
Exit Function

l_Error:
SQL_ExecuteNonQuery = Err.Description
WriteErrLog(Err.Description, paraSQLComm)
End Function

Public Sub Open()
On Error GoTo l_Error
SQLConnect.Open()
Exit Sub

l_Error:
WriteErrLog(Err.Description)
End Sub

Public Sub Close()
On Error Resume Next
SQLConnect.Close()
End Sub

Public Sub BeginTrans()
SQLComm.Transaction = SQLConnect.BeginTransaction
End Sub

Public Sub CommitTrans()
SQLComm.Transaction.Commit()

End Sub

Public Sub RollbackTrans()
SQLComm.Transaction.Rollback()

End Sub

Public Function ConnectionStatus() As System.Data.ConnectionState
ConnectionStatus = SQLConnect.State
End Function

Private Sub WriteErrLog(ByVal paraErrMsg As String, Optional ByVal paraErrSQL As String = "")
FileOpen(FileNo, FileName, OpenMode.Append, OpenAccess.Write)
PrintLine(FileNo, paraErrMsg & "->" & paraErrSQL)
FileClose(FileNo)
End Sub

End Class