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 are listed in the following tables:
OracleCommandBuilder Constructors
OracleCommandBuilder constructors are listed in Table 5-8.
Table 5-8 OracleCommandBuilder Constructors
| Constructor | Description |
|---|---|
| OracleCommandBuilder Constructors | Instantiates a new instance of OracleCommandBuilder class (Overloaded) |
OracleCommandBuilder Static Methods
OracleCommandBuilder static methods are listed in Table 5-9.
Table 5-9 OracleCommandBuilder Static Methods
| Methods | Description |
|---|---|
| DeriveParameters | 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 |
Equals |
Inherited from Object (Overloaded) |
OracleCommandBuilder Properties
OracleCommandBuilder properties are listed in Table 5-10.
Table 5-10 OracleCommandBuilder Properties
| Name | Description |
|---|---|
Container |
Inherited from Component |
| DataAdapter | Indicates the OracleDataAdapter for which the SQL statements are generated |
| CaseSensitive | Indicates whether or not double quotes are used around Oracle object names when generating SQL statements |
Site |
Inherited from Component |
OracleCommandBuilder Public Methods
OracleCommandBuilder public methods are listed in Table 5-11.
Table 5-11 OracleCommandBuilder Public Methods
| Public Method | Description |
|---|---|
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
| GetDeleteCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform deletions on the database |
GetHashCode |
Inherited from Object |
| GetInsertCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform insertions on the database |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
| GetUpdateCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform updates on the database |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
| RefreshSchema | Refreshes the database schema information used to generate INSERT, UPDATE, or DELETE statements |
ToString |
Inherited from Object |
OracleCommandBuilder Events
The OracleCommandBuilder event is listed in Table 5-12.
OracleCommandBuilder constructors create new instances of the OracleCommandBuilder class.
Overload List:
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.
This constructor creates an instance of the OracleCommandBuilder class.
Declaration
// C# public OracleCommandBuilder();
Remarks
Default constructor.
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 are listed in Table 5-13.
Table 5-13 OracleCommandBuilder Static Methods
| Methods | Description |
|---|---|
| DeriveParameters | 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 |
Equals |
Inherited from Object (Overloaded) |
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
|
See Also:
|
OracleCommandBuilder properties are listed in Table 5-14.
Table 5-14 OracleCommandBuilder Properties
| Name | Description |
|---|---|
Container |
Inherited from Component |
| DataAdapter | Indicates the OracleDataAdapter for which the SQL statements are generated |
| CaseSensitive | Indicates whether or not double quotes are used around Oracle object names when generating SQL statements |
Site |
Inherited from Component |
This property indicates the OracleDataAdapter for which the SQL statements are generated.
Declaration
// C#
OracleDataAdapter DataAdapter{get; set;}
Property Value
OracleDataAdapter
Remarks
Default = null
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 are listed in Table 5-15.
Table 5-15 OracleCommandBuilder Public Methods
| Public Method | Description |
|---|---|
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
| GetDeleteCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform deletions on the database |
GetHashCode |
Inherited from Object |
| GetInsertCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform insertions on the database |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
| GetUpdateCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform updates on the database |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
| RefreshSchema | Refreshes the database schema information used to generate INSERT, UPDATE, or DELETE statements |
ToString |
Inherited from Object |
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.
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.
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.
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.
The OracleCommandBuilder event is listed in Table 5-16.