Oracle® Data Provider for .NET Developer's Guide 10g Release 2 (10.2) Part Number B14307-01 |
|
|
View PDF |
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
See Also:
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.
See Also:
This constructor creates an instance of the OracleCommandBuilder
class.
Declaration
// C# public OracleCommandBuilder();
Remarks
Default constructor.
See Also:
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.
See Also:
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) |
See Also:
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:
http://msdn.microsoft.com/library
for detailed information about this Microsoft .NET Framework 1.1 feature
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 |
See Also:
This property indicates the OracleDataAdapter
for which the SQL statements are generated.
Declaration
// C# OracleDataAdapter DataAdapter{get; set;}
Property Value
OracleDataAdapter
Remarks
Default = null
See Also:
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
See Also:
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 |
See Also:
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
.
See Also:
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
.
See Also:
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
.
See Also:
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.
See Also:
The OracleCommandBuilder
event is listed in Table 5-16.