Link to home
Start Free TrialLog in
Avatar of ericwong27
ericwong27Flag for Singapore

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).Direction = ParameterDirection.Output <--- Support ref cursor

' Code under System.Data.Common
param = _dataFactory.CreateParameter()
param.ParameterName = "result"
param.DbType = <----------------------------------  can't find a data type that support ref cursor
param.Direction = ParameterDirection.Output
C_Command.Parameters.Add(param)



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.StoredProcedure

            ' 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).Direction = ParameterDirection.Output
            C_Command.Parameters.Add("updateDate", OracleType.DateTime).Direction = ParameterDirection.Output

            ' Fill Data
            Dim dt As DataTable = New DataTable
            C_DataAdapter = New OracleDataAdapter(C_Command)
            C_DataAdapter.Fill(dt)

            Update_Date = CType(C_Command.Parameters("updateDate").Value, Date)


            Return dt

        Catch ex As Exception
           


        End Try

End Function
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Try the Oracle .NET Data Provider, it has support for RefCursor type.

Bob
Avatar of ericwong27

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.
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
It seem that a good ideal. Any sample code for this :-)
' 2003 definitions
Public Enum DbTypeEx
      AnsiString = DbType.AnsiString
      AnsiStringFixedLength = DbType.AnsiStringFixedLength
      [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
Thank, I will try it when back to office. :-)

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.CreateParameter()
param.ParameterName = "result"
param.DbType = DbTypeEx.RefCursor <------------
param.Direction = ParameterDirection.Output
C_Command.Parameters.Add(param)


Error Message

Value is not valid for DbType: 24.
Parameter name: dbType
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.