How To Use a DataReader Against an Oracle Stored Procedure in Visual C# .NET

原创 2004年10月11日 14:15:00

How To Use a DataReader Against an Oracle Stored Procedure in Visual C# .NET

対象製品
This article was previously published under Q309361
For a Microsoft Visual Basic .NET version of this article, see 308073.
For a Microsoft Visual C++ .NET version of this article, see 309362.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data.OleDb

IN THIS TASK

SUMMARY

This step-by-step article uses the DataReader object to retrieve data from an Oracle stored procedure. You can use the DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row is kept in memory.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
This article assumes that you are familiar with the following topics:
  • Visual C# .NET
  • ADO.NET fundamentals and syntax
back to the top

Create the Oracle Tables

This sample uses tables that are defined in the Oracle Scott/Tiger schema. The Oracle Scott/Tiger schema is included with the default Oracle installation.

If this schema does not exist, you must run the following table and insert scripts for the tables:
   CREATE TABLE DEPT
   (DEPTNO NUMBER(2,0) NOT NULL, 
   DNAME VARCHAR2(14) NULL, 
   LOC VARCHAR2(13) NULL,
   PRIMARY KEY (DEPTNO)
   );

   INSERT INTO Dept VALUES(11,'Sales','Texas');
   INSERT INTO Dept VALUES(22,'Accounting','Washington');
   INSERT INTO Dept VALUES(33,'Finance','Maine');

   CREATE TABLE EMP
   (EMPNO NUMBER(4,0) NOT NULL, 
   ENAME VARCHAR2(10) NULL, 
   JOB VARCHAR2(9) NULL, 
   MGR NUMBER(4,0) NULL, 
   SAL NUMBER(7,2) NULL, 
   COMM NUMBER(7,2) NULL, 
   DEPTNO NUMBER(2,0) NULL,
   FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),  
   PRIMARY KEY (EMPNO)
   );

   INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
   INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
   INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
				
back to the top

Create the Oracle Packages

Create the following Oracle package on the Oracle server:
CREATE OR REPLACE PACKAGE curspkg_join AS 
   	TYPE t_cursor IS REF CURSOR ; 
   	Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor); 
   END curspkg_join;
   / 
				
Create the following Oracle package body on the Oracle server:
   CREATE OR REPLACE PACKAGE BODY curspkg_join AS
   Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor) 
   IS 
   	v_cursor t_cursor; 
   BEGIN 
   	IF n_EMPNO <> 0 
   	THEN
   		OPEN v_cursor FOR 
   		SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
   			FROM EMP, DEPT 
   			WHERE EMP.DEPTNO = DEPT.DEPTNO 
   			AND EMP.EMPNO = n_EMPNO;

   	ELSE 
   		OPEN v_cursor FOR 
   		SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
   			FROM EMP, DEPT 
   			WHERE EMP.DEPTNO = DEPT.DEPTNO;

   	END IF;
   	io_cursor := v_cursor; 
   END open_join_cursor1; 
   END curspkg_join;
   / 
				
back to the top

Create the Visual C# .NET Application

  1. Create a new Visual C# Windows Application project. Form1 is added to the project by default.
  2. Add the following code to the top of the Code window:
    using System.Data.OleDb;
    					
  3. Add the following code to the Form_Load event of Form1:
    OleDbConnection Oraclecon = new OleDbConnection("Provider=MSDAORA.1;Password=tiger;"
                  + "User ID=scott;Data Source=OracleServer;Persist Security Info=True");
    Oraclecon.Open();
    OleDbCommand myCMD =  new OleDbCommand
    ("{call curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}", Oraclecon);
    myCMD.Parameters.Add("ID", OleDbType.Numeric, 4).Value = 0;
    OleDbDataReader myReader;
    myReader = myCMD.ExecuteReader();
    int x; 
    int count;
    count = 0;
    while (myReader.Read())
    				
    {	
    	for (x = 0; x <= myReader.FieldCount - 1; x++) 
    		Console.Write(myReader.GetValue(x) + " ");
    
          
    	Console.WriteLine();
    	count += 1;
    }
    
    
    MessageBox.Show(count + " Rows Returned.");
    myReader.Close();
    Oraclecon.Close();
    					
  4. Modify the OleDbConnection string as appropriate for your environment.
  5. Press the F5 key to compile and run the application. Notice that the data from the Oracle stored procedure appears in the Debug window, and a row count appears in a message box.
back to the top

Additional Information

Notice that the code loops through the DataReader:
while (myReader.Read())
				
This is because the DataReader reads only one line at a time.

back to the top

REFERENCES

For additional information%1, click the article number%2 below to view the article%2 in the Microsoft Knowledge Base:

176086 How To Retrieve Recordsets from Oracle Stored Procedures Using ADO

For more information about the DataReader, refer to the following topic in the Microsoft .NET Software Development Kit (SDK) documentation:

Retrieving Data Using the DataReader
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
   myCMD.CommandText  ="{call curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}";
   myCMD.Parameters.Add("ID", OleDbType.Numeric, 4).Value = 0;
   OleDbDataReader myReader;
   myReader = myCMD.ExecuteReader();
   int x;
   int i=0;

   while (myReader.Read())
    
   {
    for (x = 0; x <= myReader.FieldCount - 1; x++)
    {
     scEntity[i] =new GCEEDAT00SCIRAIEntity ();
     scEntity[i].MiseName =myReader.GetValue (x++).ToString ();
     scEntity[i].MiseCode =myReader.GetValue (x++).ToString ();
     scEntity[i].MinCount =myReader.GetValue (x++).ToString ();
     scEntity[i].Success =myReader.GetValue (x++).ToString ();

    }
    i++;
    
   }


   myReader.Close();

Stored Procedure存储过程基础知识详解

存储过程是做项目的必备技术,只要你面试找工作,数据库及存储过程也是必考的,下面一起来了解存储过程的简单基本技术知识: 一、存储过程基本概念? 存储过程就是作为可执行对象存放在数据库中的一个或多个S...
  • u013519551
  • u013519551
  • 2017年05月19日 15:04
  • 633

mysql常见错误汇总

以下主要是导入存储过程的时候遇到的问题:
  • wq7570875
  • wq7570875
  • 2014年05月22日 11:48
  • 1754

浅谈Sybase的备份与恢复及常见问题解决

      上一篇写了Sybase的安装,下面就讲一下备份与恢复.这两步也是必须会的.必须掌握的.    1.1 备份    首先说一下备份.    备份的命令是     dump database ...
  • cuiran
  • cuiran
  • 2010年08月03日 15:18
  • 4864

mysql 事件 存储过程 Can't create a PROCEDURE from within another stored routine

Can't create a PROCEDURE from within another stored routine. 出现了这个问题,说明你是在用某种工具在编写事件,那么在开头的时候,就不用再写...
  • hjm4702192
  • hjm4702192
  • 2013年01月25日 14:47
  • 7138

Use Stored Procedure in JPA

  • wuminlang
  • wuminlang
  • 2013年05月22日 17:28
  • 255

SQL存储过程(Stored Procedure)

SQL存储过程(Stored Procedure) 什么是存储过程? 存储过程(procedure)类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数,也可以返回结...
  • lhy2199
  • lhy2199
  • 2015年12月28日 10:37
  • 5940

Oracle Procedure 存储过程

一、块 块是pl/sql组成的基本单元,本篇讲述存储过程,鉴于2者有很高的相似性,且是其中一种块,所以在此做一个简单的块讲述...
  • bbliutao
  • bbliutao
  • 2012年08月06日 08:41
  • 14240

数据库之存储过程(stored procedure)简述

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。...
  • xunileida
  • xunileida
  • 2012年04月22日 12:00
  • 1329

.NET : 一定不要忘记关闭DataReader对象

 今天早上处理一个紧急的客户反应问题,这是去年开发的一套绩效考核系统。他们现在集团在推广使用,用户数多起来之后就发现某个页面响应速度非常慢,甚至会报告错误,无法完成操作。 经过检查,发现有一个...
  • chengs_
  • chengs_
  • 2014年10月13日 15:42
  • 1406

c#.net 文本编辑控件——使用 Visual C# .NET 打印 RichTextBox 控件的内容

原文连接: http://support.microsoft.com/default.aspx?scid=kb;zh-cn;812425
  • junherry
  • junherry
  • 2009年08月26日 17:57
  • 1085
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:How To Use a DataReader Against an Oracle Stored Procedure in Visual C# .NET
举报原因:
原因补充:

(最多只允许输入30个字)