How To Use a DataReader Against an Oracle Stored Procedure in Visual C# .NET
来源:互联网 发布:淘宝天猫店怎么开 编辑:程序博客网 时间:2024/04/30 00:05
How To Use a DataReader Against an Oracle Stored Procedure in Visual C# .NET
対象製品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
- Visual C# .NET
- ADO.NET fundamentals and syntax
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
- Create a new Visual C# Windows Application project. Form1 is added to the project by default.
- Add the following code to the top of the Code window:
using System.Data.OleDb;
- 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();
- Modify the OleDbConnection string as appropriate for your environment.
- 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.
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();
- How To Use a DataReader Against an Oracle Stored Procedure in Visual C# .NET
- Creating a Stored Procedure or Function in an Oracle Database
- How to call stored procedure in Hibernate
- How to call stored procedure in Hibernate
- navicate how to create a stored procedure
- navicate how to create a stored procedure
- HOW TO: Set a Windows Hook in Visual C# .NET
- HOW TO: Set a Windows Hook in Visual C# .NET
- Sql Server - How to write a Stored procedure in Sql server
- Use Stored Procedure in JPA
- How to call an Oracle Stored Procedure that returns one or more REF CURSORS, using ADO from C++ How
- How To Use the ODBC .NET Managed Provider in Visual C# .NET and Connection Strings
- How to debug stored procedure/function in TOAD
- illustrates how to call a SQL Server stored procedure
- How to Use Procedure
- How to use composition and inheritance in visual c# ?
- How to authenticate against the Active Directory by using Forms authentication and Visual C# .NET
- How to create Function/Procedure in Oracle?
- 正则表达式
- [转载]Java面试中的陷阱
- ASP.NET 应用程序安全设计原则
- 软件人员的生涯规划
- HOW TO:使用 ADO.NET 和 Visual C# .NET 调用带参数的存储过程
- How To Use a DataReader Against an Oracle Stored Procedure in Visual C# .NET
- 00016.url of media security
- 所有老公必看的爱情教条
- 《RFC 2464 Transmission of IPv6 Packets over Ethernet Networks》学习小结
- 留神MSN和QQ好友发送来的文件!!!
- .NET重要技术思考——原文在《程序员》杂志第六期
- 如何在WINDOWS 2000将ORALCE完全卸载
- 如何安装VS.NET2003中文版自带的QuickStart
- Hibernate和Spring对DAO处理的实例