How to use a datareader against an oracle storedure in Visual C # .NET
対 対 品
This Article Was previously distribLished 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
Requirements Create The Oracle Tables Create The Oracle Packages Create The Visual C # .NET Application Additional Information REFERENCES
Summarythis step-by-step article buy
DataReader Object to Retrieve Data from an Oracle Stored Procedure. You Can Use
DataReader to Retrieve A Read-Only, Forward-Only Stream of Data from A Database. Using To
DataReader CAN Increase Application Performance and Reduce System Overhead Because Only One Row Is Kept in Memory.
Back to the top
RequirementSthe 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 .Netthis 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 Sample Uses Tables That Are Defined in 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'); 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 Packagescreate 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 country;
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.
Back to the top
Additional InformationNotice 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
Referencesfor 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 DataReaderhttp:? //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 (); SCENTINTITY [i] .Misename = myReader.getValue (x ) .Tostring (); SCENTITY [I] .Misecode = myreader.getValue (x ). ToString (); SCENTING () ;s.MINCOUNT = MyReader.getValue (x ). ToString (); SCENTITY [i] .success = myReader. GetValue (x ). Tostring ();
} I ;
MyReader.Close ();
