How To Fill a DataSet from an Oracle Stored Procedure Using the OLE DB .NET Data Provider with Visual Basic .NET

For a Microsoft Visual C# .NET version of this article, see
310101 .
For a Microsoft Visual Basic 6.0 version of this article, see
176086 .

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

IN THIS TASK

Summary

This article demonstrates how to fill a DataSet object with the resultset 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.

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 Basic .NET syntax
  • ActiveX Data Objects (ADO)

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;
    /

Steps to Create Visual Basic .NET Application

  1. Create a new Visual Basic .NET Windows application. Form1 is added to the application by default.
  2. Drag a DataGrid control and a Button control, and drop the controls onto Form1. DataGrid1 and Button1 are added to Form1 by default.
  3. Add the following code as the first line in the Code window:
    Imports System.Data.OleDb
  4. Add the following code in the Button1_Click event:
    Dim cnOra As New OleDbConnection("Provider=MSDAORA;Data Source=myOracleServer;" & _
    "user id=myUID;password=myPWD;Persist Security info=False;")

    Dim cmdPerson As New OleDbCommand _
    ("{call packPerson.allPerson({resultset 3, ssn, fname, lname})}", cnOra)
    cmdPerson.CommandType = CommandType.Text

    Dim daPerson As New OleDbDataAdapter(cmdPerson)

    cnOra.Open()

    Dim ds As New DataSet()
    Try
    daPerson.Fill(ds, "Persons")
    Me.DataGrid1.DataSource = ds.Tables("Persons")
    Catch ex As Exception
    MsgBox(ex.Message)
    End Try

    cnOra.Close()
  5. Modify the OleDbConnection string as appropriate for your environment.
  6. Press the F5 key to build and run the application.
  7. Click Button1. The results should appear in the DataGrid control.
NOTE: Because only Oracle 8.0 and later support the retrieval of resultsets and the execution of stored procedures, you must use an Oracle 8.x server to run this application.

References

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
176086 How To Retrieve Recordsets from Oracle Stored Procedures Using ADO
308073 How To Use a DataReader Against an Oracle Stored Procedure in Visual Basic .NET
Propriedades

ID do Artigo: 308072 - Última Revisão: 23 de mar de 2009 - Revisão: 1

Comentários