Export (0) Print
Expand All

OracleDataAdapter Class

Note: This namespace, class, or member is supported only in version 1.1 of the .NET Framework.

Represents a set of data commands and a connection to a database that are used to fill the DataSet and update the database. This class cannot be inherited.

For a list of all members of this type, see OracleDataAdapter Members.

System.Object
   System.MarshalByRefObject
      System.ComponentModel.Component
         System.Data.Common.DataAdapter
            System.Data.Common.DbDataAdapter
               System.Data.OracleClient.OracleDataAdapter

[Visual Basic]
NotInheritable Public Class OracleDataAdapter
   Inherits DbDataAdapter
   Implements IDbDataAdapter
[C#]
public sealed class OracleDataAdapter : DbDataAdapter,
   IDbDataAdapter
[C++]
public __gc __sealed class OracleDataAdapter : public
   DbDataAdapter, IDbDataAdapter
[JScript]
public class OracleDataAdapter extends DbDataAdapter implements
   IDbDataAdapter

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

Remarks

The OracleDataAdapter serves as a bridge between a DataSet and database for retrieving and saving data. The OracleDataAdapter provides this bridge by using Fill to load data from the database into the DataSet, and using Update to send changes made in the DataSet back to the data source.

When the OracleDataAdapter fills a DataSet, it will create the necessary tables and columns for the returned data if they do not already exist. However, primary key information will not be included in the implicitly created schema unless the MissingSchemaAction property is set to AddWithKey. You may also have the OracleDataAdapter create the schema of the DataSet, including primary key information, before filling it with data using FillSchema. For more information, see Adding Existing Constraints to a DataSet.

The OracleDataAdapter also includes the SelectCommand, InsertCommand, DeleteCommand, UpdateCommand, and TableMappings properties to facilitate loading and updating of data.

The .NET Framework Data Provider for Oracle does not support batched SQL statements. However, it does allow you to use multiple REF CURSOR output parameters to fill a DataSet, each in its own DataTable. You must define the parameters, mark them as output parameters, and indicate that they are REF CURSOR data types. Note that you will be unable to use the Update method when the OracleDataAdapter is filled from REF CURSOR parameters to a stored procedure, because Oracle does not provide the information necessary to determine what the table name and column names are when the SQL statement is executed. The following C# example assumes that you have created this stored procedure.

create or replace package sp_pkg as
      type refCursorxx is ref cursor;
procedure getdata(a1 out refCursorxx, a2 out refCursorxx);
end;
create or replace package body sp_pkg as
       procedure getdata(a1 in number, a2 out refCursorxx) is
       begin
            open a1 for select * from emp;
            open a2 for select * from dept;
            end getdata;
       end;

The following C# example demonstrates how you might obtain table and column information using the stored procedure.

[C#] 
OracleConnection conn = new OracleConnection("Data Source=Oracle8i;Integrated Security=yes");
Conn.Open;
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "sp_pkg.getdata";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("a1", OracleType.Cursor)).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter("a2", OracleType.Cursor)).Direction = ParameterDirection.Output;
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
adapter.Fill(ds);

The following Visual Basic example demonstrates how to use an OracleDataAdapter to fill a DataSet using an Oracle REF CURSOR. These examples use tables that are defined in the Oracle Scott/Tiger schema, and require the following PL/SQL package and package body. You must create these on your server to use the examples.

Create the following Oracle package on the Oracle server.

CREATE OR REPLACE PACKAGE CURSPKG AS 
    TYPE T_CURSOR IS REF CURSOR; 
    PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, 
                               IO_CURSOR IN OUT T_CURSOR); 
    PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, 
                                DEPTCURSOR OUT T_CURSOR);
END CURSPKG;
/

Create the following Oracle package body on the Oracle server.

CREATE OR REPLACE PACKAGE BODY CURSPKG AS
    PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,
                               IO_CURSOR IN OUT T_CURSOR)
    IS 
        V_CURSOR T_CURSOR; 
    BEGIN s 
        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_ONE_CURSOR; 
    PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
                                DEPTCURSOR OUT T_CURSOR)
    IS 
        V_CURSOR1 T_CURSOR; 
        V_CURSOR2 T_CURSOR; 
    BEGIN 
        OPEN V_CURSOR1 FOR SELECT * FROM EMP;
        OPEN V_CURSOR2 FOR SELECT * FROM DEPT;
        EMPCURSOR  := V_CURSOR1; 
        DEPTCURSOR := V_CURSOR2; 
    END OPEN_TWO_CURSORS; 
END CURSPKG;
/

This Visual Basic example executes a PL/SQL stored procedure that returns two REF CURSOR parameters, and fills a DataSet with the rows that are returned.

[Visual Basic] 
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim connString As New String("Data Source=Oracle8i;Integrated Security=yes")
    Dim ds As New DataSet()
    Dim conn As New OracleConnection(connString)
    Dim cmd As New OracleCommand()
    cmd.Connection = conn
    cmd.CommandText = "CURSPKG.OPEN_TWO_CURSORS"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New OracleParameter("EMPCURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output
    cmd.Parameters.Add(New OracleParameter("DEPTCURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output
    Dim da As New OracleDataAdapter(cmd)
    Try
        da.Fill(ds)
    Catch Myex As Exception
        MessageBox.Show(Myex.Message.ToString)
    End Try
    ds.Relations.Add("EmpDept", ds.Tables(0).Columns("Deptno"), ds.Tables(1).Columns("Deptno"), False)
    DataGrid1.DataSource = ds.Tables(0)

After using the OracleDataAdapter to perform a Fill or FillSchema operation, the DataColumn.ReadOnly property always returns false, regardless of whether a column can be updated or not, because the Oracle server does not return this information.

Example

[Visual Basic, C#, C++] The following example uses OracleCommand, OracleDataAdapter and OracleConnection to select records from an Access database, and populate a DataSet with the selected rows. The filled DataSet is then returned. To accomplish this, the method is passed an initialized DataSet, a connection string, and a query string that is an SQL SELECT statement.

[Visual Basic] 
Public Function SelectOracleSrvRows(dataSet As DataSet, connection As String, query As String) As DataSet
    Dim conn As New OracleConnection(connection)
    Dim adapter As New OracleDataAdapter()
    adapter.SelectCommand = new OracleCommand(query, conn)
    adapter.Fill(dataset)
    Return dataset
End Function

[C#] 
public DataSet SelectOracleSrvRows(DataSet dataset,string connection,string query) 
{
    OracleConnection conn = new OracleConnection(connection);
    OracleDataAdapter adapter = new OracleDataAdapter();
    adapter.SelectCommand = new OracleCommand(query, conn);
    adapter.Fill(dataset);
    return dataset;
}


[C++] 
public:
DataSet* SelectOracleSrvRows(DataSet* dataset,String* connection,String* query) 
{
    OracleConnection* conn = new OracleConnection(connection);
    OracleDataAdapter* adapter = new OracleDataAdapter();
    adapter->SelectCommand = new OracleCommand(query, conn);
    adapter->Fill(dataset);
    return dataset;
}

[JScript] No example is available for JScript. To view a Visual Basic, C#, or C++ example, click the Language Filter button Language Filter in the upper-left corner of the page.

Requirements

Namespace: System.Data.OracleClient

Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family

Assembly: System.Data.Oracleclient (in System.Data.Oracleclient.dll)

See Also

OracleDataAdapter Members | System.Data.OracleClient Namespace | OracleConnection | OracleCommand

Show:
© 2014 Microsoft