vb.net連接Oracle数据库

Imports Oracle.DataAccess.Client '導入命名空間

Public Class Form1

Dim Sql As String

Public SqlTP As OracleConnection = New OracleConnection("Password= ""密碼"";Persist Security Info=True;User ID=帳號;Data Source=資料庫")
Public CmdTP As New OracleCommand
Public drTP As OracleDataReader

 

Public Sub OpenTP()'打开Oracle連線
If SqlTP.State = ConnectionState.Closed Then
SqlTP.Open()
End If

CmdTP.CommandText = Sql
CmdTP.CommandType = CommandType.Text
CmdTP.Connection = SqlTP
drTP = CmdTP.ExecuteReader()
End Sub

Public Sub CloseTP()'关闭Oracle連線
drTP.Close()
If SqlTP.State = ConnectionState.Open Then
SqlTP.Dispose()
End If
End Sub

 

Private Sub A1()

Dim i, n As Integer

Dim TP0 As String

Sql = ("SELECT COUNT(*) FROM SH01.OGA_FILE")

OpenTP()
drTP.Read()
n = drTP.Item(0)'筆數
CloseTP()

 

Sql = ("SELECT SH01.OGA_FILE.OGA02  FROM SH01.OGA_FILE")
OpenTP()

For i = 1 To n
drTP.Read()'下一筆

TP0 = drTP.Item(0)'內容

Next
CloseTP()

End Sub

 

End Class

 

安装oracle客戶端

这里点“下一步”

这里点“下一步”

选择“管理员”，再点“下一步”

记住这里的路径，下面要用到；再点“下一步”

这里点“下一步”继续

这里点“安装”继续

安装进行中……

选择“执行典型配置”再点“下一步”

这里点“下一步”继续

点击“完成”完成安装，出来后退出安装结束。

 

配置
 listener.ora

sqlnet.ora 

tnsnames.ora 

三個文件

 從安裝位置如D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN打開

listener.ora

內容如下

# listener.ora Network Configuration File: D:\oracle\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u2/oracle/10g)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

 

sqlnet.ora內容如下

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

NAMES.DIRECTORY_PATH= (TNSNAMES)

 

tnsnames.ora內容如下

# tnsnames.ora Network Configuration File: D:\oracle\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

TOPTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = toptest)
)
)

TOPSTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = topstd)
)
)

TOPPROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.9)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = topprod)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

 

运行CMD――>执行Tnsping 資料庫名稱  检查是否安装成功。