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

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


            ' 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)

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

            Return dt

        Catch ex As Exception

        End Try

End Function
LVL 10
Who is Participating?
Bob LearnedCommented:
Yeah, maybe a different approach, otherwise you would have to extend the parameter class to use the extended enums.  I have seen generic data factories, that expose a generic interface, but the underlying class structure uses provider-specific classes to implement.

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

ericwong27Author Commented:
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.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Bob LearnedCommented:
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.

ericwong27Author Commented:
It seem that a good ideal. Any sample code for this :-)
Bob LearnedCommented:
' 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

ericwong27Author Commented:
Thank, I will try it when back to office. :-)
ericwong27Author Commented:

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

Error Message

Value is not valid for DbType: 24.
Parameter name: dbType
ericwong27Author Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.