一直一来都没对数据连接问题做深入研究,只是写一些练习类型的示例。最近想真正开发些小的项目,便需要考虑真实环境中客户端与服务器的连接问题了。单位的服务器装的是Oracle10g,我的笔记本中装的是Oracle11g,本来没想到会有多大的麻烦,可是实际上麻烦非常之大。
根据微软的说法,在.net framework中会将弃用System.Data.OracleClient,建议采用第三方客户端。好在Orcale很地道,开发有专门针对.net framework的ODP.net。于是便根据网上找到的一些资料,使用ODP.net做数据连接的客户端。具体的处理方法为:
1、在项目中添加引用Oracle.DataAccess.dll:该文件在Oracle的安装目录中的..\ODP.NET\bin\2.x\中。
2、在app.config中分别写本地和服务器的连接字符串。在connectionStrings节点中增加add节点,其中的代码分别为: add name="OracleConnectionString0" connectionString="Data Source=xx.xxx.x.xxx/你的Oracle数据库名;User Id=你的用户名assword=你的用户密码;"
add name="OracleConnectionString1" connectionString="Data Source=127.0.0.1/你的Oracle数据库名;User Id=你的用户名assword=你的用户密码;"
3、在类文件中引用System.Data、Oracle.DataAccess.Client、Oracle.DataAccess.Types、System.Configuration。
4、在类文件中编写几个函数和子程序用于数据库处理即可。大体如下:
Imports System.Data Imports oracle.DataAccess.Client
Imports oracle.DataAccess.Types
Imports System.Configuration
Public Class ClassOracle
'Odp.net连接
Private Shared connstring As String '连接字符串
Public Sub New()
If String.IsNullOrEmpty(connstring) = True Then
Dim str1 As String = ConfigurationManager.ConnectionStrings("OracleConnectionString0").ConnectionString
Dim str2 As String = ConfigurationManager.ConnectionStrings("OracleConnectionString1").ConnectionString
Using conn As New oracleConnection(str1)
Try
conn.Open()
If conn.State = ConnectionState.Open Then
connstring = str1
Exit Sub
End If
Catch ex As Exception
End Try
End Using
Using conn As New oracleConnection(str2)
Try
conn.Open()
If conn.State = ConnectionState.Open Then
connstring = str2
End If
Catch ex As Exception
End Try
End Using
End If
End Sub
Public Function GetDataTable(ByVal ssql As String) As DataTable
'功能:返回数据表
Dim tb As New DataTable
If String.IsNullOrEmpty(connstring) = False Then
Using conn As New oracleConnection(connstring)
conn.Open()
Using cmd As New oracleCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = ssql
Using adp As New oracleDataAdapter(cmd)
Using ds As New DataSet
adp.Fill(ds)
tb = ds.Tables(0)
End Using
End Using
End Using
conn.Close()
End Using
Else
MessageBox.Show("连接服务器失败!")
End If
Return tb
End Function
Public Sub ExecuteNonQuery(ByVal ssql As String)
'功能:执行非Select命令
If String.IsNullOrEmpty(connstring) = False Then
Using conn As New oracleConnection(connstring)
conn.Open()
Using cmd As New oracleCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = ssql
cmd.ExecuteNonQuery()
End Using
conn.Close()
End Using
Else
MessageBox.Show("连接服务器失败!")
End If
End Sub
Public Function ExecuteScalar(ByVal ssql As String) As Object
'功能:返回单一值
Dim obj As Object = Nothing
If String.IsNullOrEmpty(connstring) = False Then
Using conn As New oracleConnection(connstring)
conn.Open()
Using cmd As New oracleCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = ssql
obj = cmd.ExecuteScalar()
End Using
conn.Close()
End Using
Else
MessageBox.Show("连接服务器失败!")
End If
Return obj
End Function
End Class
|