[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


REF CURSOR support?

Posted on 2006-05-02
Medium Priority
Last Modified: 2007-12-19
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
Question by:ericwong27
  • 5
  • 4
LVL 96

Expert Comment

by:Bob Learned
ID: 16595296
Try the Oracle .NET Data Provider, it has support for RefCursor type.

LVL 10

Author Comment

ID: 16595492
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.
LVL 96

Expert Comment

by:Bob Learned
ID: 16595811
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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 10

Author Comment

ID: 16595852
It seem that a good ideal. Any sample code for this :-)
LVL 96

Expert Comment

by:Bob Learned
ID: 16595962
' 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

LVL 10

Author Comment

ID: 16595980
Thank, I will try it when back to office. :-)
LVL 10

Author Comment

ID: 16601905

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
LVL 96

Accepted Solution

Bob Learned earned 1000 total points
ID: 16604661
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.  


LVL 10

Author Comment

ID: 16605014
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.


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month18 days, 20 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question