vb.net oracle 更新数据库,VB.NET下连接Oracle数据库的范例

【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