OracleDataAdapter Wizard

The OracleDataAdapter Wizard generates Oracle Data Provider .NET code so that you can connect Oracle Database to your applications. It lets you modify the SELECT, INSERT, UPDATE, and DELETE statements and connection information that are generated in the OracleDataAdapter, OracleConnection, and OracleCommand components.

The OracleDataAdapter Wizard is provided for backward compatibility with Visual Studio 2003.

Visual Studio 2005 and Visual Studio 2008 users typically use Microsoft TableAdapters, using the Microsoft Data Sources window and the Microsoft Table Adapter Wizard to generate the TableAdapters. For more information on TableAdapters, see the Microsoft documentation.

This section covers the following topics:

Starting the OracleDataAdapter Wizard

To start the OracleDataAdapter Wizard, use one of the following methods:

  • Right-click the OracleDataAdapter component and from the menu, select Configure with OracleDataAdapter Wizard.

  • Drag an OracleDataAdapter component from the toolbox onto the project's form designer.

The OracleDataAdapter Wizard's opening screen appears as follows:

Description of oracledataadapter1_wizard.gif follows
Description of the illustration oracledataadapter1_wizard.gif

Using the OracleDataAdapter Wizard

In general, you use the OracleDataAdapter Wizard to modify the Oracle Data Provider data access code for your database objects. After you complete the wizard, the changes are reflected in the OracleDataAdapter, OracleConnection, and OracleCommand components.

The windows in the OracleDataAdapter wizard are as follows:

Window Description

Welcome to the OracleDataAdapter Wizard

Welcome page for the wizard. To bypass this page the next time you run the wizard, select the Do not show this page again check box.

Configure your OracleConnection

Specifies the data connection for the Oracle data adapter, with the current connection offered as the default. To use a different connection, click

New Connection. The new connection information will appear in the OracleConnection component.

Specify your SELECT statement type

Select from the following choices to execute the OracleDataAdapter SELECT command:

  • Create a SQL SELECT statement

  • Create anonymous PL/SQL SELECT statement

  • Create SELECT statement using an existing Stored Procedure

Configure your SELECT statement

Depending on the choices you made in the previous window, do the following:

  • If you selected either the SQL SELECT statement or the anonymous PL/SQL SELECT statement options in the previous window, enter code to execute the SELECT statement.

    To create parameters for the SELECT statement, click Parameters. To validate that the SELECT statement works, click Preview Results.

  • If you selected the Create SELECT statement using an existing Procedure option, select the procedure that you want from the drop-down list. Procedures appear in the following order:

    1. Procedures and functions that the current user owns.

    2. Package methods that the current user owns.

    3. Procedures and functions that other users own.

    4. Package methods that other users own.

    If you select a function, its parameters (parameter name and data type (all data types and Oracle-specific data types)) appear in the Parameters region. If the parameters cannot be generated, an error appears instead.

Configure your INSERT, UPDATE, and DELETE statements

Select from the following choices:

  • Automatic: Generates the INSERT, UPDATE, and DELETE statements based on the SELECT statement you created in the previous step.

  • Custom: Allows you to customize the INSERT, UPDATE, and DELETE statements.

  • Do not create: Bypasses the creation of INSERT, UPDATE, and DELETE statements.

Specify your INSERT statement type

Specify your UPDATE statement

Specify your DELETE statement

If you selected Custom in the previous step, use these screens to enter the SELECT statement's INSERT, UPDATE, and DELETE statements.

To create parameters for the INSERT, UPDATE, or DELETE statement, click Parameters. To validate that the statement works, click Preview Results.

Note: Not all nodes can generate INSERT, UPDATE, and DELETE statements.

Summary

Displays a summary of all your choices. To complete the wizard, click Finish.

Configuring Parameters for Statements

When you configure the SELECT, INSERT, UPDATE, or DELETE statement in the OracleDataAdapter Wizard, you have the option of setting parameters for the statement by clicking the Parameters button. A dialog box similar to the following appears:

Description of oracledataadapter_parameters.gif follows
Description of the illustration oracledataadapter_parameters.gif

The controls in the Parameters dialog box are as follows:

Control Description

Parameters

Lists parameters for this statement.

To create a new parameter, click Add, and then use the Parameter Details pane to modify the parameter as needed. To remove a parameter, select it and click Remove.

The parameters displayed are based on the previous selection: SQL, PL/SQL, or stored procedure.

Parameter Details

Displays the following information, depending on the type of statement used:

  • Name: Enter the parameter's name. This name will be used for parameter binding if you select the By name option under Parameter Binding.

  • Direction: Select from the following:

    • Input: Indicates that you must supply a value for the parameter when calling the statement.

    • Output: Indicates that the statement passes a value for this parameter back to its calling environment after execution

    • Input-Output: Indicates that you must supply a value for the parameter when calling the statement and that the statement passes a value back to its calling environment after execution.

    • ReturnValue: Indicates that the parameter will hold the return value of a function call. You will not typically have ReturnValue parameters for SQL or PL/SQL statements.

  • Type: Represents either of the following:

    • Oracle DbType: Select from the list of data types supported by Oracle Database. This setting returns the value of the parameter as the appropriate Oracle Data Provider. NET (ODP.NET) type. Alternatively, select the data type from DbType.

    • DbType: Select from the list of Microsoft Active X Data Objects .NET (ADO.NET) data types. This setting returns the value of the parameter as the appropriate system type. Alternatively, select the data type from Oracle DbType.

  • Precision: Depending on the Type selection, specifies the parameter's precision.

  • Scale: Depending on the Type selection, specifies the parameter's scale.

  • Size: Depending on the Type selection, specifies the size of the parameter.

  • Source column: Name of the DataSet column to which a parameter is bound. For INSERT, UPDATE, or DELETE statements only. See the description of Source version for an example.

  • Source version: Allows you to select which state of a DataSet change to write to the database for an INSERT, UPDATE, or DELETE statement. Select from the following:

    • Original: Value before any changes have been applied. Typically, this value is the value that was last retrieved from the database, and is used for parameters in the WHERE clause of an update or delete.

    • Current: Current value of the DataSet. It is typically used for new values to insert.

    • Proposed: Value for unapplied changes made to the DataSet such as edits that are currently in progress, or new rows that have not yet been added to the DataSet.

    • Default: Value of the DataSet before any changes have been applied. This value has been written to the database by the ODP.NET AcceptChanges call.

    For example, table EMP has two columns, EMPNO and ENAME. Its command text is: 

    UPDATE EMP SET EMPNO = :0, ENAME = :1 where EMPNO = :2 and ENAME = :3

    Setting the WHERE clause parameters 2 and 3 to use Original for their source version ensures that updates to the row will not have changed since the last time that row was fetched:

    • Parameter 0: Source version setting = Current; source column = "EMPNO"

    • Parameter 1: Source version setting = Current; source column = "ENAME"

    • Parameter 2: Source version setting = Original; source column="EMPNO"

    • Parameter 3: Source version setting = Original; source column="ENAME"

Parameter Binding

Select one of the following:

  • By position: Binds the parameters for this command based on the order in which they are added to the OracleCommand.

  • By name: Binds the parameters for this command based on the name you enter for the parameter in the Name box under Parameter Details.

OK

Creates the parameter and returns to the OracleDataAdapter wizard.

See Also

| Data Connections Node | Schemas Node | Connecting to an Oracle Database from Visual Studio .NET