Oracle Recipe #3: How to Execute a query that returns a Single Row with the method GetOracleValues.

if you want to obtain multiple values from a database, you can call the executeReader method once on a OracleCommand object, to execute a SQL statement that returns a collection of values in a single row result.

The ExecuteReader method returns an instance of a class that implements the IDataReader interface. Each of the data reader classes provided by .NET Framework has a GetValues method, which returns an array of column values for the current row.

To obtain a single row from a database.

  1. Open a OracleConnection.
  2. Create and initialize a OracleCommand object.
  3. Call the ExecuteReader method on the command object. Assign the return value from this method to a data reader variable.
  4. Call the Read method on the data reader object to move to the first(and only) row in the result set.
  5. Call the GetOracleValues method on the data reader object. Pass an object array as a parameter to retrieve the scalar results of the query.
  6. Convert each element in the array to an appropriate data type, if necessary.
  7. Close the OracleDataReader object.
  8. Dispose the OracleCommand object.
  9. Close the database connection.

The following example shows how to execute a query that returns a set of values.
The example place the results into an array named results.

Fig 1. Using the GetOracleValues of an OracleDataReader object.

Fig 2. Testing the program.

Download example source code.

Oracle Recipe #2: How to execute a query that returns a Scalar result with OracleCommand.

  1. Microsoft ADO.NET command objects have an ExecuteScalar method, which enables you to execute a query that returns a single result.
  2. Open a database connection.
  3. Create and initialize a command object.
  4. Call the ExecuteScalar method on the command object.
  5. Convert the return value from ExecuteScalar into an appropriate data type.
  6. Dispose the command object.
  7. Close the database connection.

The following example, show how to execute a query that determines the average salary from the table employees on the HR schema provided by Oracle Database XE.
The example assume that the query does not return a NULL result.

Fig 1. OracleCommand ExecuteScalar method code example.

Download source code