OracleCommandBuilder Class

An OracleCommandBuilder object provides automatic SQL generation for the OracleDataAdapter when updates are made to the database.

Class Inheritance

Object

  MarshalByRefObject

    Component

      OracleCommandBuilder

Declaration

// C#
public sealed class OracleCommandBuilder : Component

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Remarks

OracleCommandBuilder automatically generates SQL statements for single-table updates when the SelectCommand property of the OracleDataAdapter is set. An exception is thrown if the DataSet contains multiple tables. The OracleCommandBuilder registers itself as a listener for RowUpdating events whenever its DataAdapter property is set. Only one OracleDataAdapter object and one OracleCommandBuilder object can be associated with each other at one time.

To generate INSERT, UPDATE, or DELETE statements, the OracleCommandBuilder uses ExtendedProperties within the DataSet to retrieve a required set of metadata. If the SelectCommand is changed after the metadata is retrieved (for example, after the first update), the RefreshSchema method should be called to update the metadata.

OracleCommandBuilder first looks for the metadata from the ExtendedProperties of the DataSet; if the metadata is not available, OracleCommandBuilder uses the SelectCommand property of the OracleDataAdapter to retrieve the metadata.

Example

The following example performs an update on the EMP table. It uses the OracleCommandBuilder object to create the UpdateCommand for the OracleDataAdapter object when OracleDataAdapter.Update() is called.

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client;
 
class OracleCommandBuilderSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    string cmdstr = "SELECT empno, sal from emp";
 
    // Create the adapter with the selectCommand txt and the
    // connection string
    OracleDataAdapter adapter = new OracleDataAdapter(cmdstr, constr);
 
    // Create the builder for the adapter to automatically generate
    // the Command when needed
    OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
 
    // Create and fill the DataSet using the EMP
    DataSet dataset = new DataSet();
    adapter.Fill(dataset, "EMP");
 
    // Get the EMP table from the dataset
    DataTable table = dataset.Tables["EMP"];
 
    // Indicate DataColumn EMPNO is unique
    // This is required by the OracleCommandBuilder to update the EMP table
    table.Columns["EMPNO"].Unique = true;
 
    // Get the first row from the EMP table
    DataRow row = table.Rows[0];
 
    // Update the salary
    double sal = double.Parse(row["SAL"].ToString());
    row["SAL"] = sal + .01;
 
    // Now update the EMP using the adapter
    // The OracleCommandBuilder will create the UpdateCommand for the
    // adapter to update the EMP table
    adapter.Update(dataset, "EMP");
 
    Console.WriteLine("Row updated successfully");
  }
}

Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

---

OracleCommandBuilder Members

OracleCommandBuilder members are listed in the following tables:

OracleCommandBuilder Constructors

OracleCommandBuilder constructors are listed in Table 5-8.

OracleCommandBuilder Static Methods

OracleCommandBuilder static methods are listed in Table 5-9.

OracleCommandBuilder Properties

OracleCommandBuilder properties are listed in Table 5-10.

OracleCommandBuilder Public Methods

OracleCommandBuilder public methods are listed in Table 5-11.

OracleCommandBuilder Events

The OracleCommandBuilder event is listed in Table 5-12.

---

OracleCommandBuilder Constructors

OracleCommandBuilder constructors create new instances of the OracleCommandBuilder class.

Overload List:

• OracleCommandBuilder()

  This constructor creates an instance of the OracleCommandBuilder class.

• OracleCommandBuilder(OracleDataAdapter)

  This constructor creates an instance of the OracleCommandBuilder class and sets the DataAdapter property to the provided OracleDataAdapter object.

OracleCommandBuilder()

This constructor creates an instance of the OracleCommandBuilder class.

Declaration

// C#
public OracleCommandBuilder();

Remarks

Default constructor.

OracleCommandBuilder(OracleDataAdapter)

This constructor creates an instance of the OracleCommandBuilder class and sets the DataAdapter property to the provided OracleDataAdapter object.

Declaration

// C#
public OracleCommandBuilder(OracleDataAdapter da);

Parameters

• da

  The OracleDataAdapter object provided.

---

OracleCommandBuilder Static Methods

OracleCommandBuilder static methods are listed in Table 5-13.

DeriveParameters

This method queries for the parameters of a stored procedure or function, represented by a specified OracleCommand, and populates the OracleParameterCollection of the command with the return values.

Declaration

// C#
public static void DeriveParameters(OracleCommand command);

Parameters

• command

  The command that represents the stored procedure or function for which parameters are to be derived.

Exceptions

InvalidOperationException - The CommandText is not a valid stored procedure or function name, the CommandType is not CommandType.StoredProcedure, or the Connection.State is not ConnectionState.Open.

Remarks

When DeriveParameters is used to populate the Parameter collection of an OracleCommand Object that represents a stored function, the return value of the function is bound as the first parameter (at position 0 of the OracleParameterCollection).

DeriveParameters can only be used for stored procedures or functions, not for anonymous PL/SQL blocks.

Invoking DeriveParameters deletes all existing parameters in the parameter collection of the command.

DeriveParameters incurs a database round-trip and should only be used during design time. To avoid unnecessary database round-trips in a production environment, the DeriveParameters method itself should be replaced with the explicit parameter settings that were returned by the DeriveParameters method at design time.

DeriveParameters can only preserve the case of the stored procedure or function name if it is encapsulated by double-quotes. For example, if the stored procedure in the database is named GetEmployees with mixed-case, the CommandText property on the OracleCommand object must be set appropriately as in the following example:

cmd.CommandText = "\"GetEmployees\"";

Stored procedures and functions in a package must be provided in the following format:

<package name>.<procedure or function name> 

For example, to obtain parameters for a stored procedure named GetEmployees (mixed-case) in a package named EmpProcedures (mixed-case), the name provided to the OracleCommand is:

"\"EmpProcedures\".\"GetEmployees\""

DeriveParameters cannot be used for object type methods.

The derived parameters contain all the metadata information that is needed for the stored procedure to execute properly. The application must provide the value of the parameters before execution, if required. The application may also modify the metadata information of the parameters before execution. For example, the Size property of the OracleParameter may be modified for PL/SQL character and string types to optimize the execution of the stored procedure.

The output values of derived parameters return as .NET Types by default. To obtain output parameters as provider types, the OracleDbType property of the parameter must be set explicitly by the application to override this default behavior. One quick way to do this is to set the OracleDbType to itself for all output parameters that should be returned as provider types.

The BindByName property of the supplied OracleCommand is left as is, but the application can change its value.

If the specified stored procedure or function is overloaded, the first overload is used to populate the parameters collection.

// Database Setup
/*
connect scott/tiger@oracle
CREATE OR REPLACE PROCEDURE MyOracleStoredProc (arg_in IN VARCHAR2, 
  arg_out OUT VARCHAR2) IS
BEGIN
  arg_out := arg_in;
END;
/
*/
 
// C#
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class DeriveParametersSample
{
  static void Main()
  {
    // Create the PL/SQL Stored Procedure MyOracleStoredProc as indicated in
    // the preceding Database Setup
 
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    // Create an OracleCommand
    OracleCommand cmd = new OracleCommand("MyOracleStoredProc", con);
    cmd.CommandType = CommandType.StoredProcedure;
 
    // Derive Parameters
    OracleCommandBuilder.DeriveParameters(cmd); 
    Console.WriteLine("Parameters Derived");
 
    // Prints "Number of Parameters for MyOracleStoredProc = 2"
    Console.WriteLine("Number of Parameters for MyOracleStoredProc = {0}",
      cmd.Parameters.Count);
 
    // The PL/SQL stored procedure MyOracleStoredProc has one IN and 
    // one OUT parameter.  Set the Value for the IN parameter.
    cmd.Parameters[0].Value = "MyText";
 
    // The application may modify the other OracleParameter properties also
    // This sample uses the default Size for the IN parameter and modifies
    // the Size for the OUT parameter
 
    // The default size for OUT VARCHAR2 is 4000
    // Prints "cmd.Parameters[1].Size  = 4000"
    Console.WriteLine("cmd.Parameters[1].Size  = " + cmd.Parameters[1].Size);
 
    // Set the Size for the OUT parameter
    cmd.Parameters[1].Size = 6;
 
    // Execute the command
    cmd.ExecuteNonQuery();
 
    // Prints "cmd.Parameters[1].Value = MyText"
    Console.WriteLine("cmd.Parameters[1].Value = " + cmd.Parameters[1].Value);
 
    con.Close();
    con.Dispose();
  }
}

Example

---

OracleCommandBuilder Properties

OracleCommandBuilder properties are listed in Table 5-14.

DataAdapter

This property indicates the OracleDataAdapter for which the SQL statements are generated.

Declaration

// C#
OracleDataAdapter DataAdapter{get; set;}

Property Value

OracleDataAdapter

Remarks

Default = null

CaseSensitive

This property indicates whether or not double quotes are used around Oracle object names (for example, tables or columns) when generating SQL statements.

Declaration

// C#
bool CaseSensitive {get; set;}

Property Value

A bool that indicates whether or not double quotes are used.

Remarks

Default = false

---

OracleCommandBuilder Public Methods

OracleCommandBuilder public methods are listed in Table 5-15.

GetDeleteCommand

This method gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform deletions on the database when an application calls Update() on the OracleDataAdapter.

Declaration

// C#
public OracleCommand GetDeleteCommand();

Return Value

An OracleCommand.

Exceptions

ObjectDisposedException - The OracleCommandBuilder object is already disposed.

InvalidOperationException - Either the SelectCommand or the DataAdapter property is null, or the primary key cannot be retrieved from the SelectCommand property of the OracleDataAdapter.

GetInsertCommand

This method gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform insertions on the database when an application calls Update() on the OracleDataAdapter.

Declaration

// C#
public OracleCommand GetInsertCommand();

Return Value

An OracleCommand.

Exceptions

ObjectDisposedException - The OracleCommandBuilder object is already disposed.

InvalidOperationException - Either the SelectCommand or the DataAdapter property is null, or the primary key cannot be retrieved from the SelectCommand property of the OracleDataAdapter.

GetUpdateCommand

This method gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform updates on the database when an application calls Update() on the OracleDataAdapter.

Declaration

// C#
public OracleCommand GetUpdateCommand();

Return Value

An OracleCommand.

Exceptions

ObjectDisposedException - The OracleCommandBuilder object is already disposed.

InvalidOperationException - Either the SelectCommand or the DataAdapter property is null, or the primary key cannot be retrieved from the SelectCommand property of the OracleDataAdapter.

RefreshSchema

This method refreshes the database schema information used to generate INSERT, UPDATE, or DELETE statements.

Declaration

// C#
public void RefreshSchema();

Remarks

An application should call RefreshSchema whenever the SelectCommand value of the OracleDataAdapter changes.

---

OracleCommandBuilder Events

The OracleCommandBuilder event is listed in Table 5-16.