ericwong27
asked on
REF CURSOR support?
I have a problem on this when try to convert my database layer from System.Data.OracleClient to System.Data.Common. Anyone can help?
' Code under System.Data.OracleClient
C_Command.Parameters.Add(" result", OracleType.Cursor).Directi on = ParameterDirection.Output <--- Support ref cursor
' Code under System.Data.Common
param = _dataFactory.CreateParamet er()
param.ParameterName = "result"
param.DbType = <------------------------- --------- can't find a data type that support ref cursor
param.Direction = ParameterDirection.Output
C_Command.Parameters.Add(p aram)
This code is working under System.Data.OracleClient
Public Function GetData(ByVal para1 As String, ByVal para2 As Integer) As DataTable
Dim C_connection As OracleConnection = Nothing
'Establish Connection
C_connection = GetConnection()
Dim C_Command As OracleCommand
Dim C_DataAdapter As OracleDataAdapter
Dim cmdText As String
Try
' Command to Execute
cmdText = "MyProcedure.GetData"
C_Command = New OracleCommand(cmdText, C_connection)
C_Command.CommandType = System.Data.CommandType.St oredProced ure
' Input Parameters data type and value
C_Command.Parameters.Add(" para1", OracleType.VarChar, 32).Value = para1
C_Command.Parameters.Add(" para2", OracleType.Number).Value = para2
' Output Parameters data type and value
C_Command.Parameters.Add(" result", OracleType.Cursor).Directi on = ParameterDirection.Output
C_Command.Parameters.Add(" updateDate ", OracleType.DateTime).Direc tion = ParameterDirection.Output
' Fill Data
Dim dt As DataTable = New DataTable
C_DataAdapter = New OracleDataAdapter(C_Comman d)
C_DataAdapter.Fill(dt)
Update_Date = CType(C_Command.Parameters ("updateDa te").Value , Date)
Return dt
Catch ex As Exception
End Try
End Function
' Code under System.Data.OracleClient
C_Command.Parameters.Add("
' Code under System.Data.Common
param = _dataFactory.CreateParamet
param.ParameterName = "result"
param.DbType = <-------------------------
param.Direction = ParameterDirection.Output
C_Command.Parameters.Add(p
This code is working under System.Data.OracleClient
Public Function GetData(ByVal para1 As String, ByVal para2 As Integer) As DataTable
Dim C_connection As OracleConnection = Nothing
'Establish Connection
C_connection = GetConnection()
Dim C_Command As OracleCommand
Dim C_DataAdapter As OracleDataAdapter
Dim cmdText As String
Try
' Command to Execute
cmdText = "MyProcedure.GetData"
C_Command = New OracleCommand(cmdText, C_connection)
C_Command.CommandType = System.Data.CommandType.St
' Input Parameters data type and value
C_Command.Parameters.Add("
C_Command.Parameters.Add("
' Output Parameters data type and value
C_Command.Parameters.Add("
C_Command.Parameters.Add("
' Fill Data
Dim dt As DataTable = New DataTable
C_DataAdapter = New OracleDataAdapter(C_Comman
C_DataAdapter.Fill(dt)
Update_Date = CType(C_Command.Parameters
Return dt
Catch ex As Exception
End Try
End Function
ASKER
Thank for the comment. Actually that no issue using System.Data.OracleClient too.
But I wish that I can writing a Provider-Independent Data Access Code with ADO.NET 2.0. Unfortunately, I'm stuck in ref cursor.
But I wish that I can writing a Provider-Independent Data Access Code with ADO.NET 2.0. Unfortunately, I'm stuck in ref cursor.
Understood ;) You are saying that there isn't a generic RefCursor type. Instead of using DbType, you could define your own enumerations, using the native values as a basis, but allowing you to extend the values to include RefCursor.
Bob
Bob
ASKER
It seem that a good ideal. Any sample code for this :-)
' 2003 definitions
Public Enum DbTypeEx
AnsiString = DbType.AnsiString
AnsiStringFixedLength = DbType.AnsiStringFixedLeng th
[Binary] = DbType.Binary
[Boolean] = DbType.Boolean
[Byte] = DbType.Byte
Currency = DbType.Currency
[Date] = DbType.Date
DateTime = DbType.DateTime
[Decimal] = DbType.Decimal
[Double] = DbType.Double
Guid = DbType.Guid
Int16 = DbType.Int16
Int32 = DbType.Int32
Int64 = DbType.Int64
[Object] = DbType.Object
SByte = DbType.SByte
[Single] = DbType.Single
[String] = DbType.String
StringFixedLength = DbType.StringFixedLength
Time = DbType.Time
UInt16 = DbType.UInt16
UInt32 = DbType.UInt32
UInt64 = DbType.UInt64
VarNumeric = DbType.VarNumeric
' DbType.StringFixedLength is the last value at 23.
RefCursor = DbType.StringFixedLength + 1
End Enum
Bob
Public Enum DbTypeEx
AnsiString = DbType.AnsiString
AnsiStringFixedLength = DbType.AnsiStringFixedLeng
[Binary] = DbType.Binary
[Boolean] = DbType.Boolean
[Byte] = DbType.Byte
Currency = DbType.Currency
[Date] = DbType.Date
DateTime = DbType.DateTime
[Decimal] = DbType.Decimal
[Double] = DbType.Double
Guid = DbType.Guid
Int16 = DbType.Int16
Int32 = DbType.Int32
Int64 = DbType.Int64
[Object] = DbType.Object
SByte = DbType.SByte
[Single] = DbType.Single
[String] = DbType.String
StringFixedLength = DbType.StringFixedLength
Time = DbType.Time
UInt16 = DbType.UInt16
UInt32 = DbType.UInt32
UInt64 = DbType.UInt64
VarNumeric = DbType.VarNumeric
' DbType.StringFixedLength is the last value at 23.
RefCursor = DbType.StringFixedLength + 1
End Enum
Bob
ASKER
Thank, I will try it when back to office. :-)
ASKER
Hi, Bob
I not able to make it work. Any advice? Maybe only provider-specific classes like System.Data.OracleClient can be used for this scenario.
param = _dataFactory.CreateParamet
param.ParameterName = "result"
param.DbType = DbTypeEx.RefCursor <------------
param.Direction = ParameterDirection.Output
C_Command.Parameters.Add(p
Error Message
Value is not valid for DbType: 24.
Parameter name: dbType
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actually I'm already using factory pattern to do that, but the code is longer. I thought that I can use ADO.NET 2.0 to refactor my code, but too bad it not fully support Oracle.
Bob