代码:
Public Function GetData(ByVal procDate As String) As ArrayListTry
Dim conn As New Oracle.DataAccess.Client.OracleConnection
Dim cmd As New Oracle.DataAccess.Client.OracleCommand
conn.ConnectionString = constring
cmd.Connection = conn
mandType = CommandType.StoredProcedure
mandText = "xxxxxxx.xxxxx"
conn.Open()
Dim para1 As New Oracle.DataAccess.Client.OracleParameter
Dim para2 As New Oracle.DataAccess.Client.OracleParameter
Dim rdr As Oracle.DataAccess.Client.OracleDataReader
With cmd
.Parameters.Clear()
.Parameters.Add("procDate", Oracle.DataAccess.Client.OracleDbType.Varchar2) _
.Value = procDate
.Parameters.Add("objCur", Oracle.DataAccess.Client.OracleDbType.RefCursor) _
.Direction = ParameterDirection.Output
End With
Dim ds As DataSet = New DataSet
Dim oraDa As Oracle.DataAccess.Client.OracleDataAdapter = _
New Oracle.DataAccess.Client.OracleDataAdapter(cmd)
Dim dt As DataTable
oraDa.Fill(ds)
dt = ds.Tables(0)
Dim dtList As New ArrayList
If Not dt Is Nothing AndAlso dt.Rows.Count > 0 Then
Dim iCount As Integer
For iCount = 0 To dt.Rows.Count - 1
dtList.Add(CStr(dt.Rows(iCount).Item(0)) & " " & CStr(dt.Rows(iCount).Item(1)))
Next
End If
Return dtList
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Function
需要注意的是,用到的CONNECTION和COMMAND对象的声明,现在用的是
Dim conn As New Oracle.DataAccess.Client.OracleConnection
Dim cmd As New Oracle.DataAccess.Client.OracleCommand
项目中一直用的是:
Dim cmd As IDbCommand = Nothing
Dim con As IDbConnection这样给存储过程传入的参数中,没有RefCursor类型,最后还是搞定了,呵呵。
可惜领导认为这样和其他的功能在DB访问上不太一样,最后还是没有使用,
不管怎样算是了解了一点中调用存储过程的方法。