How to execute Oracle parameterized commands with ODP.NET

SQL statements can receive input-only parameters, output-only parameters, and bidirectional parameters.
You can use a OracleCommand object to execute parameterized SQL statements.
To execute a parameterized SQL statement use the following steps:

  1. Open a database connection,use OracleConnection.
  2. Create and initialize an OracleCommand object.
  3. Create a OracleParameter object, for each input parameter required by the SQL statement. Specify the name, type size, and value for each parameter, and add it to the parameters collection of the command object.
  4. Execute the command by calling the ExecuteScalar, ExecuteReader, ExecuteXmlReader, or ExecuteNonQuery method, as appropriate for the type of SQL statement.
  5. Use the return value obtained by executing the command.
  6. Dispose the command object.
  7. Close the database connection.

The following example shows how to execute a SQL statement that updates employee by employee id (please, check this post for further information).
The SQL statement requires the following parameters: prmFirstName , prmLastName, prmEmail, prmPhoneNumber, prmHireDate, prmSalary,prmCommission and prmEmployeeId.

Fig 1. The application code.

Fig 2. Running the program.

Download example source code.

Oracle Recipe #4 How to retrieve the list of schema objects with Oracle Data Provider for .NET (ODP.NET)

In this post, I am going to introduce you one of the sample schemas that Oracle provides as we learn Oracle database: The HR Schema.
But before I introduce it specifically, we need to understand what is a schema.

I’ve found two definitions for the same term, a schema basically can be:

  • A logical container for data structures
  • A collection of objects associated with the database.

Oracle draws the distinction between logical and physical structures: structures that are visible at a disk level or operating system level such as data files, control files and redo log files are considered physical structures, on the contrary, objects like tablespaces, schemas, tables, views , and any database objects are considered logical structures.
A container in this context means that a single schema name can contain many different objects, these logical objects are known as schema objects, and they are made up of structures such as:

  • Table: A table is the basic logical storage unit in the Oracle database; composed of rows and columns.
  • Cluster: A cluster is a set of tables physically stored together as one table.
  • Index: An index is a structure created to help retrieve data more quickly and efficiently.
  • View: Logically represents subsets of data from one or more tables.
  • Store procedure: Stored procedures are predefined SQL queries stored in the data dictionary designed to allow more efficient queries.
  • Sequence: Numeric value generator.
  • Package: Named PL/SQL modules that group related stored procedures, functions, and identifiers.
  • Synonyms: Gives alternative names to objects.

The HR schema

The HR schema is a sample schema that Oracle makes available for learning purposes.
You can install sample schemas using DBCA (DataBase Configuration Assistant) or you can get it from the following link:

Fig 1. Entity Relationship Diagram for HR Schema.

Schemas present a layer of abstraction for your data structure and it helps to avoid a problem called name collision. Let me show you an example: if we don’t use schemas a user called Bob can create a table called Employees, and then another user called Alice cannot create a table called Employees on the same schema that Bob, but Alice can create a table in a different schema. Other users can access or execute objects within a user’s schema once the schema owner grants privileges.

List schema objects using .NET

The following code example uses Oracle Developer Tools for Visual Studio (ODT) to retrieve the list of schema objects that are available and then displaying them to the console.

Fig 2. Create and return an open OracleConnection
Fig 3. The utilities class
Fig 4. The main program
Fig 5. Running the program
Fig 6. Retrieving the list of schema objects of hr user.
Fig 7. Retrieving the list of schema objects of system user.
  • Note 1: You will find in many Oracle’s texts that some people using schema and user indistinctly.
  • Note 2: Oracle validates that the users have permissions to use the schema objects being accessed by theirs.

Download example source code.

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

How to connect an Oracle Data Source by using ADO.NET

Step 1: Provide a connection string

To connect to a database, you must provide a connection string to identify the database. The following list describes several common parameters of connection strings.

  • Persist Security Info: if is false, the data source does not return security-sensitive if the connection is open.
  • User ID and Password: The data source login and password to use if you are not using integrated security.
  • Integrated Security or Trusted_Connection: if true, the data source uses the current account credentials for authentication, if false, you must specify the User ID and Password in the connection string.
  • Data Source: The name or network address of the data source instance.
  • Initial Catalog or Database: the name of the database.
  • Connection Timeout: The length of time in seconds to wait for a connection to the server before the data source terminates the attempt and returns an error. The default timeout is 15 seconds.
Fig 1. Provide a connection string.

Step 2: Retrieve a connection string from an application configuration file.

You can either store connection strings in an application configuration file or you can hard-code them directly in your application. If you store connection strings in the configuration file, you can modify them easily, without having to edit the source code and recompile the application.
Each connection string that is stored in an application configuration file has its own assigned name. In an application, you can access a connection string by its programmatic name.

Fig 2. Create and return an OracleConnection object.

Step 3: Handle connection events

ADO.NET connection objects have two events that you can use to retrieve informational messages from a data source or to determine if the state of a connection has changed.

  • InfoMessage: Occurs when a data source returns an informational message. Informational messages are messages from a data source that do not result in an exception being thrown.
  • StateChage: Occurs when a connection changes from the closed state to the open state or from the open state to the closed state.

If an error occurs at the data source, the data provider throws an exception. However, if the data source returns an informational message, the data provider raises an InfoMessage event instead.

Step 4: Handle connection exceptions

The .NET Data provider for Oracle throws an OracleException when it encounters an error or warning generated by an Oracle database. OracleException objects have a Code property that gets the code portion of the error as an integer and message property that gets a description of the error.

Fig 3. Step 3 and 4: Handle connection events and exceptions, you can see the example below.

The following code show how to test each step in C# code.

Fig 4. The code to test our classes.

Fig 5. We run the program and verify the events in the log.

Fig 6. The program wrote the events in the log.

Download source code.

Oracle Recipe #1: How to generate an IDENTITY column using a sequence

Primary keys can be created using either numeric or character data types, with one exception: Oracle does not have the same concept of identity columns as MS SQL Server.

What is an Identity Column?

It is a property that automatically generates a unique sequential value when it is assigned to a numeric data type. Oracle handles this concept using a database object called sequence.

The Sequence database object enables you to generate a unique sequence number. Each user of the sequence can increment it and obtain numbers for their use. Because multiple users can obtain sequence numbers, there is no guarantee that the numbers you get will not have gaps.

A sequence does not have to be related to a single table, and therefore could be used to provide unique numbers to multiple tables. As a general rule, having one sequence per table or at least one for each major table results in easier diagnostics and a better overall experience.
The following examples show how you can add an identity column for two tables: categories and publishers.

Each table will use a IDENTITY column as PRIMARY KEY.
Fig 1. The categories and publishers catalog.

Fig 2. Script to create both tables.

The following code shows an example of how to select the NEXTVAL from a sequence. Upon executing this trigger, the sequence value is incremented by 1.

Fig 3. Script to create database objects: triggers and sequences.

Each time we add a new row, the trigger will automatically create a new unique number for that row.
With this concept, the first row ID would be 1, and the next ID would be last ID number plus one.

Fig 4. Inserting rows without primary key.

Fig 5.Querying the tables.

Download Scripts