How To Fill a DataSet from an Oracle Stored Procedure by Using the OLE DB .NET Data Provider with Visual C# .NET (310101)

SUMMARY

This article demonstrates how to fill a DataSet object with the result set from an Oracle stored procedure. The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. The DataSet is a memory-resident representation of data that provides a consistent, relational programming model regardless of the data source. The DataSet represents a complete set of data, including related tables, constraints, and relationships among the tables.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Windows XP, Windows 2000, or Windows NT 4.0 Service Pack 6a
• Microsoft Data Access Components (MDAC) 2.6 or later
• Oracle 8.0 Server or later
• Microsoft Visual Studio .NET
• Oracle Client installed on the client computer

This article assumes that you are familiar with the following topics:

• Data definition language (DDL) queries in Oracle
• Visual C# .NET syntax
• ActiveX Data Objects (ADO)

back to the top

Steps to Create Package in Oracle Database

1. Use the following code to create a table named Person:

   CREATE TABLE person
             (ssn     NUMBER(9) PRIMARY KEY,
              fname   VARCHAR2(15),
              lname   VARCHAR2(20));
   
            INSERT INTO person VALUES(555662222,'Sam','Goodwin');
   
            INSERT INTO person VALUES(555882222,'Kent','Clark');
   
            INSERT INTO person VALUES(666223333,'Sally','Burnett');
   
            COMMIT;
   					

2. Use the following code to create the package header:

   CREATE OR REPLACE PACKAGE packperson
            AS
                TYPE tssn is TABLE of  NUMBER(10)
                INDEX BY BINARY_INTEGER;
                TYPE tfname is TABLE of VARCHAR2(15)
                INDEX BY BINARY_INTEGER;
                TYPE tlname is TABLE of VARCHAR2(20)
                INDEX BY BINARY_INTEGER;
   
                PROCEDURE allperson
                        (ssn    OUT     tssn,
                         fname  OUT     tfname,
                         lname  OUT     tlname);
              
             END packperson;
   
             / 
   					

3. Use the following code to create the package body:

   CREATE OR REPLACE PACKAGE BODY packperson
            AS
   
            PROCEDURE allperson
                        (ssn    OUT     tssn,
                         fname  OUT     tfname,
                         lname  OUT     tlname)
            IS
                CURSOR person_cur IS
                        SELECT ssn, fname, lname
                        FROM person;
   
                percount NUMBER DEFAULT 1;
   
            BEGIN
                FOR singleperson IN person_cur
                LOOP
                        ssn(percount) := singleperson.ssn;
                        fname(percount) := singleperson.fname;
                        lname(percount) := singleperson.lname;
                        percount := percount + 1;
                END LOOP;
            END;
   
            END;
            / 
   					

back to the top

Steps to Create Visual C# .NET Application

1. Create a new Visual C# Windows Application project. Form1 is added to the project by default.
2. Drag a DataGrid control and a Button control to Form1. DataGrid1 and Button1 are added to Form1 by default.
3. Add the following code after the first line, "using System.Data," in the Code window:

   using System.Data.OleDb;
   					

4. Add the following code to the Button1_Click event:

   OleDbConnection cnOra = new OleDbConnection("Provider=MSDAORA;Data Source=myOracleServer;" 
                                              + "user id=myUID;password=myPWD;"
                                              + "persist security info=false;");
   OleDbCommand cmdPerson = new OleDbCommand
               + ("{call PackPerson.allPerson({resultset 3, ssn, fname, lname})}", cnOra);
   		OleDbDataAdapter daPerson = new OleDbDataAdapter(cmdPerson);
   
   cnOra.Open();
   
   DataSet ds = new DataSet();
   daPerson.Fill(ds,"Person");
   this.dataGrid1.DataSource = ds.Tables["Person"];
   
   cnOra.Close();
   					

5. Press the F5 key to build and to run the application.
6. Click Button1. Notice that the results appear in the DataGrid control.

NOTE: Because only Oracle 8.0 and later support the retrieval of result sets and the execution of stored procedures, you must use an Oracle 8.x server to run this application.

back to the top