OracleCommandBuilder Class

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

Class Inheritance

System.Object

  System.MarshalByRefObject

    System.ComponentModel.Component

      System.Data.Common.DbCommandBuilder (ADO.NET 2.0 only)

        OracleDataAccess.Client.OracleCommandBuilder

Declaration

// ADO.NET 2.0: C#
public sealed class OracleCommandBuilder : DbCommandBuilder

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

ODP.NET Version: ODP.NET for .NET Framework 2.0 or ODP.NET for .NET Framework 4

---

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.

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

CatalogLocation

This property is not supported.

Declaration

// ADO.NET 2.0: C#
public override CatalogLocation CatalogLocation {get; set;}

Exceptions

NotSupportedException - This property is not supported.

Remarks

This property is not supported.

CatalogSeparator

This property is not supported.

Declaration

// ADO.NET 2.0: C#
public override string CatalogSeparator {get; set;}
 

Exceptions

NotSupportedException - This property is not supported.

Remarks

This property is not supported.

ConflictOption

This property is not supported.

Declaration

// ADO.NET 2.0: C#
public override string ConflictOption {get; set;}

Exceptions

NotSupportedException - This property is not supported.

Remarks

This property is not supported.

DataAdapter

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

Declaration

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

Property Value

An OracleDataAdapter object.

Remarks

Default = null

QuotePrefix

This property specifies the beginning character or characters used to specify database objects whose names contain special characters such as spaces or reserved words.

Declaration

// ADO.NET 2.0: C#
public override string QuotePrefix {get; set;}

Property Value

The beginning character or characters to use. The default value is "\"".

Remarks

This property is independent of any OracleConnection or OracleCommand objects.

QuoteSuffix

This property specifies the ending character or characters used to specify database objects whose names contain special characters such as spaces or reserved words.

Declaration

// ADO.NET 2.0: C#
public override string QuoteSuffix {get; set;}

Property Value

The ending character or characters to use. The default value is "\"".

Remarks

This property is independent of any OracleConnection or OracleCommand objects.

SchemaSeparator

This property specifies the character to be used for the separator between the schema identifier and other identifiers.

Declaration

// ADO.NET 2.0: C#
public override string SchemaSeparator {get; set; }

Property Value

The character to be used as the schema separator.

Exceptions

NotSupportedException - The input value is not a dot (.).

Remarks

The default schema separator is a dot (.). The only acceptable value for this property is a dot (.).

This property is independent of any OracleConnection or OracleCommand objects.

Example

// C#
 
using System;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;
 
class SchemaSeperatorSample
{
  static void Main(string[] args)
  {
    try
    {
      OracleCommandBuilder cmdBuilder = new OracleCommandBuilder();
 
      //schemaSeparator is dot(.)
      Console.WriteLine("schemaSeparator is {0}", 
                            cmdBuilder.SchemaSeparator);
 
      //set the schemaseparator, only '.' is allowed.
      cmdBuilder.SchemaSeparator = ".";
 
      // the only acceptable value for this property is a dot (.)
      // Hence the following line will throw NotSupportedException
      cmdBuilder.SchemaSeparator = "!";
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
      Console.WriteLine(ex.StackTrace);
    }
  }
}

---

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.

QuoteIdentifier

This method returns the correct quoted form of the provided unquoted identifier, with any embedded quotes in the identifier properly escaped.

Declaration

// ADO.NET 2.0: C#
public override string QuoteIdentifier(string unquotedIdentifier);

Parameters

• UnquotedIdentifier

  An unquoted identifier string.

Return Value

The quoted version of the identifier. Embedded quotes within the identifier are properly escaped.

Exceptions

ArgumentNullException - The input parameter is null.

Remarks

This method is independent of any OracleConnection or OracleCommand objects.

Example

// C#
 
using System;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;
 
class QuoteIdentifierSample
{
  static void Main(string[] args)
  {
    OracleCommandBuilder builder = new OracleCommandBuilder();
    string quoteIdentifier = builder.QuoteIdentifier("US\"ER");
    
    //quoteIdentifier for "US\"ER" is (\"US\"\"ER\")
    Console.WriteLine("quoteIdentifier is {0}" , quoteIdentifier);
  }
}

RefreshSchema

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

Declaration

// ADO.NET 2.0: C#
public override void RefreshSchema();

Remarks

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

UnquoteIdentifier

This method returns the correct unquoted form of the provided quoted identifier, removing any escape notation for quotes embedded in the identifier.

Declaration

// ADO.NET 2.0: C#
public override string UnquoteIdentifier(string quotedIdentifier);

Parameters

• quotedIdentifier

  The quoted string identifier.

Return Value

The unquoted identifier, with escape notation for any embedded quotes removed.

Exceptions

ArgumentNullException - The input parameter is null.

ArgumentException - The input parameter is empty.

Remarks

This method is independent of any OracleConnection or OracleCommand objects.

Example

// C#
 
using System;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;
 
class UnQuoteIdentifierSample
{
  static void Main(string[] args)
  {
    //create an OracleCommandBuilder object.
    OracleCommandBuilder builder = new OracleCommandBuilder();
 
    string identifier = "US\"ER";
    Console.WriteLine("Identifier is {0}", identifier);
 
    // quote the identifier
    string quoteIdentifier = builder.QuoteIdentifier(identifier);
 
    //quoteIdentifier of "US\"ER" is (\"US\"\"ER\")
    Console.WriteLine("QuotedIdentifier is {0}" , quoteIdentifier);
    string unquoteIdentifier = builder.UnquoteIdentifier(quoteIdentifier);
 
    //And its unquoteIdentifier is US\"ER
    Console.WriteLine("UnquotedIdentifier is {0}" , unquoteIdentifier);
  }
}  

---

OracleCommandBuilder Events

The OracleCommandBuilder event is listed in Table 5-16.