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.

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.

Utilizando las clases de ADO.NET en Oracle.

Hace bastante tiempo que hice unas clases de ADO.NET para Oracle (un tipo helper de manera elemental). Quizás no es la manera más optima de acceder a una base de datos, pero al menos en ambientes restringidos en donde existen aplicaciones legadas donde no es posible actualizar otro proveedor de ADO.NET para Oracle que no sea el que viene predeterminado por .NET.

Aquí esta la clase para manejar la conexión, se llama OracleDataBase

Fig 1. Clase para manejar la conexion a la base de datos.

Utilizo otra clase llamada OracleDataBaseCommand para auxiliarme con los comandos.

Fig 2. Clase auxiliar para ejecutar los comandos en la base de datos.

Su utilización dentro de una clase que sirva para persistir o extraer datos sería de la siguiente manera.

Fig 3. Clase que utiliza las clases auxiliares para guardar un objeto.

Fig 4. Clase principal que crea, actualiza y consulta un cliente en la base de datos.