Return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle

Hello,

I am wondering if anyone in this community knows the shorter way to return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle, something like

return (IDataReader) SqlHelper.ExecuteReader(ConnectionString, "GET_ACCOUNT", Id);

instead of using the way below (look at the C# code)

Stored Procedure

 
create or replace PROCEDURE "GET_ACCOUNT"
(
	p_ID IN ACCOUNT.ID%type, retCursor OUT SA.MYGEN.sqlcur
)
IS
BEGIN
    OPEN retCursor FOR
	SELECT
		ID,
		NAME
	FROM ACCOUNT
	WHERE
		ID = p_ID
;
END GET_ACCOUNT;
 

Code (C#)

OracleConnection OraConn = new OracleConnection(_connectionString);
OraConn.Open();

OracleCommand OraCmd = new OracleCommand();
OraCmd.Connection = OraConn;
OraCmd.CommandText = "GET_ACCOUNT";
OraCmd.CommandType = CommandType.StoredProcedure;
OraCmd.Parameters.Add(new OracleParameter("retCursor", OracleType.Cursor)).Direction = ParameterDirection.Output;
OraCmd.Parameters.Add("p_ID", OracleType.Number, 10).Value = Id;

OraCmd.ExecuteNonQuery();
return (IDataReader)OraCmd.Parameters["retCursor"].Value;

Thank you.

cheers,

Eric

0
erickywong
7/11/2007 11:21:30 AM
asp.net.oracle 2269 articles. 0 followers. Follow

4 Replies
4704 Views

Similar Articles

[PageSpeed] 22

A while back I had to do a project using Oracle and VB... it was fun, because I am used to MS SQL and C#. Anyhow, to make my life easier, I wrote this little datasource to work with Oracle "Get" type stored procedures:

 

Imports System
Imports System.Data
Imports System.Data.OracleClient
Imports System.ComponentModel

    < _
    ToolboxData("<{0}:OracleDataSource runat=server></{0}:OracleDataSource>") _
    > _
    Public Class OracleDataSource
        Inherits SqlDataSource

        Private _AddOutputCursor As Boolean = True
        Private _SelectCursorName As String = "p_Cursor"
        Private _SchemaName As String = ""

        < _
        Category("Data"), _
        Browsable(True), _
        Bindable(False), _
        DefaultValue(GetType(Boolean), "False"), _
        Description("Add a ref cursor output parameter to Select Stored Procedure Commands"), _
        PersistenceMode(PersistenceMode.Attribute) _
        > _
        Public Property AddOutputCursor() As Boolean
            Get
                Return _AddOutputCursor
            End Get
            Set(ByVal value As Boolean)
                _AddOutputCursor = value
            End Set
        End Property

        < _
        Category("Data"), _
        Browsable(True), _
        Bindable(False), _
        DefaultValue("p_Cursor"), _
        Description("Cursor Parameter Name for Select Stored Procedure"), _
        PersistenceMode(PersistenceMode.Attribute) _
        > _
        Public Property SelectCursorName() As String
            Get
                Return _SelectCursorName
            End Get
            Set(ByVal value As String)
                _SelectCursorName = value
            End Set
        End Property

        < _
        Category("Data"), _
        Browsable(True), _
        Bindable(False), _
        DefaultValue(""), _
        Description("Default schema to use for database access"), _
        PersistenceMode(PersistenceMode.Attribute) _
        > _
        Public Property SchemaName() As String
            Get
                Return _SchemaName
            End Get
            Set(ByVal value As String)
                _SchemaName = value
            End Set
        End Property

        Protected Overrides Sub OnInit(ByVal e As System.EventArgs)
            MyBase.OnInit(e)
            Dim view As SqlDataSourceView
            view = DirectCast(GetView("DefaultView"), SqlDataSourceView)
            AddHandler view.Selecting, AddressOf OnSelecting
            AddHandler view.Inserting, AddressOf OnCommand
            AddHandler view.Updating, AddressOf OnCommand
            AddHandler view.Deleting, AddressOf OnCommand
        End Sub

        Protected Sub OnSelecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
            If (e.Command.CommandType = CommandType.StoredProcedure) Then
                If SchemaName <> String.Empty Then
                    e.Command.CommandText = SchemaName + "." + e.Command.CommandText
                End If
                If AddOutputCursor Then
                    Dim dbParam As New OracleParameter()
                    dbParam.OracleType = OracleType.Cursor
                    dbParam.Direction = ParameterDirection.Output
                    dbParam.ParameterName = _SelectCursorName
                    e.Command.Parameters.Add(dbParam)
                End If
            End If
        End Sub

        Protected Sub OnCommand(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
            If (e.Command.CommandType = CommandType.StoredProcedure) Then
                If SchemaName <> String.Empty Then
                    e.Command.CommandText = SchemaName + "." + e.Command.CommandText
                End If
            End If
        End Sub

    End Class

 

I was able to directly substitute this for the SqlDataSource for all my controls. All the "Get" type stored procedures were written like "open p_cursor for ...", and that worked fine, no need to declare a bunch of cursor types.

0
valenumr
7/11/2007 12:04:17 PM

Hi,

 You can use like this:

OracleDataReader dr;

dr=OraCmd.Parameters["retCursor"].Value;

Return this dr;

Hope this helps

0
kalkumar
7/11/2007 2:57:09 PM

If you are using ODP.net ( oracle's managed provider for oracle ) rather then ado.net ( microsoft's managed provider for oracle) you have to do it the way you are doing it.

If you are using ado.net then you can do a ExecuteReader. You have to pass in the RefCursor as a parameter either way. 

Here is an msdn artical that shows ExecuteReader with RefCursors.

http://msdn2.microsoft.com/en-us/library/hsd6yxkc(VS.80).aspx

I believe if you use enterprise library it will dynamically determine the refcursor parameters and you do not have to write code to add them.

This thread talks about it

http://forums.asp.net/t/883771.aspx

I think the down side is that it can only return one ref cursor at a time and you have to call it cur_out

0
carlsonad
7/13/2007 10:34:50 PM

Hi guys,

Thank you very much for all the inputs, I appreciate it very much. I will look into all your suggestions.

Thanks!!! Wink

 

cheers,

~eric~

0
erickywong
7/16/2007 3:58:33 AM
Reply:

Similar Artilces:

urgent: .NET Provider for Oracle or Oracle Data Provider for .NET
If I want to access Oracle DB in ASP.NET application, and I am using .NET Provider for Oracle or Oracle Data Provider for .NET, should I have to install OracleClient on my .NET server? Is there other choice? Thank you very much.Johnson Johnson2007:should I have to install OracleClient on my .NET server?  You have to options1. install the ODP.NET on your web serverOR2. put the oracle assemblies in your BIN directory   Hi jimmy,Thank you for you reply. Because I could not find a server right now, I should test it first before I agree or disagree with you, as most articles I...

which is shou i use? Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET
Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NETI am confused?Which is better or which one should I use?BTW, I am going to use .net 2.0 with oracle 9. Can anyone tell me what shou I use for working with oracle database?Thanks very much in advance!Justin Oracle's data provider has more functionality than Microsoft's.If your doing transaction processing you can have SAVEPOINTs, which Microsoft's data provider does not provide.Also, Oracle has PL/SQL Associative Arrays which are very powerful.Oracle also allows you to bind parameters by name and pos...

.NET Oracle Provider
Hi all,I am using .NET 2.0 Frameworks Oracle provider and it does not have a OracleDbTypes Type for me to use. I would like to create a parameter that is of the Oracle type NVARCHAR, but it seems I can only use DbTypes, which does not have this. If I used DbTypes.String, would this be ok, otherwise, how else can I be more specific about the Oracle Parameter data type?Thanks  can u explain which type of data u r going to storeThank uBaba Thank uBabaPlease remember to click "Mark as Answer" on this post if it helped you. What's your version of ODP? You have OracleD...

Choosing Oracle Client Version with Microsoft Managed Oracle Provider For .net
We need to change the Oracle Client that the System.Data.OracleClient uses to connect to our database on a server that has multiple versions of the Oracle Client. A different application on the server that we do not have control over is requiring the default ORACLE_HOME directory to remain an older Oracle 9i version of the client. Because of this, our applications using the Managed Oracle Provider for .net are using the 9i client to connect. We have a planned Oracle upgrade for our Database that will require us to use the new 10g version of the client that is also installed on the server, bu...

Which .Net Provider for Oracle 9i Microsoft or Oracle
Can anyone provide me pros and cons of provider to access Oracle Database ? I am aware of Microsoft Provider and Oracle Provider. Microsoft http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/manprooracperf.asp Oracle http://otn.oracle.com/software/tech/windows/odpnet/utilsoft.html I guess you can use either. But as some post suggests, if both installed, then MS ODP will not work. I've been using DataDirect Technologies Connect for .Net and Oracle. It's not free, so it depends on your budget. The speed improvements were great for us, and it's ni...

Problems retrieving REF Cursor with .Net Oracle Provider (System.Data.OracleClient)
Hi , I created a package which recieves 3 parameters. One of them, is a type that I created using ref cursor, likewise the example provided in the .Net Oracle Client Documentation and inside msdn site (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp the part which talks about Ref Cursor). I´ve already tested the package, and it´s working properly. But when I tried to run the ExecuteReader method of my Oracle Command I got the error "PLS-00306: wrong number or types of arguments in call to 'PAGINAR'". Her...

Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...

How call a Oracle Function using Microsoft .Net Oracle Client (System.Data.OracleClient)?
I try to call Oracle Function in .net C# by using System.Data.OracleClient. But i am not successful. Here is my code FUNCTION TTHAO_MONTT_EXISTS ( vten varchar2) RETURN boolean IS ret boolean; montt_id integer; BEGIN ret:=false; montt_id:= -1; select monthethao_id into montt_id from TTHAO_MONTHETHAO where ten = vten; if montt_id!=-1 then ret:=true; end if; return ret; Exception when others then return false; END; I do not know, when I call function TTHAO_MONTT_E...

Oracle store procedure problem using Powerbuilder and Oracle 8.0.4.0 Net
Powerbuilder version 6.0.00 / 6.5 Build 444 Oracle Net 8.0.4.0 I found there is problem with Oracle store procedure having more than 6 in parameters. I got GPF error on Window 95 and Dr. Watson error on NT. Also, I found it works find in Oracle Net 8.0.3.0. Is this a bug? Is there any new patch for this if it is a bug. Any recommendation about What version Oracle Net or SQL Net should I used for Oracle server version 8.0.4.0 (I don't think I am using any Oracle 8 new features)? Thanks in advance, Sharon Li If you use PB 6.5, you can try the new Oracle 8 driver: ftp...

Oracle Spatial support in Devart's ADO.NET Data Provider for Oracle [Edit]
dotConnect for oracle v. 7.3 http://www.devart.com/dotconnect/oracle/ , ADO.NET Provider from Devart, includes support for Oracle Spatial in the projects, using Entity Framework. Devart Team constantly implements all the available Entity Framework innovations, and shortly after the introduction of DbGeometry and DbGeography classes in Entity Framework 5 has implemented support for Oracle Spatial in the Entity Framework provider for Oracle. Oracle MDSYS.SDO_GEOMETRY object type can now be mapped to DbGeometry and DbGeography classes. Now dotConnect for Oracle includes new versions of Devar...

Oracle ODP.net mulitple Oracle Client
To connect to the oracle database using ODP.net, the applicate must add the dll file called Oracle.DataAccess. There are a lot of version of Oracle.DataAccess. As I know, if the client use Oracle Client 10g Release 2. If the client use Oracle Client 10g Release 2, the application also have to use the Oracle.DataAccess the come from Oracle Client 10g Release 2.  My question is that there are any method to add more than one Oracle.DataAccess version in my product to support multiple OracleClient.   That should automatically happen for the most part.  Oracle's ODP.NET i...

Oracle managed provider for .Net 2.0
Does ADO.Net 2.0 comes with Oracle Managed Provider too? If it's so, which is better to use, Oracle Managed Provider developed by Microsoft or by Oracle? In ADO.Net 2.0 can I use data paging (bring only the ammount of data that will be shown on the actual page instead of bringing all the data every time we change the actual page)? How can I do this using Oracle 10g?Juliano NunesMCAD | MCP | Microsoft Student Partnerhttp://julianonetfox2.spaces.live.com (Does ADO.Net 2.0 comes with Oracle Managed Provider too? If it's so, which is better to use, Oracle Managed Provider developed by Microso...

Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi  My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number  i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file   Thank ssandhya   To send SMS, you need some third party SMS providers. if you consul...

OleDb.Net vs. ODBC.Net Connection to Oracle
I want to connect to an Oracle database, and I understand that I can use either the OleDB.Net data provider or the ODBC.Net data provider for my connection. Does it matter which one I use? What are the advantages and disadvantages of each? Would appreciate any input. ODBC is slower than OleDb. because ODBC has to talk to OleDbProvider then OleDbProvider will talk to Oracle Db. Instead if you are able to use OleDb straight to DB then it will be faster. If you only have THOSE choices, I would take OleDb over ODBC any day. If you are using Framework 1.1, you have the choice of using th...

Web resources about - Return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle - asp.net.oracle

Highest Voted 'entity-framework' Questions - Stack Overflow
Q&A for professional and enthusiast programmers

Microsoft Data Access Components - Wikipedia, the free encyclopedia
This article has an unclear citation style . The references used may be made clearer with a different or consistent style of citation , footnoting ...

E-Trade and eBay Join Ad Age Data Conference October 28-29
... and more. Be sure to reserve your spot today. We're offering a limited 10% discount this week only to those who use the discount code DATAREADER. ...

Windows 8 WriteableBitmap Pixel Arrays in C# and C++
August 5, 2012 Roscoe, N.Y. Sometimes I thiink of myself as a scientist who studies operating systems. Although these operating systems form ...

Latest Updates
Latest Updates - Free source code and tutorials for Software developers and Architects.; Updated: 15 Feb 2013

moredotnet
M ore d otnet .NET Interview Questions, ASP.NET, C#, VB.NET, XML, SQL Check Out http://www.dotnetUncle.com √ .NET NOTES OOPS in .NET .NET ...

The Weekly Source Code 51 - Asynchronous Database Access and LINQ to SQL Fun - Scott Hanselman
Scott Hanselman on Programming, User Experience, The Zen of Computers and Life in General

Should you use Entity Framework for .NET applications?
I have been working on a project which I thought would be simpler than it turned out to be – nothing new there, most software projects are like ...

MSDN Magazine December 2001
The Windows XP kernel includes a number of improvements over Windows 2000 that promote better scalability and overall performance. This article ...

A Map for the Microsoft Application Development Platform
... N-Tier Remote Data Security Silverlight SQL Azure SQL Server Streaming XML Features ADO.NET DataSet ADO.NET DataTable ADO.NET DataReader ...

Resources last updated: 1/11/2016 11:03:46 AM